Hi I am trying to execute below script in a two-state monitor for creating an alert based on SQL Query. but not getting any alert although the query executes fine if I check it using OLEDB Monitor in SCOM.

I am using unhealthy criteria to be:

Property[@Name='JobState'] equals FAIL

To trace, I am writing below textual status on a file D:\ABC\SCOMSCRIPT.TXT. and this file shows all the values correctly:

"CONNECTION OPENED.QUERY EXECUTED.FAIL.RETURNING oBag to SCOM."

But SCOM doesn't raise any alert in alerts section. Any help or idea:

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=DB05.ABC.DE;Database=DBprod;Trusted_Connection=TRUE"

objCN.Open strConnection

 

Set objFSO=CreateObject("Scripting.FileSystemObject")

outFile="D:\ABC\SCOMSCRIPT.TXT"

Set objFile = objFSO.CreateTextFile(outFile,True)

objFile.Write "CONNECTION OPENED."

 

Dim strSQLQuery

strSQLQuery = "SELECT TOP 1 [joblogJobName], [job_definition_gkey], [execution_node], [scheduler_group_name], [job_start_date], [job_end_date], [job_execution_time],  [state], [note] FROM [DBprod].[dbo].[frm_job_log] where state='FAIL' order by job_end_date DESC"

 

Dim objRS

Set objRS=CreateObject("ADODB.Recordset")

Set objRS = objCN.Execute(strSQLQuery)

 

objFile.Write "QUERY EXECUTED."

objFile.Write objRS.Fields("state") & "."

Call oBag.AddValue("JobState",objRS.Fields("state"))

objFile.Write "RETURNING oBag to SCOM."

objFile.Close

 

Call objAPI.Return(oBag)

objRS.Close

Thanks.

What happens when you run the script manually on a command prompt? Run it with:

cscript.exe myscript.vbs

Also note that the criteria is case sensitive, so if the SQL query returns "fail" and you're looking for "FAIL" it won't match up.

Hi,

Thank you for your response. I haven't try running the query on command prompt. Will do it and update the status here.

Regarding the case sensitivity, the SQL Query returns FAIL, and same is matched in the alert criteria.

Some snapshots for reference:

T

Try to modify the following code with
***************************************************
objFile.Write "QUERY EXECUTED."

objFile.Write objRS.Fields("state") & "."

Call oBag.AddValue("JobState",objRS.Fields("state"))

objFile.Write "RETURNING oBag to SCOM."

objFile.Close
******************************************************************
objFile.Write "QUERY EXECUTED."

objFile.Write objRS.Fields("state") & "."

if objRs.Fields("state")="FAIL" then
  Call oBag.AddValue("JobState","FAIL")
else
  Call oBag.AddValue("JobState","SUCCESS")
end if
objFile.Write "RETURNING oBag to SCOM."

objFile.Close
*********************************************************************

Roger

Hi Roger,

I made the suggested change. and I can see in the log file that script is reaching inside the IF clause (which means objRS.fields('state') contains FAIL). but still the monitor doesn't raise any alert in the Active Alerts.

if objRs.Fields("state")="FAIL" then

  objFile.Write "ENTERED IN IF CLAUSE"

  Call oBag.AddValue("JobState","FAIL")

else

  objFile.Write "ENTERED IN ELSE CLAUSE"

  Call oBag.AddValue("JobState","SUCCESS")

end if

Thanks.

Hi Andyinsdca,

I tried to run the script manually on command prompt using cscript.exe XXXXXXXXXXSQLQuery1.vbs and below is the output:

PS C:\> cscript.exe .\XXXXXXXXXXSQLQuery1.vbs
Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.

<DataItem type="System.PropertyBagData" time="2015-07-01T11:54:32.3752875+04:00" sourceHealthServiceId="6A939022-3E9C-1F44-3A42-D4F044585687"><Property Name="JobName" VariantType="9">XXX_XXXX_XXXXX_2012-09-23 10:09:23_-183773828</Property
><Property Name="JobNode" VariantType="9">XXXXXXX-node1</Property><Property Name="JobGroup" VariantType="9">EDI</Property><Property Name="JobStartDate" VariantType="9">06/25/2015 05:15:12</Property><Property Name="JobEndDate" VariantTyp
e="9">06/25/2015 05:15:18</Property><Property Name="JobNote" VariantType="9">EdiJob "XXX_XXXX_XXXXX" Failed. Please refer XXX Batch : 426676 File extracted successfully but failed to send due to problem in communication. It will be sent i
n next successful extract.
Cannot load the given file 1435194912426XXX_XXXX_XXXXX.edi
</Property><Property Name="JobState" VariantType="8">FAIL</Property></DataItem>

Property JobState is getting the value FAIL. Is there anything I am missing in matching the criteria?

I cannot see any alert raising by the two-state monitor in SCOM.

If I open the health explorer of the targeted object, should it list the monitor there?

Regards.

Further checking
1) Make sure Generate alerts for this monitor is selected and Generate an alert when the monitor is in a Critical health state in Alerting tab
2) Open the health explorer of the object which contain the monitor and check the operational state of the monitor

3) Recalculate health of this monitor 

4) Try to change the Health state as Monitor condition healthy --> Critical and Unhealthy monitor condition --> critical  to see whether the monitor health state is change from healthy into critical

5) At last, try to modify the condition as Property[@Name='JobState'] contains FAIL

Roger 

• Edited by System Center guyMicrosoft community contributor 18 hours 50 minutes ago

Further checking
1) Make sure Generate alerts for this monitor is selected and Generate an alert when the monitor is in a Critical health state in Alerting tab
2) Open the health explorer of the object which contain the monitor and check the operational state of the monitor

3) Recalculate health of this monitor 

4) Try to change the Health state as Monitor condition healthy --> Critical and Unhealthy monitor condition --> critical  to see whether the monitor health state is change from healthy into critical

5) At last, try to modify the condition as Property[@Name='JobState'] contains FAIL

Roger 

• Edited by System Center guyMicrosoft community contributor Wednesday, July 01, 2015 12:19 PM

Everything looks OK, and per System Center Guy, I am a big fan of his suggestion #5 to change it to CONTAINS instead of EQUALS; my experience has taught me that EQUALS never works, unless you're doing EQUALS (a number)