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.
Details
Rod Trent
Released: March 26, 2020
Tables
Events
Keywords
DevicesIntuneUser
Operators
whereprojectjoinkindoniifisemptydatetimenullisnull