Azure Virtual Desktop KQL (Kusto) query

Question

I 'm looking for a KQL (Kusto) query that gives me the total number of users per application or per application group for an Azure Virtual Desktop hostpool from the Log Analytics Workspace. This query gives the total number of users per hostpool, but I would to distinct that further:

WVDConnections
| where TimeGenerated > ago(7d) 
| where State == "Connected" 
| project _ResourceId, UserName
| project-rename Hostpool = _ResourceId 
| summarize DistinctUsers= dcount(UserName) by Hostpool
| extend HostPool=toupper(strcat(split(Hostpool, "/")[4], ".", split(Hostpool, "/")[8]))
| project HostPool, DistinctUsers

Thanks!

It is not giving enough information.

Answer 1

Have you already tried this (the datatable is only for testing purposes):

let WVDConnections = datatable(TimeGenerated: datetime, UserName: string, ApplicationGroupName: string)
[
    datetime(2024-07-01 08:00:00), "[email protected]", "DesktopGroup1",
    datetime(2024-07-02 09:00:00), "[email protected]", "DesktopGroup1",
    datetime(2024-07-03 10:00:00), "[email protected]", "RemoteAppGroup1",
    datetime(2024-07-04 11:00:00), "[email protected]", "RemoteAppGroup1",
    datetime(2024-07-05 12:00:00), "[email protected]", "DesktopGroup2",
    datetime(2024-07-07 13:00:00), "[email protected]", "RemoteAppGroup2",
    datetime(2024-07-07 14:00:00), "[email protected]", "RemoteAppGroup2",
    datetime(2024-07-08 15:00:00), "[email protected]", "DesktopGroup1",
    datetime(2024-07-09 16:00:00), "[email protected]", "DesktopGroup2",
    datetime(2024-07-10 17:00:00), "[email protected]", "RemoteAppGroup2"
];
WVDConnections
| where TimeGenerated >= ago(10d) 
| summarize Users = make_set(UserName) by ApplicationGroupName
| project ApplicationGroupName, Users

Sample code here.