Automation of sp_Blitz with a PowerShell wrapper 

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.

The ISACA

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 3.1.1.2, can be answered with this query:

SELECT *
FROM dbo.BlitzResults
WHERE 
    (
    CheckID = 86  AND  -- elevated role
    Details LIKE '%db_owner%'  -- ISACA 3.1.1.2
    );

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 [1].  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:

 

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/spblitz-automation/issues
And thanks to Brent Ozar and his team for the years of hard work and community support for this.  You guys are inspirational.

Update 1/9/19.

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.

2 Replies to “Automation of sp_Blitz with a PowerShell wrapper ”

    1. Thanks Jeff! I’ll be watching to see if Brent and his team release a script that does something similar to this, but for now the Powershell is doing a lot of work for me.

Leave a Reply

Your email address will not be published. Required fields are marked *