如何在Azure Data Explorer中遍历CVE JSON数组生成新行？

需求是从Defender for Cloud Qualys扫描器中提取信息，为每行数据里的每个CVE单独生成一行，同时关联虚拟机名称与QID，预期输出格式如下：

virtualMachineName,cvetitle,QID
computer1,CVE-2022-21123,48585
computer1,CVE-2022-21125,48585
computer1,CVE-2022-21127,48585
computer1,CVE-2022-21166,48585
computer2,CVE-2022-21125,48585
computer2,CVE-2010-38244,39459
computer3,CVE-2009-83492,39459

当前Kusto查询仅能获取第一个CVE条目，需要修改查询以获取所有CVE并为每个CVE生成对应新行，原查询代码如下：

securityresources
| where type =~ "microsoft.security/assessments/subassessments"
| extend QID=tostring(properties.id)
| where properties.additionalData.source =~ "Built-in Qualys vulnerability assessment"
| extend vulnerabilityName=tostring(properties.displayName),
            vulnerabilityType = tostring(properties.additionalData.assessedResourceType),
            virtualMachineName=split(properties.resourceDetails.id, "/")[-1],
            allCves = properties.cve
| extend cvetitle = properties['additionalData']['cve'][0]['title']
| project QID, vulnerabilityName, vulnerabilityType, virtualMachineName, cvetitle, allCves

修改后的查询代码 #

securityresources
| where type =~ "microsoft.security/assessments/subassessments"
| extend QID=tostring(properties.id)
| where properties.additionalData.source =~ "Built-in Qualys vulnerability assessment"
| extend virtualMachineName=split(properties.resourceDetails.id, "/")[-1]
// 展开CVE数组，每个元素生成单独一行
| mv-expand cve = properties.additionalData.cve
| extend cvetitle = tostring(cve.title)
// 保留需要的列
| project virtualMachineName, cvetitle, QID

关键修改说明 #

• 移除了原查询中仅提取第一个CVE的extend cvetitle = properties['additionalData']['cve'][0]['title']语句
• 使用mv-expand运算符展开properties.additionalData.cve数组，将数组中的每个CVE对象拆分为单独的数据行
• 展开后直接从每个CVE对象中提取title字段作为cvetitle
• 最终仅保留需求所需的virtualMachineName、cvetitle和QID列，完全匹配预期输出格式

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