cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 8

SolarWinds: PowerShell monitor script not returning information correctly

Jump to solution

Hello SWers,

I am working on creating a PS component monitor in SAM.  I have been reading as many posts as I can to try to reach my end goal, but I am thinking I need to check with the community to see if what I am trying is possible (I think it is).  What I am trying to do is this:

I have a SQL query, that I know returns X word if there is an error in the SQL job.  I also know this SQL query will return Y phrase if it is successful.  What my PS script does is connects to the SQL server and db, runs the query, and outputs the results to a file.  I then run some if-then statements on the results to see if anything matches X, or Y.  Here is my if-then:

#Setting results text file to variable

$importedTxt = Get-Content -Path "C:\Code\Sql_Results.txt"

#Read output file and do some processing

forEach ($line in $importedTxt)

{

    If ($line -Match "X1" -or $line -Match "X2")

    {

$errorLines += $line

    }

    ElseIf ($line -Match "Y1" -or $line -Match "Y2")

    {

$successLines += $line

    }

}

#Display errors output if count of errors is is greater than or equal to 1

If ($errorLines.count -ge 1)

{

    Write-Host "Statistic.Error: $($errorLines.count)"

    Write-Host "Message.Error: $errorLines, please look into the error."

} Else

{

    Write-Host "Statistic.Success: $($successLines.count)"

    Write-Host "Message.Success: $successLines, process finished with no errors."

}

Now, I am trying to push this into SAM, but it isn't working quite right.  I get the following when I try to test my script:

pastedImage_2.png

First, is it possible to include BOTH (Statistic.Success / Message.Success) AND (Statistic.Error / Message.Error) in the same script?

Secondly, what credential should I be using to test?  The credential I am using here is the same credential I use to log into the Orion server, so I am thinking there is a connection issue going on with this script.

Thank you,

ZP

Edit: Here are links that I've visited to try to figure this out, note that I am not super script savvy!

The Basics of PowerShell (part 1)

The Basics of PowerShell (part 2)

The Basics of PowerShell (part 3)

SAM Script Component Monitors - Everything you need to know

0 Kudos
1 Solution

Accepted Solutions
Highlighted

Re: SolarWinds: PowerShell monitor script not returning information correctly

Jump to solution

Yes you can set up to 10 output pairs in the script, so you can do the pairs you described with 1 component, but you need to report them both under all circumstances or else the monitor will show as beeing unknown, so add the one you aren't using to your if statements with 0 whatever placeholder value.  You should probably just initialize your successlines with a 0 as well to get it display correctly on these null situations.

The credential you test with should be one that has permission to execute that kind of powershell on the target server.

- Marc Netterfield, Github

View solution in original post

0 Kudos
10 Replies
Highlighted

Re: SolarWinds: PowerShell monitor script not returning information correctly

Jump to solution

Yes you can set up to 10 output pairs in the script, so you can do the pairs you described with 1 component, but you need to report them both under all circumstances or else the monitor will show as beeing unknown, so add the one you aren't using to your if statements with 0 whatever placeholder value.  You should probably just initialize your successlines with a 0 as well to get it display correctly on these null situations.

The credential you test with should be one that has permission to execute that kind of powershell on the target server.

- Marc Netterfield, Github

View solution in original post

0 Kudos
Highlighted
Level 8

Re: SolarWinds: PowerShell monitor script not returning information correctly

Jump to solution

$importedTxt = Get-Content -Path "C:\Code\Sql_Results.txt"

#Read output file and do some processing

forEach ($message in $importedTxt)

{

    If ($message -Match "X1" -or $message -Match "X2")

    {

        $ErrorLines += $message

        $ErrorCount++

    }

    ElseIf ($message -Match "Y1" -or $message -Match "Y2")

    {

        $SuccessLines += $message

        $SuccessCount++

    }

}

#Display errors output if count of errors is is greater than or equal to 1

Write-Host "Statistic.ErrorLines: $ErrorCount"

Write-Host "Message.ErrorLines: $ErrorLines, please look into the error."

Write-Host "Statistic.SuccessLines: $SuccessCount"

Write-Host "Message.SuccessLines: $SuccessLines, process finished with zero errors."

if ($ErrorCount > 0)

{

    Exit 1;

}

else

{

    Exit 0;

}

Now those are always written to the host; however, I am finding that the statistical number does not get pulled into the SolarWinds chart.  When I test the script in SAM I get the write output result:

