Running sp_AskBrent with @ExpertMode = 1 in PowerShell

I am trying to run sp_AskBrent from PowerShell using Invoke-SQLCmd and capture its output in a variable:

$query = "EXEC SAIDBA.monitoring.sp_AskBrent @Seconds=10;"
$check = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $query 
             -ErrorAction Stop -ConnectionTimeout 3

When running with ExpertMode = 0, no problem.

But when running with ExpertMode = 1, we notice three things:

• It outputs every data to the shell

• $check is null

-
It ends with the following error:


Invoke-Sqlcmd: Duplicate column names are not permitted in SQL PowerShell. To repeat a column, use a column alias for the duplicate column in the format Column_Name AS New_Name.

At line:1 char:18

• $check = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $query -Er ...

• CategoryInfo: SyntaxError: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException

• FullyQualifiedErrorId : DuplicateColumnNameErrorMessage, Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

I think there is a workaround of providing parameters for sp_AskBrent to store the ExpertMode data into tables and select from those tables afterwards, but I want to make sure there is no way to get everything back at once in PowerShell.

Brent here. Correct, @ExpertMode = 1 turns on multiple result sets - diagnostics, wait stats, file stats, and perfmon counters.

If you only want one result set, don't turn on @ExpertMode.

If you want multiple result sets, but your application (in this case, PoSH) can't consume them, you'll need to log them to tables. That's where these parameters come in:

• @OutputDatabaseName - the name of the database you want to write into. Must already exist.

• @OutputSchemaName - the schema where your tables will live, like dbo. Must already exist.

• @OutputTableName - the table name for the top result set (diagnostics). This parameter also requires the above two. If the table doesn't already exist, it will be created.

• @OutputTableNameFileStats, @OutputTableNamePerfmonStats, @OutputTableNameWaitStats - the rest of the result sets will be written here. If each table doesn't already exist, it will be created. Each of these is totally optional - you can pass in none, some, or all of them.

If you want it to work differently, describe what you need, and I'll see if I can come up with a way to get it. Hope that helps!

From a comment:


I was actually thinking about writing a health check script which I would run in case of incident or for monitoring and which would parse the output of sp_AskBrent.

OK, cool. Just a few things to keep in mind - the tables don't clean themselves out, and they can get big if you use the @SkipChecksQueries = 0 parameter, for example. The sp_AskBrent® licensing also prohibits distribution, so just make sure you're not bundling the script, or installing it on servers you don't own. Otherwise, go for it! If there's anything I can do to help make it easier, holler.

There's a date field in all of the output tables to help you maintain history tables.

StackExchange Database Administrators Q#122262, answer score: 10