CODEWITHSUNDEEP
azureazure-data-explorerkql
gsscoder
gsscoder

Reputation: 3392

Group data by time interval in KQL (Azure Data Explorer)

I simplify the table I have in ADX:

.create table trackedEvents (
    eventId:                 guid,
    eventType:               string,
    timestamp:               datetime,
    data1:                   string,
    data2:                   int,
    data3:                   real)

I need to count records grouping for a time interval of 1 hour in a specified time range.

I'm able to do it without grouping:

trackedEvents
| where eventType == 'pageEvent'
  and timestamp >= datetime('2021-05-18')
  and timestamp <= datetime('2021-05-19')
| summarize Count=count()

I obviously get a scalar result. I'd like to get a tabular result with a count grouped for each hour of the time range.

I'm quite new to KQL, so any help will be really appreciated.

Regards, Giacomo S. S.

Upvotes: 9

Views: 10382

Answers (1)

Slavik N
Slavik N

Reputation: 5298

You should use the bin function, like this:

trackedEvents
| where eventType == 'pageEvent'
| summarize Count=count() by bin(timestamp, 1d)

Upvotes: 15

Related Questions