need to combine two queries to set up an alert for private endpoint creation or modification in subnets with disabled privateEndpointNetworkPolicies

Question

I want to join two queries so that I can create an alert for the creation or modification of private endpoints attached to a subnet where privateEndpointNetworkPolicies is disabled.

`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`

These query should give me newly created or modified PEP details where associated subnet is having privateEndpointNetworkPolicies disabled . but getting this error 'Some aspects of the query had errors so the results are not complete If the issue persists, please open a support ticket.'

Answer 1

Need to combine two queries to set up an alert for private endpoint creation or modification in subnets with disabled privateEndpointNetworkPolicies: -

Before a workaround on your error, I tried executing your query in my environment and was able to retrieve the results as shown below.

Coming to the issue, there might be a chance of getting conflict errors in Azure resource graph explorer with few functions such as join etc.

In order to avoid the conflicts, I modified your query as below and was able to obtain the results as expected.

Note: As I don't have the resources that matches your specific requirement, I received output as no results.

Resources
| where type == "microsoft.network/privateEndpoints"  and isnotnull(properties)
| extend subnetIdall = properties.subnet.id
| extend ssubnetIdall = tolower(tostring(subnetIdall))
| extend PEP = name
| project PEP, ssubnetIdall
| join kind=inner(
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
| extend JoinID = strcat(PEP, SSubnetID)