Azure资源图查询缺失规则的NSG时KQL查询报错求助

原查询错误原因 #

你的KQL查询出现ParserFailure错误，核心问题有两个：

1. 在not()函数内直接对数组properties.securityRules使用| where是无效语法，KQL不支持这种嵌套方式处理数组结构。
2. 嵌套查询中错误引用properties.securityRules.ruleName，遍历数组元素时，需先通过展开或数组函数处理，且元素上下文内应直接访问ruleName字段。

可用查询版本 #

以下两种查询均可实现需求，且能明确显示NSG缺失的具体规则：

方法1：使用array_contains（性能更优） #

无需展开数组，直接通过lambda表达式检查数组内是否存在目标规则：

Resources
| where type == "microsoft.network/networksecuritygroups"
| extend hasAllowThis = array_contains(properties.securityRules, r => tolower(r.ruleName) =~ "allowthis"),
          hasAllowThat = array_contains(properties.securityRules, r => tolower(r.ruleName) =~ "allowthat")
| where hasAllowThis == false or hasAllowThat == false
| extend MissingRules = dynamic([])
| extend MissingRules = iif(hasAllowThis == false, array_union(MissingRules, dynamic(["AllowThis"])), MissingRules)
| extend MissingRules = iif(hasAllowThat == false, array_union(MissingRules, dynamic(["AllowThat"])), MissingRules)
| project NSGName = name, ResourceGroup = resourceGroup, MissingRules
| order by NSGName asc

方法2：使用mv-expand展开数组 #

先展开安全规则数组，聚合每个NSG的已存在规则后，判断缺失项：

Resources
| where type == "microsoft.network/networksecuritygroups"
| mv-expand securityRule = properties.securityRules
| extend ruleName = tolower(tostring(securityRule.ruleName))
| summarize existingRules = make_set(ruleName) by NSGName = name, ResourceGroup = resourceGroup
| extend hasAllowThis = "allowthis" in (existingRules),
          hasAllowThat = "allowthat" in (existingRules)
| where hasAllowThis == false or hasAllowThat == false
| extend MissingRules = dynamic([])
| extend MissingRules = iif(hasAllowThis == false, array_union(MissingRules, dynamic(["AllowThis"])), MissingRules)
| extend MissingRules = iif(hasAllowThat == false, array_union(MissingRules, dynamic(["AllowThat"])), MissingRules)
| project NSGName, ResourceGroup, MissingRules
| order by NSGName asc

查询结果说明 #

• NSGName：缺少规则的网络安全组名称
• ResourceGroup：NSG所属的资源组
• MissingRules：该NSG缺失的规则列表（可能包含AllowThis、AllowThat或两者）

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