Azure资源图查询报错：获取已分配与已弃用策略失败

原查询错误原因 #

你的查询触发UnexpectedQueryExecutionError，大概率是直接使用join处理大量策略状态数据时，超出了Azure Resource Graph查询引擎的执行限制；另外原查询中HTML转义的引号"，实际执行时需替换为普通单/双引号。

修正后的查询方案 #

方案1：用lookup替代join（适配资源关联场景） #

lookup在Resource Graph中处理跨资源类型关联时更高效，能降低执行压力：

policyresources 
| where type =~ 'microsoft.policyinsights/policystates'
| extend
    policyDefinitionId = tostring(properties.policyDefinitionId),
    policyAssignmentScope = tostring(properties.policyAssignmentScope),
    policyAssignmentName = tostring(properties.policyAssignmentName)
| lookup kind=leftouter (
    policyresources
    | where type =~ 'microsoft.authorization/policydefinitions'
    | extend 
        description = tostring(properties.description),
        displayName = tostring(properties.displayName)
    | where displayName contains 'deprecated'
    | project id, description, displayName
) on $left.policyDefinitionId == $right.id
| project policyDefinitionId, policyAssignmentScope, policyAssignmentName, displayName, description

方案2：用let拆分查询逻辑（提升可读性与效率） #

先预筛选已弃用的策略集合，再关联策略状态数据：

// 预定义所有已弃用的策略定义
let deprecatedPolicies = policyresources
| where type =~ 'microsoft.authorization/policydefinitions'
| extend 
    policyDisplayName = tostring(properties.displayName),
    policyDescription = tostring(properties.description)
| where policyDisplayName contains 'deprecated'
| project policyDefinitionId = id, policyDisplayName, policyDescription;

// 关联策略分配状态数据
policyresources
| where type =~ 'microsoft.policyinsights/policystates'
| extend
    policyDefinitionId = tostring(properties.policyDefinitionId),
    policyAssignmentScope = tostring(properties.policyAssignmentScope),
    policyAssignmentName = tostring(properties.policyAssignmentName)
| join kind=inner deprecatedPolicies on policyDefinitionId
| project policyDefinitionId, policyAssignmentScope, policyAssignmentName, policyDisplayName, policyDescription

补充说明 #

• 若只需获取已被分配的弃用策略，使用join kind=inner比leftouter更高效，可自动过滤无匹配的记录。
• 执行前确保查询中的引号为普通单/双引号，避免HTML转义字符导致的语法问题。

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