Introduction to sp_Blitz
This summer I’ve had several pressing reasons for automating Brent Ozar’s sp_Blitz, a popular SQL Server database health check script that is part of the BrentOzarULTD/SQL-Server-
First-Responder-Kit. The sp_Blitz script creates a prioritized list of issues for dba research and attention. My main reason was for auditing, but the script checks many areas of concern.
One of my reasons for automating is to help with the ISACA www.isaca.org database audit assurance program. Many of the questions tie directly to areas that sp_Blitz is checking.
For example, ISACA question number 220.127.116.11, can be answered with this query:
SELECT * FROM dbo.BlitzResults WHERE ( CheckID = 86 AND -- elevated role Details LIKE '%db_owner%' -- ISACA 18.104.22.168 );
The PowerShell wrapper
The sp_Blitz script has the capability to save the results to a central server. So, I set out to write a PowerShell script to manage the process on all of the instances from one script instead of cluttering all the servers with my PowerShell wrapper script . I also didn’t want to leave a linked server connection to my central management database, so the script needed to build and remove the linked server connection on the fly.
The code really doesn’t do much, it’s just a wrapper around SQLCMD calls. This is what it looks like on the console, but note that it’s being run from a Task Scheduler job:
In the end, I wanted something that’s quick to create and test on a local PC and easy to deploy on the server. The credentials are encrypted with a PowerShell SecureString like this:
The code is here https://github.com/kkarns/
It’s not the best example of PowerShell that I’ve worked on. Clearly, another round of refactoring can reduce the script even more. A log file is written after each script run, and stored in the logs directory within the script directory. Oh, and SettingsClean.XML is a hypothetical example for github without real server names, The script is actually looking for a file named Settings.XML.
The recommendation is to run this at 4:45 p.m. before nightly maintenance jobs alter the nature of your cache information.
Questions and a humble thanks.
If you have the same needs as me I hope this PowerShell wrapper helps. If you have any questions leave an issue. https://github.com/kkarns/
And thanks to Brent Ozar and his team for the years of hard work and community support for this. You guys are inspirational.
I finally refactored the PowerShell script. https://github.com/kkarns/
spblitz-automation.git This update should be a lot easier to use.
[1.] I’m looking into PowerShell remoting as an alternative to reduce the clutter, but this approach is pretty simple.