Various Azure Resource Graph queries

asg-in-nsg-rules.kql

resources
| where type == "microsoft.network/networksecuritygroups"
| extend securityRules = properties.securityRules
| mv-expand securityRules
| where securityRules.properties.destinationApplicationSecurityGroups != '' or securityRules.properties.sourceApplicationSecurityGroups != ''
| mv-expand srcAsgs = securityRules.properties.sourceApplicationSecurityGroups
| mv-expand dstAsgs = securityRules.properties.destinationApplicationSecurityGroups
| extend srcAsgNames = split(srcAsgs.id, "/")[-1]
| extend dstAsgNames = split(dstAsgs.id, "/")[-1]
| mv-expand subnet = properties.subnets
| extend vnetName = split(subnet.id, "/")[-3]
| extend subnetName = split(subnet.id, "/")[-1]
| project id, nsg_name=name, vnetName, subnetName, subscriptionId, resourceGroup, location, ruleName=securityRules.name, srcAsgNames, dstAsgNames, ruleProperties=securityRules.properties

pe-routes.kql

// private endpoint routes
resources
| where type == "microsoft.network/privateendpoints"
| extend nics = properties.networkInterfaces
| mv-expand nics
| extend nic = tostring(nics.id)
| project nic
| join kind=leftouter ( 
  resources
  | where type == 'microsoft.network/networkinterfaces'
  ) on $left.nic == $right.id
| extend ipConfigs = properties.ipConfigurations
| mv-expand ipConfigs
| extend ip = ipConfigs.properties.privateIPAddress
| extend cidr = strcat(tostring(ip), '/32')
| project cidr
| sort by tostring(cidr)

pe-without-nsg.kql

// Private endpoints without NSG rules enforced
resources
| where type == "microsoft.network/privateendpoints"
| extend props = parse_json(properties)
| extend provisioningState = tostring(props.provisioningState)
| extend subnetId = tostring(props.subnet.id)
| extend owner = tags['Owner']
| extend environment = tags['Environment']
| extend technicalOwner = tags['Technical Owner']
| join kind = leftouter  (
resources 
| where type == 'microsoft.network/virtualnetworks'
| extend subnets = parse_json(properties).subnets
| mv-expand subnets
| extend subnetId = tostring(subnets.id)
| extend subnetName = subnets.name
| extend subnetPrivateEndpointNetworkPolicies = subnets.properties.privateEndpointNetworkPolicies
| extend subnetNsgId = subnets.properties.networkSecurityGroup.id
| project subnetId, subnetName, subnetNsgId, subnetPrivateEndpointNetworkPolicies
) on subnetId
| project owner, technicalOwner, environment, id, name, location, resourceGroup, subscriptionId, provisioningState, subnetId, subnetName, subnetNsgId, subnetPrivateEndpointNetworkPolicies
| order by tostring(environment)