How to Split your Data Into A Fixed Amount of Buckets

Okay, another question from Twitter (original content will have to wait till I get some more free time!)

Here’s the challenge:

So what we need to do here is somehow infer the time-range of the query, and then create a fixed set of time bins according to that range.

I think the only way to that is by performing 2 queries – one to get the time range and convert it into a fixed interval, and a second query with the actual logic.

To convert the result of the first query into a ‘variable’ we can use in the second query, I’ll use the ‘toscalar‘ operation.

Here we go:

let numberOfBuckets = 24;
let interval = toscalar(requests
| summarize interval = (max(timestamp)-min(timestamp)) / numberOfBuckets
| project floor(interval, 1m));
| summarize count() by bin(timestamp , interval)

I use ‘floor’ here just to round the interval and make the results a bit more readable.

One thought on “How to Split your Data Into A Fixed Amount of Buckets

  1. Hello Expert, I read your blog and thought you may be able to help with the struggle I am having with my Query for ALERTs. I am trying to build an ALERT for a condition where in a timespan of 15mins if the number of FailedRequests were Greater than 99% of the requests received I want to raise the Alert. I have written a KQL Query but unfortunately it just fires of even without real issues happening i.e. without really getting the condition of greater than 99%. I hardly have 3 or 4 500 errors in that period and it still reports and Alert fires. Following is the query and I am sure I am making some silly mistake in it any help (not sure if using the bin(timestamp,15m) will help?

    Any help in fixing above query so it really fires only when 99% of the time things fail i.e. when all of the requests received are failing basically.

    requests | where cloud_RoleName == ‘ABCDEF_cloudRName’ and resultCode != ‘404’ | summarize FailedPercent=countif(success == false)*100/count() by timestamp, cloud_RoleName, appName | where FailedPercent > 99 | project RelatedCI=’XYZZZ’,AlarmTime=timestamp,Category=”Cloud-Azure-Monitor”,SubCategory=”Application”,Object=appName ,”Value of Metric”,”Percentage Failed Requests”,” is “, FailedPercent


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s