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

Joshua Hensley 21 Reputation points
2024-11-11T23:53:53.5633333+00:00

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.

Azure Virtual Desktop
Azure Virtual Desktop
A Microsoft desktop and app virtualization service that runs on Azure. Previously known as Windows Virtual Desktop.
1,599 questions
Microsoft Sentinel
Microsoft Sentinel
A scalable, cloud-native solution for security information event management and security orchestration automated response. Previously known as Azure Sentinel.
1,181 questions
Microsoft Entra ID
Microsoft Entra ID
A Microsoft Entra identity service that provides identity management and access control capabilities. Replaces Azure Active Directory.
22,450 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Mounika Reddy Anumandla 905 Reputation points Microsoft Vendor
    2024-11-12T06:03:45.9233333+00:00

    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."


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.