如何合并Azure资源图查询结果：合规状态+排除范围计数

要将策略合规状态查询和排除范围计数查询的结果合并，你可以使用Kusto查询语言的join操作，基于共同的策略分配标识（如policyAssignmentName）将两个数据集关联起来。以下是修正并合并后的完整查询：

// 定义排除范围计数的子查询
let excludedScopesQuery = policyresources
| where type == "microsoft.authorization/policyassignments"
| where name == "StorageminimumTLS1_2"
| project policyAssignmentName = name, excludedScopesCount = array_length(properties['notScopes']);

// 合规状态查询，修正语法错误后与子查询关联
PolicyResources
| where type =~ 'Microsoft.PolicyInsights/PolicyStates'
| extend complianceState = tostring(properties.complianceState)
| extend
resourceId = tostring(properties.resourceId),
policyAssignmentId = tostring(properties.policyAssignmentId),
policyAssignmentScope = tostring(properties.policyAssignmentScope),
policyAssignmentName = tostring(properties.policyAssignmentName),
policyDefinitionId = tostring(properties.policyDefinitionId),
policyDefinitionReferenceId = tostring(properties.policyDefinitionReferenceId),
stateWeight = iff(complianceState == 'NonCompliant', int(300), iff(complianceState == 'Compliant', int(200), iff(complianceState == 'Conflict', int(100), iff(complianceState == 'Exempt', int(50), int(0)))))
| where policyAssignmentName == 'StorageminimumTLS1_2'  
| summarize max(stateWeight) by resourceId, policyAssignmentId, policyAssignmentScope, policyAssignmentName
| summarize counts = count() by policyAssignmentId, policyAssignmentScope, max_stateWeight, policyAssignmentName
| summarize overallStateWeight = max(max_stateWeight),
nonCompliantCount = sumif(counts, max_stateWeight == 300),
compliantCount = sumif(counts, max_stateWeight == 200),
conflictCount = sumif(counts, max_stateWeight == 100),
exemptCount = sumif(counts, max_stateWeight == 50) by policyAssignmentId, policyAssignmentScope, policyAssignmentName
| extend totalResources = todouble(nonCompliantCount + compliantCount + conflictCount + exemptCount)
| extend 
compliancePercentage = iff(totalResources == 0, todouble(100), 100 * todouble(compliantCount + exemptCount) / totalResources),
complianceState = iff(overallStateWeight == 300, 'noncompliant', iff(overallStateWeight == 200, 'compliant', iff(overallStateWeight == 100, 'conflict', iff(overallStateWeight == 50, 'exempt', 'notstarted'))))
// 关联排除范围计数的结果
| join kind=inner (excludedScopesQuery) on policyAssignmentName
// 保留需要的字段
| project policyAssignmentName, policyAssignmentScope, complianceState, compliancePercentage, compliantCount, nonCompliantCount, exemptCount, conflictCount, excludedScopesCount

关键说明： #

• 子查询定义：用let语句将排除范围计数的查询定义为临时表excludedScopesQuery，并保留policyAssignmentName作为关联键。
• 语法修正：修复了原查询1中extend语句的语法错误，确保字段分隔和逻辑正确。
• 关联操作：使用join kind=inner将合规状态查询的结果与子查询关联，确保只保留匹配的策略分配数据。
• 字段投影：最后用project筛选并整理需要展示的字段，方便在Azure仪表板小部件中使用。

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