KQL Help - creating a histogram of concurrent user count from table of connect/completed timestamps?

Copper Contributor

Hi,

I am running Azure WVD connected into LogAnalytics and I would like to create a chart showing the number of concurrent users per hour over the previous week.

 

I have written this query (currently limited to just a specific user for testing):

 

let Events = WVDConnections
| where UserName == "tomas.zemek@ricardo.com";
Events
	| where State == "Connected"
	| project CorrelationId , UserName, StartTime=TimeGenerated, State
	| join
	(
	    Events
	    | where State == "Completed"
	    | project EndTime=TimeGenerated, CorrelationId, State
	)
	on CorrelationId
	| project UserName, CorrelationId, State, State1, StartTime, EndTime
| sort by StartTime desc

 

 

Which gives me the below tabular output (I have included the state and state1 columns for clarity):

 
2020-10-22_15h07_59.png
But, I am stuck as I can't workout how to do the make-series statement to count the number of concurrent user sessions at any time.The below statement just counts the number of

 

| make-series count(UserName) default=0 on StartTime from ago(7d) to now() step 15m

 
I am also aware that this query excludes any currently running sessions as they don't have a completed entry until they are finished and I would like to include the current sessions too.
 
Can anyone help me with the KQL for this?
 
Thanks in advance
 
Gary
 
3 Replies

Any ideas on this anyone - is this even possible in KQL?

@garymansell 

 

Have you looked at the Workbook for WVD, you can see the many KQL examples used by this when you open it in edit mode?

 https://techcommunity.microsoft.com/t5/windows-it-pro-blog/proactively-monitor-arm-based-windows-vir...

@CliveWatson 

Yeah - have checked through there and I can see no queries or similar one that do what I want to do in my question