Aug 11 2022 09:12 AM
I have a query which I am running on data for last 24 hours, and summarizing number of alerts by Hour... Essentially the "DateUTCHourOnly" variable can be 0-23 for all the hours of the day.
In our environment it would be unusual for the standard deviation to be over 1000 for those Alerts per Hour values... Additionally, over 1000 alerts in an hour would be very high (it is just a coincidence both the stdev and 1000 per hour alert value are the same).
This is the key part of the query:
| summarize Alerts=count(),max(TimeGenerated) by DateUTCHourOnly
| summarize make_list(Alerts),stdev(Alerts),max(Alerts)
| where stdev_Alerts > 1000 or max_Alerts >=1000
So the problem is, it would be "ideal" to "fill" each Hour as 0 for the alert count value if No Alerts occurred in that hour. Right now I need to use max, because what if there was an alert in 1 hour of the day? Then the stdev is 0 no matter how huge the value=)
So my question is, can this be done it a better way, for example setting "0" alert value somehow for hours that never got any alert? Then I would not need to use max with a threshold count for the max value in the set
Thanks for any tips=)
Aug 11 2022 09:41 AM
Aug 11 2022 12:06 PM - edited Aug 11 2022 12:31 PM
Extremely cool, thanks! here is what I came up with...
| summarize Alerts=count(),max(TimeGenerated) by DateUTCHourOnly
| make-series HourlyData=avg(Alerts) default=0 on DateUTCHourOnly from 0 to 24 step 1
| project series_stats(HourlyData)
| where series_stats_HourlyData_stdev >=1000
Does that look OK? Edit: figured out the nonempty