如何合并Azure Defender for Cloud的两个KQL查询实现类Azure可视化？

我有两个分别针对microsoft.security/assessments和microsoft.security/securescores/securescorecontrols的KQL查询，想把它们合并，实现类似Azure原生的可视化效果——因为Defender for Cloud里的评估始终隶属于安全评分控制。尝试用join但不知道关联条件，也不太熟悉join的用法。

原始查询 #

microsoft.security/assessments 查询 #

securityresources
| where type == "microsoft.security/assessments"
| extend name = properties.displayName
| extend resourceDetails = properties.resourceDetails
| extend resourceName = resourceDetails.ResourceName
| extend statusDetails = properties.status
| extend status = statusDetails.code
| extend metadata = properties.metadata
| extend severity = metadata.severity
| join kind=leftouter  ( 
    resourcecontainers
        | where type == "microsoft.resources/subscriptions"
        | extend resolvedSubId = tostring(split(id, '/', 2)[0]), subscriptionName = name
        | project resolvedSubId, subscriptionName
) on $left.subscriptionId == $right.resolvedSubId
| project name, subscriptionName, resourceName, status,severity

microsoft.security/securescores/securescorecontrols 查询 #

securityresources
    | where type == "microsoft.security/securescores/securescorecontrols"
    | extend name = properties.displayName
    | extend healthy = properties.healthyResourceCount
    | extend unhealthy = properties.unhealthyResourceCount
    | extend notApplicable = properties.notApplicableResourceCount
    | extend score = properties.score
    | extend scr= parse_json(score)
    | project name, healthy, unhealthy, notApplicable, CurrentScore=scr.current, MaxScore=scr.max

合并查询方案 #

要关联这两类数据，核心是利用microsoft.security/assessments中的properties.secureControlId字段——这个字段正好对应microsoft.security/securescores/securescorecontrols的资源name（注意是资源逻辑ID，不是显示名称）。

以下是合并后的查询，用左外连接保留所有评估数据，同时关联对应的安全评分控制信息：

// 提取评估数据，保留用于关联的secureControlId
let assessments = securityresources
| where type == "microsoft.security/assessments"
| extend assessmentName = properties.displayName
| extend resourceDetails = properties.resourceDetails
| extend resourceName = resourceDetails.ResourceName
| extend statusDetails = properties.status
| extend status = statusDetails.code
| extend metadata = properties.metadata
| extend severity = metadata.severity
| extend secureControlId = properties.secureControlId
| join kind=leftouter  ( 
    resourcecontainers
        | where type == "microsoft.resources/subscriptions"
        | extend resolvedSubId = tostring(split(id, '/', 2)[0]), subscriptionName = name
        | project resolvedSubId, subscriptionName
) on $left.subscriptionId == $right.resolvedSubId
| project assessmentName, subscriptionName, resourceName, status, severity, secureControlId;

// 提取安全评分控制数据，用资源name作为关联ID
let secureScoreControls = securityresources
| where type == "microsoft.security/securescores/securescorecontrols"
| extend controlDisplayName = properties.displayName
| extend healthy = properties.healthyResourceCount
| extend unhealthy = properties.unhealthyResourceCount
| extend notApplicable = properties.notApplicableResourceCount
| extend score = properties.score
| extend scr= parse_json(score)
| project secureControlId = name, controlDisplayName, healthy, unhealthy, notApplicable, CurrentScore=scr.current, MaxScore=scr.max;

// 关联两个数据集，输出整合后的字段
assessments
| join kind=leftouter secureScoreControls on secureControlId
| project assessmentName, controlDisplayName, subscriptionName, resourceName, status, severity, healthy, unhealthy, notApplicable, CurrentScore, MaxScore

关键说明 #

• 用let语句给子查询起别名，让逻辑更清晰
• 评估查询中提取的secureControlId是关联的核心，它和安全控制资源的name字段完全匹配
• 左外连接（kind=leftouter）确保所有评估数据都被保留，避免因关联缺失丢失信息（实际场景中这种情况极少）
• 重命名了部分字段（比如assessmentName、controlDisplayName），避免显示名称冲突，提升可读性

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