CODEWITHSUNDEEP
azure-log-analyticskql
mattb
mattb

Reputation: 472

Kusto query to get percentage value of events over time

I have a Kusto / KQL query in azure log analytics that aggregates a count of events over time, e.g.:

customEvents
| where name == "EventICareAbout"
| extend channel = customDimensions["ChannelName"]
| summarize events=count() by bin(timestamp, 1m), tostring(channel)

This gives a good results set of a count of the events in each minute bucket.

But the count is fairly meaningless, what I want to know is if that count is different to the average of over the say last hour.

But I'm not even sure how to start constructing something like that.

Any pointers?

Upvotes: 0

Views: 1327

Answers (1)

Avnera
Avnera

Reputation: 7618

There are a couple of ways to achieve this, first, calculate the hourly avg as an additional column then calculate the diffs from the hourly average:

let minuteValues = customEvents
| where name == "EventICareAbout"
| extend channel = customDimensions["ChannelName"]
| summarize events=count() by bin(timestamp, 1m), tostring(channel)
| extend Day = startofday(timestamp), hour =hourofday(timestamp);
let hourlyAverage = customEvents
| where name == "EventICareAbout"
| extend channel = customDimensions["ChannelName"]
| summarize events=count() by bin(timestamp, 1m), tostring(channel)
| summarize hourlyAvgEvents = avg(events) by bin(timestamp,1h), tostring(channel)
| extend Day = startofday(timestamp),hour =hourofday(timestamp);
minuteValues
| lookup hourlyAverage on hour, Day
| extend Diff = events- hourlyAvgEvents

Another option is to use the built-in Anomaly detection

Upvotes: 2

Related Questions