Azure: Kusto (KQL): Network Related Queries

ListIPGroupsContainingCIDR.kql

// Specify a value for `testCidr` (must be a valid CIDR; so if just looking for a specific IPv4 IP, append /32 on the end).  
// Run this and you'll see all IP Groups which contain CIDRs or IPs which overlap in any way with the given value.

resourcecontainers | where type == "microsoft.resources/subscriptions" | limit 1 // this is a hack to give us a single row
| project testCidr = "123.123.123.123/32" // update this value to the CIDR you're interested in
| extend testCidrSplit = array_concat(split(split(testCidr, '/')[0],'.'), split(split(testCidr, '/')[1],'x'))
| extend testCidrFirstIp = toint(testCidrSplit[0]) * 16777216 + toint(testCidrSplit[1]) * 65536 + toint(testCidrSplit[2]) * 256 + toint(testCidrSplit[3])
| extend testCidrLastIp = testCidrFirstIp + pow(2,32-testCidrSplit[4])-1
| extend joinhack = 1
| join kind = inner
(
    resources
    | where type =~ 'microsoft.network/ipgroups'
    | project subscriptionId, resourceGroup, name, ipAddresses = properties.ipAddresses
    | mv-expand cidr = ipAddresses
    | extend cidrSplit = array_concat(split(split(cidr, '/')[0],'.'), split(split(strcat(tostring(cidr), "/32"), '/')[1],'x'))
    | extend cidrFirstIp = toint(cidrSplit[0]) * 16777216 + toint(cidrSplit[1]) * 65536 + toint(cidrSplit[2]) * 256 + toint(cidrSplit[3])
    | extend cidrLastIp = cidrFirstIp + pow(2,32-cidrSplit[4])-1
    | extend joinhack = 1
) on joinhack
| where cidrFirstIp <= testCidrLastIp and cidrLastIp >= testCidrFirstIp
| order by cidrFirstIp, cidrLastIp
| project subscriptionId, resourceGroup, name, cidr

ListVNetsContainingCIDR.kql

// Specify a value for `testCidr` (must be a valid CIDR; so if just looking for a specific IPv4 IP, append /32 on the end).  
// Run this and you'll see all VNets with address ranges which overlap in any way with the given value.

resourcecontainers | where type == "microsoft.resources/subscriptions" | limit 1 // this is a hack to give us a single row
| project testCidr = "172.27.31.96/27" // update this value to the CIDR you're interested in
| extend testCidrSplit = array_concat(split(split(testCidr, '/')[0],'.'), split(split(testCidr, '/')[1],'x'))
| extend testCidrFirstIp = toint(testCidrSplit[0]) * 16777216 + toint(testCidrSplit[1]) * 65536 + toint(testCidrSplit[2]) * 256 + toint(testCidrSplit[3])
| extend testCidrLastIp = testCidrFirstIp + pow(2,32-testCidrSplit[4])-1
| extend joinhack = 1
| join kind = inner
(
    resources 
    | where type =~ 'Microsoft.Network/virtualNetworks'
    | project id, subscriptionId, resourceGroup, name, addressPrefixes = properties['addressSpace'].['addressPrefixes'], joinhack = 1
    | mv-expand addressPrefixes
    | extend cidrSplit = array_concat(split(split(addressPrefixes, '/')[0],'.'), split(split(addressPrefixes, '/')[1],'x'))
    | extend firstIpVal = toint(cidrSplit[0]) * 16777216 + toint(cidrSplit[1]) * 65536 + toint(cidrSplit[2]) * 256 + toint(cidrSplit[3])
    | extend lastIpVal = firstIpVal + pow(2,32-cidrSplit[4])-1
    | project-away cidrSplit
)
on joinhack
| where firstIpVal <= testCidrLastIp and lastIpVal >= testCidrFirstIp
| project subscriptionId, resourceGroup, name, addressPrefixes, firstIpVal, lastIpVal
| order by firstIpVal, lastIpVal

ListVNetsWithClashingAddressRanges.kql

resources 
| where type =~ 'Microsoft.Network/virtualNetworks'
| project id, subscriptionId, resourceGroup, name, addressPrefixes = properties['addressSpace'].['addressPrefixes']
| mv-expand addressPrefixes
| extend cidrSplit = array_concat(split(split(addressPrefixes, '/')[0],'.'), split(split(addressPrefixes, '/')[1],'x'))
| extend firstIpVal = toint(cidrSplit[0]) * 16777216 + toint(cidrSplit[1]) * 65536 + toint(cidrSplit[2]) * 256 + toint(cidrSplit[3])
| extend lastIpVal = firstIpVal + pow(2,32-cidrSplit[4])-1
| project-away cidrSplit
| extend nastyhack = 1
| join kind = inner
(
    resources 
    | where type =~ 'Microsoft.Network/virtualNetworks'
    | project id, subscriptionId, resourceGroup, name, addressPrefixes = properties['addressSpace'].['addressPrefixes']
    | mv-expand addressPrefixes
    | extend cidrSplit = array_concat(split(split(addressPrefixes, '/')[0],'.'), split(split(addressPrefixes, '/')[1],'x'))
    | extend firstIpVal = toint(cidrSplit[0]) * 16777216 + toint(cidrSplit[1]) * 65536 + toint(cidrSplit[2]) * 256 + toint(cidrSplit[3])
    | extend lastIpVal = firstIpVal + pow(2,32-cidrSplit[4])-1
    | project-away cidrSplit
    | extend nastyhack = 1
) on nastyhack //only equality allowed so we can't do $left.id != $right.id and $left.firstIpVal <= $right.lastIpVal and $left.lastIpVal >= $right.firstIpVal
| where id != id1 and firstIpVal <= lastIpVal1 and lastIpVal >= firstIpVal1
| project subscriptionId, resourceGroup, name, addressPrefixes, addressPrefixes1, name1, resourceGroup1, subscriptionId1, firstIpVal, firstIpVal1, lastIpVal, lastIpVal1
| order by firstIpVal, firstIpVal1, lastIpVal, lastIpVal1