合并KQL查询创建privateEndpointNetworkPolicies禁用子网的PEP告警遇错误

需要创建告警监控附加到privateEndpointNetworkPolicies已禁用子网的私有端点（PEP）的创建/修改操作，编写的KQL查询执行时出现错误：

Some aspects of the query had errors so the results are not complete If the issue persists, please open a support ticket.

原查询代码 #

arg("").Resources    
| where type =~ "microsoft.network/privateEndpoints" and isnotnull(properties)      
| extend subnetIdall = properties.subnet.id    
| extend subnetIdSplit = split(subnetIdall, "/")    
| extend vnetId = strcat_array(array_slice(subnetIdSplit,0,8), "/")    
| extend vnetName = strcat(subnetIdSplit[8])    
| extend subnetName = strcat(subnetIdSplit[8], "/", subnetIdSplit[10])     
| extend id = tolower(tostring(id))    
| extend ssubnetIdall = tolower(tostring(subnetIdall))     
| project ssubnetIdall, name, subnetName, vnetName, vnetId     
| join kind=inner(
arg("").Resources 
    | where type == 'microsoft.network/virtualnetworks' 
    | mv-expand properties.subnets limit 500
    | where properties_subnets.properties.privateEndpointNetworkPolicies == "Disabled"
    | extend SubnetNameALL = tostring(properties_subnets.name)
    | extend SubnetID = properties_subnets.id
    | extend SSubnetID = tolower(tostring(SubnetID))
    | project VirtualNetworkName=name, VirtualNetworkCIDR=properties.addressSpace.addressPrefixes, SubnetNameALL, SubNetCIDR=properties_subnets.properties.addressPrefix, SubNetPEPpolicy=properties_subnets.properties.privateEndpointNetworkPolicies, SSubnetID
)on $left.ssubnetIdall == $right.SSubnetID

错误原因及修正点 #

1. 无效的arg("")前缀：KQL中直接引用Resources表即可，arg("").Resources属于语法错误，是触发报错的主要原因。
2. array_slice索引错误：子网ID格式为/subscriptions/{subId}/resourceGroups/{rg}/providers/Microsoft.Network/virtualNetworks/{vnetName}/subnets/{subnetName}，KQL的array_slice是左闭右开规则，原查询用0,8会导致虚拟网络ID缺失vnet名称部分，需改为0,9。
3. 冗余的strcat调用：strcat(subnetIdSplit[8])可直接简化为subnetIdSplit[8]，数组元素本身就是字符串类型。
4. 大小写敏感的比较：用=~替代==进行字符串比较，避免因属性值大小写不一致导致数据遗漏。
5. 不必要的子网数量限制：mv-expand properties.subnets limit 500会过滤超过500的子网，导致关联数据丢失，建议移除该限制。

修正后的KQL查询 #

// 获取所有私有端点及其关联子网信息
Resources    
| where type =~ "microsoft.network/privateEndpoints" and isnotnull(properties)      
| extend subnetId = properties.subnet.id    
| extend subnetIdParts = split(subnetId, "/")    
// 正确提取虚拟网络ID：覆盖到virtualNetworks后的名称部分
| extend vnetId = strcat_array(array_slice(subnetIdParts, 0, 9), "/")    
| extend vnetName = subnetIdParts[8]    
| extend subnetName = strcat(subnetIdParts[8], "/", subnetIdParts[10])     
| extend normalizedSubnetId = tolower(subnetId)     
| project normalizedSubnetId, pepName = name, subnetName, vnetName, vnetId     

// 关联到privateEndpointNetworkPolicies已禁用的子网
| join kind=inner (
    Resources 
    | where type =~ 'microsoft.network/virtualnetworks' 
    | mv-expand properties.subnets
    | where properties_subnets.properties.privateEndpointNetworkPolicies =~ "Disabled"
    | extend subnetName = tostring(properties_subnets.name)
    | extend subnetId = properties_subnets.id
    | extend normalizedSubnetId = tolower(subnetId)
    | project 
        vnetName = name, 
        vnetCidr = properties.addressSpace.addressPrefixes, 
        subnetName, 
        subnetCidr = properties_subnets.properties.addressPrefix, 
        pepPolicy = properties_subnets.properties.privateEndpointNetworkPolicies, 
        normalizedSubnetId
) on normalizedSubnetId

针对创建/修改操作的告警查询补充 #

如果需要监控私有端点的创建/修改操作（而非当前存在的私有端点），需改用AzureActivity表（需确保活动日志采集正常），示例如下：

// 监控私有端点的创建或修改操作，关联到禁用PEP策略的子网
AzureActivity
| where OperationNameValue =~ "Microsoft.Network/privateEndpoints/write"
| where StatusValue =~ "Success"
| extend properties = parse_json(Properties)
| extend subnetId = properties.targetResource.properties.subnet.id
| extend subnetIdParts = split(subnetId, "/")    
| extend vnetId = strcat_array(array_slice(subnetIdParts, 0, 9), "/")    
| extend vnetName = subnetIdParts[8]    
| extend subnetName = strcat(subnetIdParts[8], "/", subnetIdParts[10])     
| extend normalizedSubnetId = tolower(subnetId)     
| project 
    EventTimestamp,
    OperationName,
    ResourceGroup,
    pepName = Resource,
    subnetName, 
    vnetName, 
    normalizedSubnetId,
    Caller

| join kind=inner (
    Resources 
    | where type =~ 'microsoft.network/virtualnetworks' 
    | mv-expand properties.subnets
    | where properties_subnets.properties.privateEndpointNetworkPolicies =~ "Disabled"
    | extend subnetId = properties_subnets.id
    | extend normalizedSubnetId = tolower(subnetId)
    | project normalizedSubnetId
) on normalizedSubnetId

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