Looking for query where we can get the following data from Azure Virtual Desktop under a particular host pool

Question

Looking for query where we can get the following data from Azure Virtual Desktop under a particular host pool.

1. Who has not logged in over the past 30 days
2. For those who have logged in, how many days did they log in
3. What is the amount of time users are logged in

Logged in = active RDP session

We've been playing around with something like this but not getting exactly what we want:

WVDConnections 
| where TimeGenerated > ago(30d) 
| where State == "Connected"  
| project CorrelationId , UserName, ConnectionType , StartTime=TimeGenerated  
| join (WVDConnections  
    | where State == "Completed"  
    | project EndTime=TimeGenerated, CorrelationId)  
    on CorrelationId  
| project Duration = EndTime - StartTime, ConnectionType, UserName, format_datetime(StartTime,'MM-dd-yyyy')
| sort by Duration desc

Any help would be appreciated.

Answer 1

Hi Joshua Hensley,

Welcome to the Microsoft Q&A Platform. Thank you for posting your query here

Based upon your question, you can follow these steps to find out users who have not accessed Azure Virtual Desktop (AVD) in last 30 days using Azure Monitor and Log Analytics

Prerequisites: - Make sure you have contributor or monitoring Reader permissions on the log analytics workspace is enabled ensure AVD diagnostics are configured to send logs to your log analytics workspace first. Go to the azure monitor and then log analytics workspace. Please refer this document to configure Diagnostic Settings for Azure Virtual Desktop.

You can review the logs to track user activity using Kusto Query Language (KQL). To find users who haven’t logged in for at least 30 days, use this query:

AVDActivityLogs 
| where TimeGenerated < ago(30d) 
| summarize LastSignIn=max(TimeGenerated) by UserPrincipalName 
| where LastSignIn < ago(30d)

This query is used to filter the users with last sign-in activity as 30 days older. Review, and export the results as required.

If you do have a list of known users, you can use the following query to compare it with recent sign-in activity and identify those who haven't logged in within the past 20 days.

To find session duration by user, your query should work fine: https://learn.microsoft.com/en-us/azure/virtual-desktop/diagnostics-log-analytics

let Events = WVDConnections | where UserName == "userupn";
Events
| where State == "Connected"
| project CorrelationId , UserName, ResourceAlias , StartTime=TimeGenerated
| join (Events
| where State == "Completed"
| project EndTime=TimeGenerated, CorrelationId)
on CorrelationId
| project Duration = EndTime - StartTime, ResourceAlias
| sort by Duration asc

Ensure that your dataset (WVDConnections) contains all necessary columns and that timestamps are accurate.

If dealing with multiple users or longer timeframes, you might want to implement additional filtering or grouping based on other attributes like date or resource type.

If you have any further queries, do let us know.

If the answer is helpful, please click "Accept Answer" and "Upvote it."