Powershell Invoke-sqlcmd not working in Monitor Template Powershell Module

Are your running on the Orion polling engine or on the remote device? Is the module installed there? 

We have 3 SW poller servers and I installed the modules for Powershell on all 3.
I'm attempting to run the scripts from our Web Interface page which has the same IP address as our primary poller.

I format mine a little different but they run. 

Invoke-Sqlcmd -Query "MyQuery From @Results" -ServerInstance "SQLServerName" -Database "DatabaseName"

@bobmarley

I tried and get the same error. 

Does the above script you suggested work for you in the Component Monitor?  If so, could you provide a screenshot just to see if anything stands out to me that I'm doing differently?  I've tried so many different combinations and it seems like nothing works with Invoke-sqlcmd in the component monitor even though the commands work in the Powershell application.  I called Solarwind Tech Support and was referred back to Thwack so any assistance would be helpful.  Right now i'm at the stage of "will the invoke-Sqlcmd command work at all, in any query in Component Monitor."

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)

Thanks. The syntax worked!  Much appreciated.