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

%3CLINGO-SUB%20id%3D%22lingo-sub-1808654%22%20slang%3D%22en-US%22%3EKQL%20Help%20-%20creating%20a%20histogram%20of%20concurrent%20user%20count%20from%20table%20of%20connect%2Fcompleted%20timestamps%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1808654%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20running%20Azure%20WVD%20connected%20into%20LogAnalytics%20and%20I%20would%20like%20to%20create%20a%20chart%20showing%20the%20number%20of%20concurrent%20users%20per%20hour%20over%20the%20previous%20week.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20written%20this%20query%20(currently%20limited%20to%20just%20a%20specific%20user%20for%20testing)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%20Events%20%3D%20WVDConnections%0A%7C%20where%20UserName%20%3D%3D%20%22tomas.zemek%40ricardo.com%22%3B%0AEvents%0A%20%7C%20where%20State%20%3D%3D%20%22Connected%22%0A%20%7C%20project%20CorrelationId%20%2C%20UserName%2C%20StartTime%3DTimeGenerated%2C%20State%0A%20%7C%20join%0A%20(%0A%20%20%20%20%20Events%0A%20%20%20%20%20%7C%20where%20State%20%3D%3D%20%22Completed%22%0A%20%20%20%20%20%7C%20project%20EndTime%3DTimeGenerated%2C%20CorrelationId%2C%20State%0A%20)%0A%20on%20CorrelationId%0A%20%7C%20project%20UserName%2C%20CorrelationId%2C%20State%2C%20State1%2C%20StartTime%2C%20EndTime%0A%7C%20sort%20by%20StartTime%20desc%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhich%20gives%20me%20the%20below%20tabular%20output%20(I%20have%20included%20the%20state%20and%20state1%20columns%20for%20clarity)%3A%3C%2FP%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222020-10-22_15h07_59.png%22%20style%3D%22width%3A%20994px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F228501i4B28473F0DB6D240%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%222020-10-22_15h07_59.png%22%20alt%3D%222020-10-22_15h07_59.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3EBut%2C%20I%20am%20stuck%20as%20I%20can't%20workout%20how%20to%20do%20the%20make-series%20statement%20to%20count%20the%20number%20of%20concurrent%20user%20sessions%20at%20any%20time.The%20below%20statement%20just%20counts%20the%20number%20of%3C%2FDIV%3E%3CDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Emake-series%20count%3C%2FSPAN%3E%3CSPAN%3E(UserName)%20default%3D%3C%2FSPAN%3E%3CSPAN%3E0%20%3C%2FSPAN%3E%3CSPAN%3Eon%3C%2FSPAN%3E%3CSPAN%3E%20StartTime%20from%20ago(%3C%2FSPAN%3E%3CSPAN%3E7%3C%2FSPAN%3E%3CSPAN%3Ed)%20to%20now()%20step%20%3C%2FSPAN%3E%3CSPAN%3E15%3C%2FSPAN%3E%3CSPAN%3Em%3C%2FSPAN%3E%3C%2FP%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EI%20am%20also%20aware%20that%20this%20query%20excludes%20any%20currently%20running%20sessions%20as%20they%20don't%20have%20a%20completed%20entry%20until%20they%20are%20finished%20and%20I%20would%20like%20to%20include%20the%20current%20sessions%20too.%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3ECan%20anyone%20help%20me%20with%20the%20KQL%20for%20this%3F%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EThanks%20in%20advance%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EGary%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1811438%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20Help%20-%20creating%20a%20histogram%20of%20concurrent%20user%20count%20from%20table%20of%20connect%2Fcompleted%20timesta%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1811438%22%20slang%3D%22en-US%22%3E%3CP%3EAny%20ideas%20on%20this%20anyone%20-%20is%20this%20even%20possible%20in%20KQL%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1811889%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20Help%20-%20creating%20a%20histogram%20of%20concurrent%20user%20count%20from%20table%20of%20connect%2Fcompleted%20timesta%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1811889%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F673841%22%20target%3D%22_blank%22%3E%40garymansell%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHave%20you%20looked%20at%20the%20Workbook%20for%20WVD%2C%20you%20can%20see%20the%20many%20KQL%20examples%20used%20by%20this%20when%20you%20open%20it%20in%20edit%20mode%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fwindows-it-pro-blog%2Fproactively-monitor-arm-based-windows-virtual-desktop-with-azure%2Fba-p%2F1508735%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fwindows-it-pro-blog%2Fproactively-monitor-arm-based-windows-virtual-desktop-with-azure%2Fba-p%2F1508735%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
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...

@Clive Watson 

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