Azure资源图KQL多查询关联：生成含订阅名称的安全报告

你的尝试仅通过subscriptionId关联数据，会导致同一订阅下多个资源组的信息错误匹配——安全评估记录属于特定资源组，必须同时用subscriptionId和resourceGroup作为关联键，才能准确匹配到对应资源组的SystemID标签。以下是修正后的完整方案：

完整KQL查询 #

// 1. 获取安全评估数据，并从resourceId中提取资源组名称
let securityAssessments = SecurityResources
| where type == 'microsoft.security/assessments'
| extend 
    resourceId = id,
    recommendationId = name,
    recommendationName = properties.displayName,
    source = properties.resourceDetails.Source,
    recommendationState = properties.status.code,
    description = properties.metadata.description,
    assessmentType = properties.metadata.assessmentType,
    remediationDescription = properties.metadata.remediationDescription,
    policyDefinitionId = properties.metadata.policyDefinitionId,
    implementationEffort = properties.metadata.implementationEffort,
    recommendationSeverity = properties.metadata.severity,
    category = properties.metadata.categories,
    userImpact = properties.metadata.userImpact,
    threats = properties.metadata.threats,
    portalLink = properties.links.azurePortal,
    // 用内置函数解析resourceId，准确提取资源组名称
    resourceGroup = parse_resource_id(resourceId).resourceGroup
| project tenantId, subscriptionId, resourceId, resourceGroup, recommendationName, recommendationId, recommendationState, recommendationSeverity, description, remediationDescription, assessmentType, policyDefinitionId, implementationEffort, userImpact, category, threats, source, portalLink;

// 2. 获取订阅名称、资源组及对应的SystemID标签
let subResourceGroups = resourcecontainers
| where type == 'microsoft.resources/subscriptions'
| project subscriptionId, subscriptionName = name
| join kind=inner (
    resourcecontainers
    | where type == 'microsoft.resources/resourcegroups'
    | project subscriptionId, resourceGroup = name, systemID = tostring(tags.SystemID)
) on subscriptionId
| project subscriptionId, subscriptionName, resourceGroup, systemID;

// 3. 关联两个数据集，保留所有安全评估记录
securityAssessments
| join kind=leftouter subResourceGroups on subscriptionId, resourceGroup
| project tenantId, subscriptionId, subscriptionName, resourceGroup, resourceId, recommendationName, recommendationId, recommendationState, recommendationSeverity, description, remediationDescription, assessmentType, policyDefinitionId, implementationEffort, userImpact, category, threats, source, portalLink, systemID

关键修正点 #

• 使用parse_resource_id内置函数从resourceId中提取资源组名称，避免手动拆分字符串的误差
• 关联时同时使用subscriptionId和resourceGroup作为匹配键，确保每个安全评估记录对应到正确的资源组标签
• 用let语句拆分模块化查询，提升可读性和维护性
• 采用leftouter join，确保即使订阅/资源组信息缺失（如资源组已删除），安全评估记录依然会被保留

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