CODEWITHSUNDEEP
azure-data-explorerkql
Kuba K
Kuba K

Reputation: 467

Time difference between separate rows in same table

I have Sessions table

Sessions
|Timespan|Name |No|
|12:00:00|Start|1 |
|12:01:00|End  |2 |
|12:02:00|Start|3 |
|12:04:00|Start|4 |
|12:04:30|Error|5 |

I need to extract from it duration of each session using KQL (but if you could give me suggestion how I can do it with some other query language it would be also very helpful). But if next row after start is also start, it means session was abandoned and we should ignore it.

Expected result:

|Duration|SessionNo|
|00:01:00|    1    |
|00:00:30|    4    |

Upvotes: 7

Views: 6354

Answers (2)

Tomer Shetah
Tomer Shetah

Reputation: 8539

You can try something like this:

Sessions
| order by No asc 
| extend nextName = next(Name), nextTimestamp = next(timestamp)
| where Name == "Start" and nextName != "Start"
| project Duration = nextTimestamp - timestamp, No

When using the operator order by, you are getting a Serialized row set, which then you can use operators such as next and prev. Basically you are seeking rows with No == "Start" and next(Name) == "End", so this is what I did,

You can find this query running at Kusto Samples open database.

Upvotes: 10

Slavik N
Slavik N

Reputation: 5328

let Sessions = datatable(Timestamp: datetime, Name: string, No: long) [
    datetime(12:00:00),"Start",1,
    datetime(12:01:00),"End",2,
    datetime(12:02:00),"Start",3,
    datetime(12:04:00),"Start",4,
    datetime(12:04:30),"Error",5
];
Sessions
| order by No asc
| extend Duration = iff(Name != "Start" and prev(Name) == "Start", Timestamp - prev(Timestamp), timespan(null)), SessionNo = prev(No)
| where isnotnull(Duration)
| project Duration, SessionNo

Upvotes: 3

Related Questions