Standard Deviation - Hourly Data - prefill "hours" with no data with "0" alerts?

[ArcticMyst]

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=)

[#2]

Have you looked at make-series rather than summarize? It has a "defaultvalue" you can use for missing data in each hourly bin

https://docs.microsoft.com/en-gb/azure/data-explorer/kusto/query/make-seriesoperator

[#3]

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