Forum Discussion

Racheal2k's avatar
Racheal2k
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's avatar
    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")

     

     

CliveWatson's avatar
CliveWatson
Former Employee
Jun 28, 2021
maybe add a last line of

| summarize anyif(winstatus !="stopped", true)
Racheal2k's avatar
Racheal2k
Copper Contributor
Jun 28, 2021

CliveWatson Thanks

 

This command is not clear to me 

because I used,
| summarize anyif(winstatus !="stopped", true) --> returns false // . As per the query i think if status is not equal to stopped in any of the VM then returns true else returns false . this returns false because service is stopped in one of the VM

also checked
| summarize anyif(winstatus !="running", true) -> returns true// . As per the query i think if status is not equal to running in any of the VM then returns true else returns false . this returns true even though the service is running in one of the VM

Here's the VM service status

6/28/2021, 10:00:08.173 AM stopped apacheNode1
6/28/2021, 10:07:53.470 AM running apacheNode2

Modified query

let status =
Event
| where TimeGenerated > ago (1d)
| where EventLog == 'System' and EventID == 7036 and Source == 'Service Control Manager' and RenderedDescription has "Apache"
| 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
| summarize status= anyif(winstatus != "stopped", true);
status
| where status == 'false'
| project status

  • Racheal2k's avatar
    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's avatar
      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's avatar
        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

         

Resources