KQL Search

Query Details

Duration Of Session

Query

let Events=datatable (SessionId:int, TimeGenerated:datetime, Event:string)
[1, datetime(2018-01-01 12:30:00),"Start",
1, datetime(2018-01-01 13:30:00),"Stop",
2, datetime(2018-01-02 12:30:00),"Start",
2, datetime(2018-01-03 13:30:00),"Stop",
3, datetime(2018-01-01 12:30:00),"Start",
3, datetime(2018-01-02 12:45:00),"Stop",
4, datetime(2018-03-03 11:30:00),"Start",
4, datetime(2018-03-03 12:30:00),"Stop",
5, datetime(2018-03-03 13:30:00),"Start"
];
Events
| where Event == "Start"
| project Event, SessionId, StartTime=TimeGenerated
| join kind=leftouter (Events 
        | where Event =="Stop"
        | project EventRight=Event, SessionId, StopTime=iif(isempty(TimeGenerated),datetime(null),TimeGenerated))
    on SessionId
| project SessionId, StartTime, StopTime, Duration = StopTime - StartTime
| where isnull(StopTime)

Explanation

The query is creating a table called "Events" with columns for SessionId, TimeGenerated, and Event. It then filters the table to only include rows where the Event is "Start". It then joins this filtered table with another filtered table where the Event is "Stop" using the SessionId as the key. The result is a table with columns for SessionId, StartTime, StopTime, and Duration, where Duration is calculated as the difference between StopTime and StartTime. Finally, it filters the table to only include rows where StopTime is null.