Forum Discussion

Copper Contributor

Jun 28, 2021

Solved

Loop through the KQL query result

Hi , I need to trigger an alert if windows service is stopped in one of the node. There are 2 nodes and service will be running in both nodes or at one node . Only If service is not running in ...

azure monitor

CliveWatson

Jun 29, 2021

Racheal2k I think you tried this before?

let status =
Event
| where TimeGenerated > ago (1d)
| where EventLog == 'System' and EventID == 7036 and Source == 'Service Control Manager'  and RenderedDescription has 'WMI Performance Adapter' //"Apache tomcat"
| parse kind=relaxed EventData with * '<Data Name="param1">' Windows_Service_Name '</Data><Data Name="param2">' Windows_Service_State '</Data>' *
| summarize count(), (TimeGenerated, winstatus) = arg_max(TimeGenerated, Windows_Service_State) by Windows_Service_Name, Computer;
status
| extend winstatus = iif(winstatus == 'running',1,0)
| summarize sumif(winstatus, winstatus > 0), ComputersOK = make_set_if(Computer, winstatus > 0), ComputerNotOk = make_set_if(Computer, winstatus == 0)
| extend ServiceStatus = iif(sumif_winstatus > 0, "The service is running"," The Service is not runnimg")

Racheal2k

Copper Contributor

Jun 29, 2021

HI ,

Noticed that same query sometimes returns true and sometimes returns False.
I think it returns the status from the last record in the result set.

CliveWatson

Former Employee

Jun 29, 2021

Racheal2k I think you tried this before?

let status =
Event
| where TimeGenerated > ago (1d)
| where EventLog == 'System' and EventID == 7036 and Source == 'Service Control Manager'  and RenderedDescription has 'WMI Performance Adapter' //"Apache tomcat"
| parse kind=relaxed EventData with * '<Data Name="param1">' Windows_Service_Name '</Data><Data Name="param2">' Windows_Service_State '</Data>' *
| summarize count(), (TimeGenerated, winstatus) = arg_max(TimeGenerated, Windows_Service_State) by Windows_Service_Name, Computer;
status
| extend winstatus = iif(winstatus == 'running',1,0)
| summarize sumif(winstatus, winstatus > 0), ComputersOK = make_set_if(Computer, winstatus > 0), ComputerNotOk = make_set_if(Computer, winstatus == 0)
| extend ServiceStatus = iif(sumif_winstatus > 0, "The service is running"," The Service is not runnimg")

Racheal2k
Copper Contributor
Jun 30, 2021
CliveWatson ,

I'm using the below query to trigger alert .

let status =
Event
| where TimeGenerated > ago(30d)
| where EventLog == 'System' and EventID == 7036 and Source == 'Service Control Manager' and RenderedDescription has "PowerCurve - Job Server"
| parse kind=relaxed EventData with * '<Data Name="param1">' Windows_Service_Name '</Data><Data Name="param2">' Windows_Service_State '</Data>' *
| summarize (TimeGenerated, winstatus) = arg_max(TimeGenerated, Windows_Service_State) by Windows_Service_Name, Computer;
status
| extend winstatus = iif(winstatus == 'running', 1, 0)
| summarize sumif(winstatus, winstatus > 0), ComputersOK = make_set_if(Computer, winstatus > 0), ComputerNotOk = make_set_if(Computer, winstatus == 0)
| extend ServiceStatus = iif(sumif_winstatus > 0, "The service is running"," The Service is not running")
| where sumif_winstatus == 0
| project sumif_winstatus, ComputerNotOk, ComputersOK

if no. of result is > 0 then an alert will be triggered.

Am facing a weird issue here , if the service is running in one of the VM this query returns null in log analytics logs window which is perfect.

But i also receive alert that service is stopped and When i click view 1 results from the alert mail i received
it returns status as 0 which means service is stopped

but if i execute the query again by selecting it , it returns null.

I don't understand this behavior from Azure. The same query gives different result through alert and when it executed from log analytics log page it gives different answer.

Could you help with explaining this?
Regards,
Racheal
Racheal2k
Copper Contributor
Jun 29, 2021
CliveWatson , Thanks and that worked.
I have tried until
status
| extend winstatus = iif(winstatus == 'running',1,0) but haven't tried Sumif command 🙂

Great work ! thanks again

Regards,
Racheal