Monitor Output of SQL Query with SCOM using Property{@Name='State'} Script

We had a requirement to monitor the the output of a Query (Stored Procedure in our case) and the Alert based on the result it generated.


In Our case we wanted to monitor something like

SELECT statusno from XXX table 
If statusno > 0 alert = OK
Else
Alert = fail
End if

So it was like If the Output is anything but 0 we wanted an alert.

I referenced these 2 articles to create our Script based Monitor
http://systemcenter.no/?p=190
http://www.systemcentercentral.com/downloads/downloadsdetails/tabid/144/indexid/7362/default.aspx

I faced some issues where in the script gave errors and did not execute correctly.

The Script i used and that worked for us is a slight modification of one listed here http://systemcenter.no/?p=190 to suit our requirement.

******************************************************************************

Dim objCN, strConnection
Dim objAPI, oBag

Set objCN = CreateObject("ADODB.Connection")
Set objAPI = CreateObject("MOM.ScriptAPI")
Set oBag = objAPI.CreatePropertyBag()


strConnection = "Driver={SQL Server};Server=Your SQL Server Name;Database=The database you need to run this against;Trusted_Connection=TRUE"
objCN.Open strConnection

Dim strSQLQuery
strSQLQuery = "The Query"

Dim objRS
Set objRS=CreateObject("ADODB.Recordset")

Set objRS = objCN.Execute(strSQLQuery)

Do Until objRS.EOF
                'WScript.Echo objRS.Fields("Status")
                if objRS.Fields("Status") <> "0" then
                                                                'WScript.Echo "evaluated as bad"
                                Call oBag.AddValue("State","BAD")
                                Call objAPI.Return(oBag)
                else
                                Call oBag.AddValue("State","GOOD")
                                Call objAPI.Return(oBag)
                end if
                objRS.MoveNext
Loop

objRS.Close


**********************************************************************
e.g
In Our case the Query was:  Exec Sp1
Server= Server01
Database= myDB01

The expressions used during the creation of Monitors are:

Property{@Name='State'}

Another Script example , if in case we can not generate the output of the SQL query to be a Number we can use string value as well:

*****************************************************************************
Dim objCN, strConnection
Dim objAPI, oBag

Set objCN = CreateObject("ADODB.Connection")
Set objAPI = CreateObject("MOM.ScriptAPI")
Set oBag = objAPI.CreatePropertyBag()



strConnection = "Driver={SQL Server};Server=Your SQL Server Name;Database=The database you need to run this against;Trusted_Connection=TRUE"
objCN.Open strConnection


Dim strSQLQuery
strSQLQuery = "SELECT [VALUE] FROM SQLReplView "

Dim objRS
Set objRS=CreateObject("ADODB.Recordset")

Set objRS = objCN.Execute(strSQLQuery)

Do Until objRS.EOF
                'WScript.Echo objRS.Fields("VALUE")
                if objRS.Fields("VALUE") = "ALERT_ON" then
                               'WScript.Echo "evaluated as bad"
                                Call oBag.AddValue("State","BAD")
                                Call objAPI.Return(oBag)
                else
                                Call oBag.AddValue("State","GOOD")
                                Call objAPI.Return(oBag)
                end if
                objRS.MoveNext
Loop

objRS.Close

Hope this helps