Azure KQL列表过滤报错：筛选无日志服务器应用失败

我想要通过一个列表过滤另一个列表，目标是获取服务器上未产生任何日志的应用列表。为此定义了两个变量：

• appsInServer：存储服务器上的所有应用列表
• appsBeingLogged：存储有日志记录的应用列表

单独执行生成这两个列表的查询均正常，但最终用于过滤的查询报错（错误信息见附图）。

报错的KQL查询：

let appsInServer = arg("").resources
| where type == 'microsoft.web/sites' and properties.serverFarmId contains ('ASP-CPRD-CardiffApp-Hybrid01')
| extend serverName = case(properties.serverFarmId contains ('ASP-CPRD-CardiffApp-Hybrid01'), 'Hybrid01', 'Unkown')
| project  Name = tolower(tostring(name)); 
let appsBeingLogged = AppPerformanceCounters 
| extend Name=replace_string(tostring(split(_ResourceId, "/")[-1]), "appi-cprd-", "")
| where ((Category == "Process" and Counter == "% Processor Time Normalized") or Name == "processCpuPercentage")
| summarize by Name
| sort by Name
| project Name = strcat("app-cprd-", tolower(Name));
appsInServer
| where Name !in~ (appsBeingLogged)
| summarize by Name
| sort by Name
| project Name;

我确认过滤逻辑本身可行，因为以下类似查询能正常执行：

let appsInServer = arg("").resources
| where type == 'microsoft.web/sites' and properties.serverFarmId contains ('ASP-CPRD-CardiffApp-Hybrid01')
| extend serverName = case(properties.serverFarmId contains ('ASP-CPRD-CardiffApp-Hybrid01'), 'Hybrid01', 'Unkown')
| project  Name = tolower(tostring(name)); 
AppPerformanceCounters 
| extend Name=replace_string(tostring(split(_ResourceId, "/")[-1]), "appi-cprd-", "")
| where ((Category == "Process" and Counter == "% Processor Time Normalized") or Name == "processCpuPercentage")
| where strcat("app-cprd-", tolower(Name)) in~ (appsInServer)
| summarize AvgCPUPercentage = sum(todouble(Value)) / count() by _ResourceId, Name
| sort by  AvgCPUPercentage
| project Name, round(AvgCPUPercentage, 2)

问题出在!in~ (appsBeingLogged)的用法上。虽然in~支持传入表作为参数，但!in~在处理表对象时容易触发语法或执行层面的错误，更可靠的方式是使用**左反连接（left anti join）**实现“不在目标列表中”的过滤逻辑。

修正后的查询如下：

let appsInServer = arg("").resources
| where type == 'microsoft.web/sites' and properties.serverFarmId contains ('ASP-CPRD-CardiffApp-Hybrid01')
| extend serverName = case(properties.serverFarmId contains ('ASP-CPRD-CardiffApp-Hybrid01'), 'Hybrid01', 'Unkown')
| project  Name = tolower(tostring(name)); 
let appsBeingLogged = AppPerformanceCounters 
| extend Name=replace_string(tostring(split(_ResourceId, "/")[-1]), "appi-cprd-", "")
| where ((Category == "Process" and Counter == "% Processor Time Normalized") or Name == "processCpuPercentage")
| summarize by Name
| sort by Name
| project Name = strcat("app-cprd-", tolower(Name));
// 通过左反连接筛选未产生日志的应用
appsInServer
| join kind=leftanti (appsBeingLogged) on Name
| sort by Name
| project Name;

关键说明 #

• join kind=leftanti会返回appsInServer中所有在appsBeingLogged内无匹配记录的行，精准实现“未产生日志的应用”查询需求。
• 相比!in~，左反连接在处理大数据集时性能更稳定，是KQL中实现“排除匹配项”场景的最佳实践。
• 原查询中的summarize by Name可直接移除，因为appsInServer已经是去重后的应用列表，左反连接后无需再次汇总。

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