Freigeben über

Monitor SQL Database value return incorrect result

  • Artikel

Issue:

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)
                else
                                Call oBag.AddValue("State","GOOD")
                                Call objAPI.Return(oBag)
                end if
                objRS.MoveNext
Loop

objRS.Close

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

Couse:

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.

Resolution:

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

  • Anonymous
    November 18, 2015
    thanks it works :)