编写KQL查询获取30天未打补丁的Azure VM遇解析错误求助

一、Let语句查询解析失败（ParserFailure）的修复 #

错误原因 #

1. Parse语法错误：原查询使用parse ["id"]不符合KQL规范，id是字符串类型字段，直接用parse id即可，无需动态字段访问写法。
2. Join条件逻辑错误：Table01和Table02按VmName、subscriptionId聚合后，arg_min和arg_max返回的id是不同补丁评估记录的ID，用这类ID做等值关联逻辑错误，同时可能触发解析器异常。

修复后的Let语句查询 #

let PatchData = patchassessmentresources
| parse id with * "virtualMachines/" VmName "/patchAssessmentResults/" *
| extend publishedDateTime = todatetime(properties.publishedDateTime)
| extend patchName = properties.patchName
| extend kbId = properties.kbId
| extend patchId = properties.patchId
| extend TimeGenerated = todatetime(properties.lastModifiedDateTime)
| where TimeGenerated > ago(31d)
| where type == "microsoft.compute/virtualmachines/patchassessmentresults/softwarepatches"
| extend classification = iff(properties.classifications[0] =~ "UpdateRollUp", "UpdateRollup", iff(isempty(properties.classifications[0]), "Unsupported", properties.classifications[0]))
| where classification contains "critical" or classification contains "security" or classification contains "Other";
let Table01 = PatchData
| summarize FirstTimeSeen = arg_min(TimeGenerated, *) by VmName, subscriptionId;
let Table02 = PatchData
| summarize LastTimeSeen = arg_max(TimeGenerated, *) by VmName, subscriptionId;
Table01
| join kind=inner Table02 on VmName, subscriptionId

说明：

• 先定义通用PatchData表，避免重复代码，降低解析器负担。
• 修正parse语法，直接使用id字段。
• 将Join条件改为VmName和subscriptionId，匹配聚合表的业务关联键。

二、嵌套Join查询Tags为空的修复 #

错误原因 #

原查询中，第一个join后的id是补丁评估结果的ID，而resources表的id是Azure虚拟机的资源ID，两者完全不匹配，导致关联失败，tags字段为空。

修复后的嵌套Join查询 #

patchassessmentresources
| parse id with * "virtualMachines/" VmName "/patchAssessmentResults/" *
// 解析出VM的完整资源ID，用于关联resources表
| extend vmResourceId = strcat(split(id, "/virtualMachines/")[0], "/virtualMachines/", VmName)
| extend publishedDateTime = todatetime(properties.publishedDateTime)
| extend patchName = properties.patchName
| extend kbId = properties.kbId
| extend patchId = properties.patchId
| extend TimeGenerated = todatetime(properties.lastModifiedDateTime)
| where TimeGenerated > ago(31d)
| where type == "microsoft.compute/virtualmachines/patchassessmentresults/softwarepatches"
| extend classification = iff(properties.classifications[0] =~ "UpdateRollUp", "UpdateRollup", iff(isempty(properties.classifications[0]), "Unsupported", properties.classifications[0]))
| where classification contains "critical" or classification contains "security" or classification contains "Other" 
| summarize FirstTimeSeen = arg_min(TimeGenerated, *) by VmName, subscriptionId, vmResourceId
| join kind=leftouter (
    patchassessmentresources
    | parse id with * "virtualMachines/" VmName "/patchAssessmentResults/" *
    | extend vmResourceId = strcat(split(id, "/virtualMachines/")[0], "/virtualMachines/", VmName)
    | extend publishedDateTime = todatetime(properties.publishedDateTime)
    | extend patchName = properties.patchName
    | extend kbId = properties.kbId
    | extend patchId = properties.patchId
    | extend TimeGenerated = todatetime(properties.lastModifiedDateTime)
    | where TimeGenerated > ago(31d)
    | where type == "microsoft.compute/virtualmachines/patchassessmentresults/softwarepatches"
    | extend classification = iff(properties.classifications[0] =~ "UpdateRollUp", "UpdateRollup", iff(isempty(properties.classifications[0]), "Unsupported", properties.classifications[0]))
    | where classification contains "critical" or classification contains "security" or classification contains "Other" 
    | summarize LastTimeSeen = arg_max(TimeGenerated, *) by VmName, subscriptionId, vmResourceId
    ) on VmName, subscriptionId, vmResourceId
| join kind=leftouter (
    resources
    | where type =~ "microsoft.compute/virtualmachines"
    | extend OsType = properties.storageProfile.osDisk.osType
    | extend vmName = name
    | project vmName, id, location, tags, OsType
    ) on $left.vmResourceId == $right.id
| extend DifferenceBetweenFirstandLast = datetime_diff("day", LastTimeSeen, FirstTimeSeen)
| project VmName, subscriptionId, resourceGroup, location, FirstTimeSeen, LastTimeSeen, DifferenceBetweenFirstandLast, vmResourceId, patchName, patchId, classification, tags

说明：

1. 解析VM资源ID：通过split和strcat从补丁评估记录的id中提取VM完整资源ID（vmResourceId），确保能和resources表的id正确关联。
2. 修正Join条件：第一个Join用VmName、subscriptionId、vmResourceId作为关联键，第二个Join用vmResourceId匹配resources表的id，保证VM的tags能正确返回。
3. 调整输出字段：将原id替换为vmResourceId，避免混淆补丁ID和VM ID。

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