Monitor SQL Database value return incorrect result


you have to monitor SQL database value using Unit Monitor / timed script, by VB Script and return the value to property bag, but the result is Misinformation.

for example: Monitoring Specific runbook status from Orchestrator Database, Policies table, column Published, show the runbook status, value '0' Meaning Runbook stopped.

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=<SQL Server Name>;Database=Orchestrator;Trusted_Connection=TRUE"
objCN.Open strConnection

Dim strSQLQuery
strSQLQuery = "Select Published FROM [Orchestartor].[dbo].[Policies] where name = 'Runbook Name'"

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

Set objRS = objCN.Execute(strSQLQuery)

Do Until objRS.EOF
                'WScript.Echo objRS.Fields("Published")
                if objRS.Fields("Published") = "0" then
                                 Call oBag.AddValue("State","BAD")
                                Call objAPI.Return(oBag)
                                Call oBag.AddValue("State","GOOD")
                                Call objAPI.Return(oBag)
                end if


The runbook that selected in script status is running [1] but the result of script is '-1' and not 1


The reason for that is, because the type of column is 'bit' not 'int', so in INT type if condition are work not in bit type.


Change the query to casting the result from bit type to INT, 

With Casting: "Select cast(Published AS INT) AS Published FROM [Orchestartor].[dbo].[Policies] where name = 'Runbook Name'"

the result of query now is:


Select the Script with casting inside the query and build the Monitor and the result will be correct..


Comments (1)

  1. Vlad says:

    thanks it works 🙂

Skip to main content