Dec 12 2019 06:46 AM
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?
Feb 13 2020 12:05 AM
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() ); counts_tbl | 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.
Thanks
Adi