Compare count of errors in last 12h against last multiple periods of 12h

New Contributor

I have a set of VMs that generate errors. I want a query to return a result if the last 12 hours has generated more errors than the last multiple periods of 12 hours.


Event| where TimeGenerated > ago(7d)| where EventLevelName == "Warning" or EventLevelName == "Error"| summarize count_ = count() by bin(TimeGenerated, 12h)| summarize maxi = max(count_), mini = min(count_)

This returns two columns with the maximum and minimum values of the last multiple periods of 12 hours. 


Problem one is that is includes the latest 12 hours so it will never be more than that when comparing.

Problem two is that it is missing that final comparison syntax. How do I do it? 

1 Reply

Hi @Preben902 


You can do the following query:


let counts_tbl = materialize(Event
| where TimeGenerated > ago(7d)
| where EventLevelName == "Warning" or EventLevelName == "Error"
| summarize count() by bin(TimeGenerated, 12h)
| order by TimeGenerated desc 
| extend rid = row_number()
| where rid > 1     //  all but current 12h bin
| summarize maxi = max(count_)
| extend dummy=1
| join (counts_tbl
| where rid == 1    //  current 12h bin
| extend dummy=1)
on dummy
| project maxi, current=count_, diff=count_-maxi

 but if you want to detect anomaly of the last point I recommend building a time series of counts using make-series and then use series_decompose_anomalies function.