Forum Discussion

Preben902's avatar
Preben902
Copper Contributor
Dec 12, 2019

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

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? 

Azure Data Explorer (Kusto)
Kusto language

1 Reply

Sort By
  • adieldar's avatar
    adieldar
    Icon for Microsoft rankMicrosoft
    Feb 13, 2020

    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

Resources