Getting Last Logon Time for AVD Users: A Cleaner, Scalable Approach

How to pull reliable last-logon data for Azure Virtual Desktop users straight from Log Analytics — without hand-rolling REST calls or asking an LLM to stitch your query results back together.

Why last logon matters for AVD

If you run AVD at any real scale, ‘when did this user last actually log on’ turns out to be one of the most-asked questions in your environment. It shows up in three different conversations:

• Cost and licensing — reclaiming Windows 365 or AVD user licenses assigned to accounts that haven’t connected in months.
• Autoscale and capacity planning — knowing which users are active helps you size host pools and schedule scaling plans instead of guessing.
• Security and compliance — stale accounts that still have session access are exactly the kind of thing an auditor (or an attacker) cares about.

None of this data lives conveniently on the user object. AVD doesn’t expose a LastLogon property the way on-prem AD does. Instead, it’s buried in the WVDConnections table in your Log Analytics workspace — which means the only way to get it is a query, not a cmdlet parameter.

The common approach — and where it breaks

A well-known pattern for this (see Niels Kok’s write-up on nielskok.tech) is to grab an access token with Az.Accounts, call the Log Analytics REST API directly, and then write a correlation function — to stitch the raw response back together, since the API returns data as separate tables.columns and tables.rows arrays rather than clean objects.

That works, and it’s a reasonable way to learn how the API is shaped under the hood. But it has a few rough edges once you actually put it into a runbook or scheduled task:

• You’re manually reconstructing PSCustomObjects from column/row arrays every time the query shape changes.
• Raw REST calls mean you’re handling token refresh, retries, and pagination yourself.
• It’s built for one workspace and one ad-hoc run — there’s no clean path to looping across host pools or regions.

The good news: PowerShell already has a module that does the token handling and the column/row correlation for you. You don’t need to reinvent it.

Prerequisites

• Diagnostic settings enabled on your host pool(s), sending at least WVDConnections (and ideally WVDCheckpoints and WVDErrors) to a Log Analytics workspace.
• Log Analytics Reader (or Monitoring Reader) role on the workspace for the account or managed identity running the script.
• The Az.Accounts and Az.OperationalInsights modules — both are part of the standard Az module set.

A cleaner script: Invoke-AzOperationalInsightsQuery

Instead of calling the REST endpoint directly, use Invoke-AzOperationalInsightsQuery from Az.OperationalInsights. It authenticates using your existing Az context, runs the KQL query, and — this is the part that removes the whole correlation step — returns the results as ready-to-use PSObjects in a .Results property. No manual mapping of columns to rows required.

The KQL query

This pulls the most recent Connected event per user, which is the practical definition of ‘last logon’ for a remote session:

WVDConnections
| where TimeGenerated > ago(90d)
| where State == 'Connected'
| summarize LastLogon = arg_max(TimeGenerated, SessionHostName, ConnectionType, ResourceAlias) by UserName
| project UserName, LastLogon, SessionHostName, HostPool = ResourceAlias, ConnectionType
| sort by LastLogon desc

arg_max() does the heavy lifting here — for each UserName it keeps only the row with the latest TimeGenerated, along with whichever session host and connection type went with that row. That’s a single summarize instead of a manual ‘loop and compare timestamps’ pattern.

The full script

# Requires -Modules Az.Accounts, Az.OperationalInsights
param(
[Parameter(Mandatory)]
[string]$WorkspaceId,
[int]$DaysBack = 90,
[string]$HostPoolName,
[string]$ExportPath = “.\\AVD-LastLogon-$(Get-Date -Format yyyyMMdd).csv”
)
if (-not (Get-AzContext)) {
Connect-AzAccount | Out-Null
}
$hostPoolFilter = if ($HostPoolName) {
"| where ResourceAlias =~ '$HostPoolName'"
} else { "" }
$query = @”
WVDConnections
| where TimeGenerated > ago(${DaysBack}d)
| where State == ‘Connected’
$hostPoolFilter
| summarize LastLogon = arg_max(TimeGenerated, SessionHostName, ConnectionType, ResourceAlias) by UserName
| project UserName, LastLogon, SessionHostName, HostPool = ResourceAlias, ConnectionType
| sort by LastLogon desc
“@
$response = Invoke-AzOperationalInsightsQuery -WorkspaceId $WorkspaceId -Query $query
if (-not $response.Results) {
Write-Warning “No connection data returned for the last $DaysBack days.”
return
}
$response.Results |
Select-Object UserName, LastLogon, SessionHostName, HostPool, ConnectionType |
Tee-Object -Variable lastLogons |
Format-Table -AutoSize
$lastLogons | Export-Csv -Path $ExportPath -NoTypeInformation
Write-Host “Exported $($lastLogons.Count) records to $ExportPath”

No REST endpoint, no bearer token plumbing, no post-processing function to reassemble the response. Invoke-AzOperationalInsightsQuery already gives you .Results as objects you can pipe straight into Format-Table, Export-Csv, or a filter.

Taking it further

1. Flag genuinely stale accounts

Once you have LastLogon per user, cross-reference it against your full Entra ID user list (or your AVD application group assignments) to catch the users who never show up in WVDConnections at all — they’re not stale, they’ve never logged on, which is an even stronger signal for license reclamation.

$assignedUsers = Get-MgGroupMember -GroupId $entraGroupId | Select-Object -ExpandProperty AdditionalProperties 
$neverLoggedOn = $assignedUsers.userPrincipalName | Where-Object { $_ -notin $lastLogons.UserName }

2. Scale across host pools and regions

Wrap the workspace ID in a loop (or pull workspace IDs from Azure Resource Graph) so the same script covers every host pool without hardcoding anything. For a multi-region deployment, this is the difference between one dashboard and N manual runs.

3. Run it on a schedule, not by hand

Publish this as an Azure Automation runbook or an Azure Function on a timer trigger, using a managed identity with Log Analytics Reader scoped to the workspace. Feed the output into a storage account or straight into Power BI, and you’ve turned a one-off script into a recurring stale-account and license-utilization report — the kind of thing that’s genuinely useful for a Windows 365 or AVD cost review.

Wrap-up

The underlying data source is the same as the original approach — WVDConnections in Log Analytics — but swapping the raw REST call and manual correlation function for Invoke-AzOperationalInsightsQuery removes an entire layer of brittle plumbing. The KQL summarize by UserName with arg_max() does in one line what a hand-written PowerShell loop would take twenty to do, and it scales cleanly from a single ad-hoc query to a scheduled, multi-host-pool reporting job.