Azure AVD主机池KQL关联查询无结果问题排查求助

我编写了用于查询Azure AVD主机池用户CPU和内存利用率的KQL查询，该查询由WVDConnections、Processor Perf、Memory Perf三部分组成。各部分单独运行正常，但合并为完整查询后，在Log Analytics工作区执行时，无论选择24小时还是30分钟时间范围，均提示“过去24小时未找到结果，请选择其他时间范围”。我期望输出UserName、CPUUtilization、MemoryUtilization字段，请问是否遗漏了什么？

原查询代码：

//list all users in a hostpool
WVDConnections
| where _ResourceId =~ 'resource id of hostpool'

//CPU and memory utilization
Perf
    | where ObjectName == "Processor" and CounterName == "% Processor Time" and InstanceName == "_Total"
    | summarize CPUUtilization = avg(CounterValue) by TimeGenerated
) on TimeGenerated
| join kind=inner (
    Perf
    | where ObjectName == "Memory" and CounterName == "Available MBytes"
    | summarize MemoryUtilization = avg(CounterValue) by TimeGenerated
) on TimeGenerated
| project TimeGenerated, CPUUtilization, MemoryUtilization

//The whole query after joining both altogether
WVDConnections
| where _ResourceId =~ 'resource id of hostpool'
| project CorrelationId, State, TimeGenerated, UserName
| summarize TimeGenerated=min(TimeGenerated) by CorrelationId, State, UserName
| join kind=inner (
    Perf
    | where ObjectName == "Processor" and CounterName == "% Processor Time" and InstanceName == "_Total"
    | summarize CPUUtilization = avg(CounterValue) by TimeGenerated
) on TimeGenerated
| join kind=inner (
    Perf
    | where ObjectName == "Memory" and CounterName == "Available MBytes"
    | summarize MemoryUtilization = avg(CounterValue) by TimeGenerated
) on TimeGenerated
| project TimeGenerated, UserName, CPUUtilization, MemoryUtilization

问题分析与修复方案 #

核心问题 #

1. 精确时间匹配导致内连接失效：WVDConnections的TimeGenerated是会话建立的单个时间点，而Perf表的TimeGenerated是性能数据的采样时间，两者几乎不可能精确对齐，inner join会直接过滤掉所有不匹配的记录，导致无结果。
2. 未关联会话主机：Perf数据来自AVD的会话主机，但原查询没有将用户会话与对应的主机关联，无法把性能数据映射到具体用户。
3. 内存利用率逻辑错误：原查询用Available MBytes（可用内存）作为利用率，这和实际要的“已使用内存占比”逻辑相反，需要结合总内存计算。

修复后的查询代码 #

WVDConnections
| where _ResourceId =~ 'resource id of hostpool'
| where State == "Connected" // 只统计已连接的活跃会话
| project CorrelationId, UserName, HostName, SessionStartTime = TimeGenerated
// 关联会话主机的CPU性能数据，采用时间范围匹配而非精确匹配
| join kind=inner (
    Perf
    | where ObjectName == "Processor" and CounterName == "% Processor Time" and InstanceName == "_Total"
    | extend HostName = tolower(Computer) // 统一主机名大小写避免匹配失败
    | summarize CPUUtilization = avg(CounterValue) by HostName, bin(TimeGenerated, 1m) // 按1分钟粒度聚合性能数据
) on $left.HostName == $right.HostName
| where TimeGenerated between (SessionStartTime .. SessionStartTime + 10m) // 匹配会话建立后10分钟内的性能数据，可根据需要调整
// 关联内存利用率数据
| join kind=inner (
    Perf
    | where ObjectName in ("Memory", "PhysicalDisk") // 获取总内存和可用内存
    | where CounterName in ("Available MBytes", "Total Physical Memory")
    | extend HostName = tolower(Computer)
    | summarize memory_data = make_bag(pack(CounterName, CounterValue)) by HostName, bin(TimeGenerated, 1m)
    | extend AvailableMemory = toreal(memory_data["Available MBytes"]), TotalMemory = toreal(memory_data["Total Physical Memory"])
    | extend MemoryUtilization = iff(TotalMemory > 0, (TotalMemory - AvailableMemory)/TotalMemory * 100, 0) // 计算已使用内存占比
    | project HostName, TimeGenerated, MemoryUtilization
) on HostName, TimeGenerated
| summarize 
    AvgCPU = avg(CPUUtilization),
    AvgMemory = avg(MemoryUtilization)
by UserName, HostName
| project UserName, HostName, CPUUtilization = round(AvgCPU, 2), MemoryUtilization = round(AvgMemory, 2)

关键优化点 #

• 用时间范围匹配替代精确时间点join，确保会话和对应时间段的性能数据能关联上
• 通过HostName关联用户会话与对应的会话主机，保证性能数据属于该用户所在的主机
• 修正内存利用率计算逻辑，输出实际的已使用内存占比
• 按用户和主机聚合平均数据，结果更具参考性

内容的提问来源于stack exchange，提问作者tehais