CODEWITHSUNDEEP
kql
Zenuka
Zenuka

Reputation: 3250

Kusto query for time difference between records

I'm looking for a way to query the time difference (in seconds) between two records grouped by operation id. When given this sample data:

datatable(timestamp:datetime, operation_id:string)
[
    datetime(15-7-2021 12:45:37), 'abc',    
    datetime(15-7-2021 12:45:39), 'abc',    
    datetime(15-7-2021 13:29:12), 'def',   
    datetime(15-7-2021 13:29:14), 'def',  
    datetime(15-7-2021 13:29:17), 'def',    
    datetime(15-7-2021 13:29:23), 'def',   
]

The expected output would be:

operation_id diff
abc 2
def 2
def 3
def 6

Is this possible?

P.s. it's similar to this question but I do not want the difference between the min and max but for each record

Upvotes: 0

Views: 2133

Answers (1)

Yoni L.
Yoni L.

Reputation: 25955

you can order the table and then use the prev() function:

datatable(ts:datetime, op_id:string)
[
    datetime(07-15-2021 12:45:37), 'abc',
    datetime(07-15-2021 12:45:39), 'abc',
    datetime(07-15-2021 13:29:12), 'def',
    datetime(07-15-2021 13:29:14), 'def',
    datetime(07-15-2021 13:29:17), 'def',
    datetime(07-15-2021 13:29:23), 'def',
]
| order by op_id asc, ts asc
| extend prev_ts = prev(ts), prev_op_id = prev(op_id)
| project op_id, diff = case(prev_op_id == op_id, (ts - prev_ts)/1s, double(null))
| where isnotnull(diff)
op_id diff
abc 2
def 2
def 3
def 6

Upvotes: 2

Related Questions