Powershell Invoke-sqlcmd not working in Monitor Template Powershell Module

Question

if i run from Powershell command line on server i get results.

if i run from script body i get "not defined"

i've added invoke sqlcmd on all of our Solarwinds Servers.

Martin_Crothers · Accepted Answer

Hi jonathanlippe,

The data will need to be formatted in a manner that Orion can parse. Assuming that you're using the Windows PowerShell Monitor component type (and not Windows Script Monitor), you'll need to have your data presented to Orion using the Write-Host commandlet.

For example:

Write-Host ("Statistic.1: " + 1)
Write-Host ("Message.1: " + "Orion")
Write-Host "Statistic.CPU_Speed: 3.14"
Write-Host "Message.CPU_Speed: GHz"

Exit(0)

The output is formatted as:

"Statistic" which is a numerical value
"Message" which is a string value

The value following the Message/Statistic keyword can be numerical or string values, and are the keys for identifying which Message/Statistic to reference, and will be the label given to it in the outputs section.

The value that Orion is checking for the thresholds is the numerical value (Statistic).

So, in order to monitor the output of your SQL query, you'll need to setup a comparison operation and use that to generate a numerical value that Orion can reference as a threshold. Given the type of data your example query returns you would need to come up with some logic to determine what you're looking for and then convert that into a True/False (1/0) response and return (Write-Host) that value.

If you're looking for a specific database name to be present you could do something like the following:

Import-Module SqlServer
$Query = "SELECT name FROM sys.databases"
$ServerInstance = $args[0]  # The argument ${Node.Caption} passed from the arguments line
$QueryResults = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $Query

$DatabaseNameExpected = "SolarWindsOrion"  # The name of the DB we are validating is present
$DatabasePresent = [int]($QueryResults.name -contains $DatabaseNameExpected)  # Checks for presence as True/False and converts to integer

Write-Host ("Statistic.OrionDatabaseExists: " + $DatabasePresent)

<#
    * This assumes that the credential set being used has permissions to query the databse.
    * Performs your query
    * Moves the "${Node.Caption}" Orion variable to the arguments line to that it can be used in the script body
    * And the rest is stuff I made up to provide an example of a way to parse the data into something usable by Orion
#>
Exit(0)

Powershell Invoke-sqlcmd not working in Monitor Template Powershell Module

Top Replies