Created
April 3, 2024 13:09
-
-
Save JohnLBevan/76ee346c34e8dede126da0699327a890 to your computer and use it in GitHub Desktop.
Azure: Kusto (KQL): Network Related Queries
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment