1

You can run sp_blitz (First Responder Kit by Brent Ozar) on a sql server to output to a table. But it will store the output only on that server. I am using Dbatools.io PowerShell commands currently. I can run the sp_Blitz commands agains all SQL servers. But I don’t know how to capture the output on my report server in a single database table centrally. Is there a way to store output of sp_blitz command against a list of all servers so that I can create a report for all critical issues across all my servers? Here is the code I am using.

Import-Module dbatools

Set your database connection details


$database = "<Database Name>"
$table = "<table name containing server names>"
$output = "dbo.BlitzResults"
$sqlInstance = "<InstanceName>"  # The instance where the ServerList table is located


$servers = Invoke-DbaQuery -SqlInstance $sqlInstance -Database $database -Query "select * from $table;"
$servers


Loop through each server and execute sp_Blitz


foreach ($server in $servers.ItemArray) {
    Write-Host "Running sp_Blitz on $($server)"


try {
    $result = Invoke-DbaQuery -SqlInstance $server -Database master -Query &quot;EXEC dbo.sp_Blitz&quot;        
    # Output the results
    $result | Format-Table -AutoSize
    #Following two lines are not working as expected
    #Write-Host $result.ItemArray
    #Write-DbaDbTableData -SqlInstance $sqlInstance -DestinationDatabase $database -DestinationTable $output -Data $result.ItemArray -CreateTable $true
} catch {
    Write-Host &quot;Failed to run sp_Blitz on $($server.ServerName). Error: $_&quot;
}



}
Pat
  • 357
  • 1
  • 5
  • 13

1 Answers1

1

Short answer

You can use the @OutputServerName parameter to send the results to the SQL instance of your choice, via a Linked Server.

Long answer

Straight from Brent's mouth, at sp_Blitz Pro Tips for Managing Multiple Servers Centrally:

How to Centralize Results Across Your Estate

As long as you’re centralizing your management of checks to skip, you can also centralize the results. sp_Blitz has parameters to write the output to a database, and it works across linked servers, too:

sp_Blitz 
  @SkipChecksServer = 'ManagementServerName',
  @SkipChecksDatabase = 'DBAtools', 
  @SkipChecksSchema = 'dbo', 
  @SkipChecksTable = 'BlitzChecksToSkip',
  @OutputServerName = 'ManagementServerName',
  @OutputDatabaseName = 'DBAtools',
  @OutputSchemaName = 'dbo',
  @OutputTableName = 'BlitzResults';

sp_Blitz will create the table if it doesn’t already exist (but the linked server, database, and schema have to already exist.)

Set that up in a daily Agent job or run it centrally from PowerShell, and you can have all your servers phoning home into one table on a regular basis. You can query the results to slice & dice ’em by priority, or find out what problems are new in your environment today as opposed to yesterday.

The output table has the same results as sp_Blitz, but adds two columns: ServerName and CheckDate, so you can query data by server or by most recent check dates.

Doug Deden
  • 2,171
  • 10
  • 14