Filtering and Grouping KQL by Hour of Day or Weekday

EDIT! A friend of mine pointed me to the use of the function HourOfDay() which is native to KQL. I will leave the post as is, but you know now that this exists. Thanks Morten.

Initially you will most likely use KQL for ad-hoc analysis if a customer calls you in panic that a system is slow. But it is much better to use the telemetry to prevent issues and predict that systems get slower and fix issues before users get their eyes all wet.

For this it is important to be able to do comparisons, and in most businesses you can compare business days (monday vs. tuesday) or weekdays (this monday vs. last monday) or hours (10am vs. 4pm).

This morning I logged in to a customers system and compared the last 4 weeks per hour.

You can immediately see that the system is not used on saturday or sunday and that the system gets busier during the day before end of day.

Also you can see the effect of Christmas, but that is irrelevant for this post. 😉

I am open to suggestions but this is what I came up with to render a chart showing the busiest hours of the day.

Hour

Event
| extend hour = tostring(toint(substring(tostring(bin(TimeGenerated, 1h)),11,2)) + 1)
| extend server = strcat(extract("Server instance:\s{1,}([^\ ]+)\s", 1, ParameterXml))
| extend object = strcat(extract("AppObjectType:\s{1,}([^\ ]+)\s", 1, ParameterXml), extract("AppObjectId:\s{1,}([^\ ]+)\s", 1, ParameterXml))
| extend executionTime = toint(executionTime = extract("Execution time:\s{1,}([^\ ]+)\s", 1, ParameterXml))
| extend query = strcat(extract("SELECT\s.FROM\s.WHERE\s.", 0, ParameterXml), extract("DELETE\s.FROM\s.WHERE\s.", 0, ParameterXml), extract("UPDATE\s.SET\s.WHERE\s.*", 0, ParameterXml))
| where ParameterXml contains "Message: Long running SQL statement"
| summarize sum(executionTime) by hour
| render piechart

Note that this client is in CET so I have to manually convert for UTC.

Also, for some reason the Hour needs to be a string to be able to render as a label for the piechart.

Weekday

The WeekDay is easier if you accept that it is zero-based where sunday is zero.

Event
| extend weekday = substring(tostring(dayofweek(TimeGenerated)), 0, 1)
| extend server = strcat(extract("Server instance:\s{1,}([^\ ]+)\s", 1, ParameterXml))
| extend object = strcat(extract("AppObjectType:\s{1,}([^\ ]+)\s", 1, ParameterXml), extract("AppObjectId:\s{1,}([^\ ]+)\s", 1, ParameterXml))
| extend executionTime = toint(executionTime = extract("Execution time:\s{1,}([^\ ]+)\s", 1, ParameterXml))
| extend query = strcat(extract("SELECT\s.FROM\s.WHERE\s.", 0, ParameterXml), extract("DELETE\s.FROM\s.WHERE\s.", 0, ParameterXml), extract("UPDATE\s.SET\s.WHERE\s.*", 0, ParameterXml))
| where ParameterXml contains "Message: Long running SQL statement"
| summarize sum(executionTime) by weekday
| render piechart