This KQL query helps you compute the user’s session duration in the last 30 days.
WVDConnections
| where State in (“Started”, “Connected”, “Completed”) // Filter relevant states
| where TimeGenerated >= ago(30d) // Get records from the last 30 days
| summarize
StartTime = minif(TimeGenerated, State == “Started”), // Earliest time for “Started”
EndTime = maxif(TimeGenerated, State == “Completed”), // Latest time for “Completed”
HasConnected = countif(State == “Connected”) > 0, // Check for “Connected” event presence
SessionHostName = anyif(SessionHostName, State == “Started”) // Capture the server name associated with the session
by CorrelationId, UserName
| extend DurationMinutes = iff(HasConnected and isnotnull(StartTime) and isnotnull(EndTime),
datetime_diff(“minute”, EndTime, StartTime),
-1) // Use -1 as a placeholder for invalid durations
| where DurationMinutes >= 0 // Filter out invalid durations (those set to -1)
| extend SessionDurationHours = DurationMinutes / 60.0 // Calculate duration in hours
| project UserName, CorrelationId, StartTime, EndTime, DurationMinutes, SessionDurationHours, SessionHostName
| order by StartTime desc // Order by StartTime for clarity
The output looks something like this:
