Join/loop through KQL query result

Question

Below gives me a table of distinct usernames

WVDConnections 
| take 100 
| sort by TimeGenerated asc, CorrelationId 
| distinct  UserName

Below gives me a session duration for a single user, but i have to manually type in the UserName

let Events = WVDConnections | where UserName == "userupn" ;
Events
| where State == "Connected"
| project CorrelationId , UserName, ResourceAlias , StartTime=TimeGenerated
| join (Events
| where State == "Completed"
| project EndTime=TimeGenerated, CorrelationId)
on CorrelationId
| project Duration = EndTime - StartTime, ResourceAlias
| sort by Duration asc

How can I combine both queries to get result for each user. If it was powershell or python, i would loop through the list given by 1srt query. Not sure how to do in KQL

Answer 1

Hi,

I think something like this would do the work:

let Events = WVDConnections  ;
Events
| where State == "Connected"
| project CorrelationId , UserName, ResourceAlias , StartTime=TimeGenerated
| join (Events
| where State == "Completed"
| project EndTime=TimeGenerated, CorrelationId, UserName)
on CorrelationId, UserName
| project Duration = EndTime - StartTime, ResourceAlias, UserName
| sort by Duration asc

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.