使用KQL查询Azure Policy（MCAS）中设为Deny效果的策略

你的KQL查询存在两处语法错误，同时可以优化逻辑覆盖更多场景，以下是具体修正方案：

核心错误修正 #

1. 拼写失误：defaulValue 漏写了字母t，正确应为 defaultValue
2. 语法遗漏：extend 语句中两个字段定义之间必须用逗号分隔，否则会触发语法校验失败

修正后的基础查询（针对参数默认值为Deny的自定义策略） #

policyresources
| where type == "microsoft.authorization/policydefinitions"
| where properties.displayName startswith "[Custom]"
// 用tostring转换避免潜在的类型不匹配问题，同时修正拼写
| where tostring(properties.parameters.effect.defaultValue) == "Deny"
| extend
    displayName = tostring(properties.displayName),
    effect = tostring(properties.parameters.effect.defaultValue)
| project displayName, effect

扩展查询：覆盖直接定义Deny的自定义策略 #

部分自定义策略不会将effect设为可配置参数，而是直接在规则中写死Deny。如果需要覆盖这类场景，可使用以下查询同时匹配两种情况：

policyresources
| where type == "microsoft.authorization/policydefinitions"
| where properties.displayName startswith "[Custom]"
// 同时匹配参数默认Deny，或策略规则直接设置Deny的场景
| where (tostring(properties.parameters.effect.defaultValue) == "Deny")
    or (tostring(properties.policyRule.then.effect) == "Deny")
| extend
    displayName = tostring(properties.displayName),
    // 优先取参数默认值，无参数则取规则中的effect
    effect = coalesce(tostring(properties.parameters.effect.defaultValue), tostring(properties.policyRule.then.effect))
| project displayName, effect

补充：筛选已纳入计划的自定义Deny策略 #

如果需要从已加入策略计划（Initiative）的策略中筛选，可以先提取计划引用的策略ID，再进行匹配：

// 提取所有策略计划中引用的策略定义ID
let initiativePolicyIds = policyresources
| where type == "microsoft.authorization/policyassignments"
| where properties.metadata.category == "Initiative"
| mv-expand properties.policyDefinitions
| project policyDefId = tostring(properties.policyDefinitions.policyDefinitionId);

// 匹配计划中包含的自定义Deny策略
policyresources
| where type == "microsoft.authorization/policydefinitions"
| where id in (initiativePolicyIds)
| where properties.displayName startswith "[Custom]"
| where (tostring(properties.parameters.effect.defaultValue) == "Deny")
    or (tostring(properties.policyRule.then.effect) == "Deny")
| extend
    displayName = tostring(properties.displayName),
    effect = coalesce(tostring(properties.parameters.effect.defaultValue), tostring(properties.policyRule.then.effect))
| project displayName, effect

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