Get AVD Client Version, Latest Version and Delta Between Versions

Azure, Azure Virtual Desktops, Microsoft

Get AVD Client Version, Latest Version and Delta Between Versions

Kyle Wise
February 6, 2024

This query is from AVD Insights workbook that you can create your own workbook or use it with PowerBi, LogicApp or PowerAutomate to get client versions used for Azure Virtual Desktop and the delta between the latest client version available. This query provides information for Windows, MacOS, IOS, HTML, Android.

let LatestClientsReleaseTimes =
    WVDConnections
    | where "*" == "*" or "*" == ClientType
    | summarize FirstSeen=min(TimeGenerated) by ClientType, ClientVersion
    //| join kind=fullouter
    //(
    //   WVDFeeds
    //    | where _ResourceId =~  and ("*" == "*" or "*" == ClientType)
    //    | summarize FirstSeen=min(TimeGenerated) by ClientType, ClientVersion
    //) on ClientType, ClientVersion
    //| project ClientType, ClientVersion, FirstSeen
    | extend ExtendedVersion=strcat(ClientVersion, '.0.0.0.0')
    | extend FirstFourSegments=indexof(ExtendedVersion, ".", indexof(ExtendedVersion, ".", indexof(ExtendedVersion, ".", indexof(ExtendedVersion, ".") + 1) + 1) + 1)
    | extend NormalizedVersion=substring(ExtendedVersion, 0, FirstFourSegments)
    | as VersionCatalog
    | join kind=inner
        (
        VersionCatalog
        | distinct ClientType, NormalizedVersion
        | extend SplitVersion=split(NormalizedVersion, '.')
        | order by
            ClientType,
            toint(SplitVersion[0]) desc,
            toint(SplitVersion[1]) desc,
            toint(SplitVersion[2]) desc,
            toint(SplitVersion[3]) desc
        | extend VersionSortIndex=row_number(1, prev(ClientType) != ClientType)
        | project ClientType, NormalizedVersion, VersionSortIndex
        )
        on ClientType, NormalizedVersion
    | project ClientType, ClientVersion, FirstSeen, VersionSortIndex;
WVDConnections
| where TimeGenerated > ago(2d) and ("*" == "*" or "*" == ClientType)
| summarize ConnectionCount=dcount(CorrelationId), LastSeen=max(TimeGenerated) by UserName, ClientType, ClientVersion//
//| join kind=fullouter
//(
//    WVDFeeds
//    | where TimeGenerated > ago(2d) and _ResourceId =~ and ("*" == "*" or "*" == ClientType)
//    | summarize RefreshCount=dcount(CorrelationId), LastSeen=max(TimeGenerated) by UserName, ClientType, ClientVersion
//) on UserName, ClientType, ClientVersion
//| project UserName=coalesce(UserName, UserName1), ClientType=coalesce(ClientType, ClientType1), ClientVersion=coalesce(ClientVersion, ClientVersion1), LastSeen=max_of(LastSeen1, LastSeen), Connections=coalesce(ConnectionCount, 0), Refreshes=coalesce(RefreshCount, 0)
//| project-away *1
| project UserName, ClientType, ClientVersion, LastSeen, Connections=ConnectionCount
| as UserData
| join kind=leftouter
    (
    UserData
    | extend ExtendedVersion=strcat(ClientVersion, '.0.0.0.0')
    | extend FirstFourSegments=indexof(ExtendedVersion, ".", indexof(ExtendedVersion, ".", indexof(ExtendedVersion, ".", indexof(ExtendedVersion, ".") + 1) + 1) + 1)
    | extend NormalizedVersion=substring(ExtendedVersion, 0, FirstFourSegments)
    | distinct ClientType, ClientVersion, NormalizedVersion
    | as VersionCatalog
    | join kind=inner
        (
        VersionCatalog
        | distinct ClientType, NormalizedVersion
        | extend SplitVersion=split(NormalizedVersion, '.')
        | order by
            ClientType,
            toint(SplitVersion[0]) desc,
            toint(SplitVersion[1]) desc,
            toint(SplitVersion[2]) desc,
            toint(SplitVersion[3]) desc
        | extend VersionSortIndex=row_number(1, prev(ClientType) != ClientType)
        | project ClientType, NormalizedVersion, VersionSortIndex
        )
        on ClientType, NormalizedVersion
    | project ClientType, ClientVersion, VersionSortIndex
    )
    on ClientType, ClientVersion
| project-away *1
| summarize arg_min(VersionSortIndex, *) by UserName, ClientType
| where VersionSortIndex > 1
| extend VersionsBehind=VersionSortIndex - 1
| join kind=inner  
    (
    LatestClientsReleaseTimes
    | where VersionSortIndex == 1
    )
    on ClientType
| extend TimeDelta=LastSeen - FirstSeen
| where TimeDelta > 1d
| project
    UserName,
    ClientType,
    ClientTypeShort=replace("com.microsoft.rdc.", "", ClientType),
    ClientVersion,
    LastSeen,
    Connections,
    LatestVersion=ClientVersion1,
    FirstSeen,
    TimeDelta=TimeDelta / 1d
| extend RowLabel=strcat(ClientTypeShort, " - Newest: ", LatestVersion)
| sort by TimeDelta desc
Share or Save this:
Share