SOLVED

Latest Data Set

%3CLINGO-SUB%20id%3D%22lingo-sub-1046611%22%20slang%3D%22en-US%22%3ELatest%20Data%20Set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1046611%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20really%20tried%20it%20a%20couple%20of%20hours%20but%20no%20more%20ideas.%20The%20scenario%20is%20as%20follows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20collect%20session%20data%20for%20a%20virtual%20desktop%20farm%20to%20display%20number%20of%20users%20connected%20to%20each%20server.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVDIUserSessions_CL%3CBR%20%2F%3E%7C%20project%20TimeGenerated%2C%20TenantName%3DTenantName_s%2C%20HostPoolName%3DHostPoolName_s%20%2C%20SessionHostName%3DSessionHostName_s%20%2C%20UserPrincipalName%3DUserPrincipalName_s%20%2C%20AdUserName%3DAdUserName_s%20%2C%20LoginTimeUTC%3DCreateTime_t%20%2C%20SessionState%3DSessionState_s%3CBR%20%2F%3E%7C%20summarize%20count(SessionState)%20by%20TenantName%2CHostPoolName%2CSessionHostName%2CTimeGenerated%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20table%20output%20is%20correct.%20I%20get%20something%20like%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3ETime%3C%2FTD%3E%3CTD%3EHostname%3C%2FTD%3E%3CTD%3ENumberOfSessions%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%E2%80%8E12%E2%80%8E%2F%E2%80%8E4%E2%80%8E%2F%E2%80%8E2019%E2%80%8E%20%E2%80%8E8%E2%80%8E%3A%E2%80%8E17%E2%80%8E%3A%E2%80%8E16.167%E2%80%8E%20%E2%80%8EAM%3C%2FTD%3E%3CTD%3EServer%201%3C%2FTD%3E%3CTD%3E11%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%E2%80%8E12%E2%80%8E%2F%E2%80%8E4%E2%80%8E%2F%E2%80%8E2019%E2%80%8E%20%E2%80%8E8%E2%80%8E%3A%E2%80%8E17%E2%80%8E%3A%E2%80%8E16.167%E2%80%8E%20%E2%80%8EAM%3C%2FTD%3E%3CTD%3EServer%202%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%E2%80%8E12%E2%80%8E%2F%E2%80%8E4%E2%80%8E%2F%E2%80%8E2019%E2%80%8E%206%3A10%3A%E2%80%8E16.167%E2%80%8E%20%E2%80%8EAM%3C%2FTD%3E%3CTD%3EServer%201%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E12%E2%80%8E%2F%E2%80%8E4%E2%80%8E%2F%E2%80%8E2019%E2%80%8E%206%3A10%3A%E2%80%8E16.167%E2%80%8E%20%E2%80%8EAM%3C%2FTD%3E%3CTD%3EServer%202%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20question%20I%20have.%20I%20am%20only%20interested%20in%20the%20last%20information%20and%20not%20in%20the%20history%2C%20so%20I%20thought%20something%20like%20max(TimeGenerated)%20should%20work%20but%20I%20am%20not%20sure%20how%20to%20change%20the%20query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20chart%20this%20result%20the%20Sessions%20are%20summed%20up%20for%20each%20server.%26nbsp%3B%3C%2FP%3E%3CP%3EServer%201%2026%3C%2FP%3E%3CP%3EServer%202%2032%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAppreciate%20any%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%3C%2FP%3E%3CP%3EJoachim%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1046611%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1051225%22%20slang%3D%22en-US%22%3ERe%3A%20Latest%20Data%20Set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1051225%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F477482%22%20target%3D%22_blank%22%3E%40JoachimHans%3C%2FA%3E%2C%26nbsp%3Bmaybe%20not%20the%20best%20approach%2C%20but%20I%20believe%20it%20would%20work%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3Elet%20LatestHostnameGenerated%20%3D%20VDIUserSessions_CL%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20max(TimeGenerated)%20%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20SessionHostName_s%3B%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3EVDIUserSessions_CL%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20project%20TimeGenerated%2C%20TenantName%3DTenantName_s%2C%20HostPoolName%3DHostPoolName_s%20%2C%20SessionHostName%3DSessionHostName_s%20%2C%20UserPrincipalName%3DUserPrincipalName_s%20%2C%20AdUserName%3DAdUserName_s%20%2C%20LoginTimeUTC%3DCreateTime_t%20%2C%20SessionState%3DSessionState_s%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%7C%20summarize%20count(SessionState)%20by%20TenantName%2CHostPoolName%2CSessionHostName%2CTimeGenerated%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ejoin%3C%2FSPAN%3E%3CSPAN%3E%20kind%3Dinner%20(LatestHostnameGenerated)%20%3C%2FSPAN%3E%3CSPAN%3Eon%3C%2FSPAN%3E%26nbsp%3B(%3CSPAN%3E%24%3C%2FSPAN%3E%3CSPAN%3Eleft.TimeGenerated%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%24%3C%2FSPAN%3E%3CSPAN%3Eright.max_TimeGenerated%20and%20%24left.SessionHostName%20%3D%3D%20%24right.SessionHostName_s)%3C%2FSPAN%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject-away%3C%2FSPAN%3E%3CSPAN%3E%20SessionHostName_s%2C%20max_TimeGenerated%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1052234%22%20slang%3D%22en-US%22%3ERe%3A%20Latest%20Data%20Set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1052234%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F453722%22%20target%3D%22_blank%22%3E%40hspinto%3C%2FA%3EThanks%20for%20replying.%20This%20is%20also%20an%20approach%20I%20thought%20about%20but%20the%20first%20statement%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elet%20LatestHostnameGenerated%20%3D%20VDIUserSessions_CL%20%7C%20summarize%20max(TimeGenerated)%20by%20SessionHostName_s%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethrows%20an%20error%20%22No%20tabular%20expression%20statement%20found%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20idea%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EJoachim%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1052240%22%20slang%3D%22en-US%22%3ERe%3A%20Latest%20Data%20Set%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1052240%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F477482%22%20target%3D%22_blank%22%3E%40JoachimHans%3C%2FA%3E%26nbsp%3B%20It%20works.%20My%20fault.%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20not%20aware%20that%20this%20only%20works%20if%20you%20enter%20the%20whole%20%22query%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi

I really tried it a couple of hours but no more ideas. The scenario is as follows.

 

I collect session data for a virtual desktop farm to display number of users connected to each server. 

 

VDIUserSessions_CL
| project TimeGenerated, TenantName=TenantName_s, HostPoolName=HostPoolName_s , SessionHostName=SessionHostName_s , UserPrincipalName=UserPrincipalName_s , AdUserName=AdUserName_s , LoginTimeUTC=CreateTime_t , SessionState=SessionState_s
| summarize count(SessionState) by TenantName,HostPoolName,SessionHostName,TimeGenerated

 

The table output is correct. I get something like

 

TimeHostnameNumberOfSessions 
‎12‎/‎4‎/‎2019‎ ‎8‎:‎17‎:‎16.167‎ ‎AMServer 111 
‎12‎/‎4‎/‎2019‎ ‎8‎:‎17‎:‎16.167‎ ‎AMServer 220 
‎12‎/‎4‎/‎2019‎ 6:10:‎16.167‎ ‎AMServer 115 
12‎/‎4‎/‎2019‎ 6:10:‎16.167‎ ‎AMServer 212 

 

First question I have. I am only interested in the last information and not in the history, so I thought something like max(TimeGenerated) should work but I am not sure how to change the query.

 

When I chart this result the Sessions are summed up for each server. 

Server 1 26

Server 2 32

 

Appreciate any help.

 

Best regards

Joachim

 

 

3 Replies
Highlighted
Solution

@JoachimHans, maybe not the best approach, but I believe it would work like this:

 

let LatestHostnameGenerated = VDIUserSessions_CL | summarize max(TimeGenerated) by SessionHostName_s;
 
VDIUserSessions_CL
| project TimeGenerated, TenantName=TenantName_s, HostPoolName=HostPoolName_s , SessionHostName=SessionHostName_s , UserPrincipalName=UserPrincipalName_s , AdUserName=AdUserName_s , LoginTimeUTC=CreateTime_t , SessionState=SessionState_s
| summarize count(SessionState) by TenantName,HostPoolName,SessionHostName,TimeGenerated
| join kind=inner (LatestHostnameGenerated) on ($left.TimeGenerated == $right.max_TimeGenerated and $left.SessionHostName == $right.SessionHostName_s)
| project-away SessionHostName_s, max_TimeGenerated
Highlighted

@hspintoThanks for replying. This is also an approach I thought about but the first statement

 

let LatestHostnameGenerated = VDIUserSessions_CL | summarize max(TimeGenerated) by SessionHostName_s;

 

throws an error "No tabular expression statement found".

 

Any idea?

 

Thanks

Joachim

 


 

Highlighted

@JoachimHans  It works. My fault. :)

 

I was not aware that this only works if you enter the whole "query". 

 

Best regards