Also see the right message here:

But when I assign it to a node these are the results I am seeing:

pastedImage_6.png

Any idea on what may be causing this to happen?

Thanks.

0 Kudos
Highlighted

Re: SolarWinds: PowerShell monitor script not returning information correctly

Jump to solution

Is the script executing locally or remotely?

Hard to tell what the output was with the obfuscation.  Just guesses at this point

- Marc Netterfield, Github
0 Kudos
Highlighted
Level 8

Re: SolarWinds: PowerShell monitor script not returning information correctly

Jump to solution

I should just post the entire thing to give you more insight.  Thanks for helping me with this.

I think it may have to do with me writing the SQL results out to a file before processing it.  But this is the entirety of the script.  Also I am executing the script locally.

#Setting Variables
$SQLServer = "server"
$SQLDBName = "db"
$uid ="username"
$pwd = Get-Content C:\Code\SQLLogin.txt
$ErrorLines = @()
$SuccessLines = @()
$ErrorCount = 0
$SuccessCount = 0


$SqlQuery = @"


SELECT [Column1]     
FROM [DB].[dbo].[Table]
WHERE Column2 IN ('App1', 'App2')
--AND Created BETWEEN DATEADD(day, DATEDIFF(day, 1, GETDATE()) ,0) AND DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
AND CONVERT(DATE, Created) >= Convert(DATE, GETDATE())
AND LogLevel in ('INFO', 'ERROR')
ORDER BY Created DESC


"@

#Creating Connection to SQL Server
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True; User ID = $uid; Password = $pwd;"
$SqlConnection.Open()


#Creating SQL Command to Execute
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandText = $SqlQuery


#Create Data Adapter
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd


#Create Dataset
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)


#Outputting Data and close connection
$DataSet.Tables[0] | Format-Table -HideTableHeaders | Out-String | Out-File "C:\Code\Sql_Results.txt"
$SqlConnection.Close()


#Setting results text file to variable
$importedTxt = Get-Content -Path "C:\Code\Sql_Results.txt"


#Read output file and do some processing
forEach ($message in $importedTxt)
{
    If ($message -Match "X2" -or $message -Match "X2")
    {
        $ErrorLines += $message
        $ErrorCount++
    }
    ElseIf ($message -Match "Y1" -or $message -Match "Y2")
    {
        $SuccessLines += $message
        $SuccessCount++
    }
}


#Display errors output if count of errors is is greater than or equal to 1
Write-Host "Statistic.ErrorLines: $ErrorCount"
Write-Host "Message.ErrorLines: $ErrorLines, please look into the error."
Write-Host "Statistic.SuccessLines: $SuccessCount"
Write-Host "Message.SuccessLines: $SuccessLines, process finished with zero errors."


if ($ErrorCount > 0)
{
    Exit 1;
}
else
{
    Exit 0;
}






















Output of $PS looks like this:

pastedImage_20.png

Removed some of the obfuscation:

pastedImage_0.png

Thanks again.

0 Kudos
Highlighted

Re: SolarWinds: PowerShell monitor script not returning information correctly

Jump to solution

For good measure, sometimes when I change the outputs in script based monitors it can be useful to just delete any of the outputs that I had previously saved, they can get weird.  Just jump out from that edit script screen to the edit application screen and you should see the outputs with a delete option near therm. 

- Marc Netterfield, Github
0 Kudos
Highlighted
Level 8

Re: SolarWinds: PowerShell monitor script not returning information correctly

Jump to solution

Thanks mesverrum, should I just delete them and then reassign the template to a node, or test it after deleting so that they repopulate? 

0 Kudos
Highlighted

Re: SolarWinds: PowerShell monitor script not returning information correctly

Jump to solution

delete, then test, let them repop and see what you get

- Marc Netterfield, Github
Highlighted
Level 8

Re: SolarWinds: PowerShell monitor script not returning information correctly

Jump to solution

Bummer, I deleted and repopulated as suggested - it still isn't pulling in that that stat:

pastedImage_1.png

0 Kudos
Highlighted

Re: SolarWinds: PowerShell monitor script not returning information correctly

Jump to solution

Not sure, I see that it is returning your lines correctly in the message so I can't begin to imagine why it doesnt see the stat integer

- Marc Netterfield, Github
0 Kudos