Latest Data Set

[JoachimHans]

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

Time	Hostname	NumberOfSessions
‎12‎/‎4‎/‎2019‎ ‎8‎:‎17‎:‎16.167‎ ‎AM	Server 1	11
‎12‎/‎4‎/‎2019‎ ‎8‎:‎17‎:‎16.167‎ ‎AM	Server 2	20
‎12‎/‎4‎/‎2019‎ 6:10:‎16.167‎ ‎AM	Server 1	15
12‎/‎4‎/‎2019‎ 6:10:‎16.167‎ ‎AM	Server 2	12

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

[#2 · Accepted 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

[#3]

@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

[#4]

@JoachimHans  It works. My fault. :)

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

Best regards