This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SolarWinds: PowerShell monitor script not returning information correctly

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

  • 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.

  • $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.

  • Is the script executing locally or remotely?

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

  • 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.

  • 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. 

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

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

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

    pastedImage_1.png

  • 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

  • Well, I appreciate the assistance regardless, thank you - I will keep digging.