How to join the 3 KQL tables?

Question

Hi all,

I want to get the inactive users data with seesionhostname, hostpoolname, lastlogindate,username

and i am trying to get it through KQL query which we need to join 3 tables

i am getting the below error

'join' operator: Failed to resolve column named '_ResourceId' Request id: 1f89431c-0f50-44cb-ba4d-48fc0010d07c

 while running the below queires

let RecentConnections =      WVDConnections     
| where TimeGenerated > ago(30d)  // Replace with your parameter     
| summarize lastLogin=max(TimeGenerated) by UserName, _ResourceId;
 

let InactiveUsers =      RecentConnections     
| where lastLogin < ago(7d)  // Replace with your parameter     
| project UserName, lastLogin, _ResourceId;
 

let InactiveComputers =      WVDHeartbeat     
| where TimeGenerated > ago(30d)  // Replace with your parameter     
| summarize LastActive = max(TimeGenerated) by Computer     
| where LastActive < ago(7d)  // Replace with your parameter     
| project LastActive, Computer;
 

// Combine inactive users with their last active computers InactiveUsers     
| join kind=inner (InactiveComputers) on InactiveUsers._ResourceId == InactiveComputers.Computer     
| project UserName, lastLogin, LastActive, _ResourceId;

 

please let me know how can i resolve it

it should have generate all details with 100% accuracy & should also work in log analytics workspace

Thanks in Advance

Answer 1

Hi @kavya madda

Try using the following

// Combine inactive users with their last active computers 
InactiveUsers     
| join kind=inner (InactiveComputers) on $left._ResourceId == $right.Computer
| project UserName, lastLogin, LastActive, _ResourceId;

---

EDIT 27 November 2024 Per our offline conversation, you were able to resolve your issue by combining WVDConnections and WVDAgentHealthStatus to get a list of inactive sessions,