'Created 12/09/2006
'Author: Justin Hannan
'
'Descripton: An general active monitor for SQL server (although could be adapted for other databases easily. Based on a specified query
'the results are checked for an expected value or result and then alert accordingling.

'On error resume next is used so if the active monitor fails to connect for some reason e.g. server down, the active monitor doesn't
'trigger an alert as some other active monitor is assumed to being used for that like Ping
On Error Resume Next

'Sending log message to the WhatsUp Event Viewer
Context.LogMessage "Checking Address=" & Context.GetProperty("Address")

'Set the result code of the check (0=Success, 1=Error)
Context.SetResult 0, "No error"

'Get Windows server credentials and server address to use in WMI authentications 
'from the device, this way you can use for any machine on any domain
strWinUser = Context.GetProperty("CredWindowsomainAndUserid")
strWinPass = Context.GetProperty("CredWindowsassword")
Address = Context.GetProperty("Address")

strQuery = "<sql query>"

'Create connection and recordset objects
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")

'Create the connection string based on the device credentials
objConn.ConnectionString = "Driver={SQL Server};Server=" & Address & ";Database=<database name>;uid=" & strWinUser & ";pwd=" & strWinPass & ";"

'Open and connect to the database
objConn.Open

'Perform the query and move to the first recordset entry
objRS.Open strQuery, objConn, 3, 3
objRS.MoveFirst

' Peform logic on query results to determine if an alert is required or if everything is OK.
IF objRS.Fields.Item("<record set column name from query specified in strQuery variable>") > 0 Then
       Context.SetResult 1, "<Error message>"
ELSE
       Context.LogMessage "<OK Message>"
END IF

'Tidy up the objects
objRS.Close
objConn.Close