KQL assistance - how do I apply a filter to my query?

Question

I'm trying to weed out any routing tables that have a default route to the internet. I've come up with a query to get all of the routing tables. How do I filter to generate a list of routes that default out to the public internet?

This is what I've come up with so far, but it's returning everything. I just want a list of routing tables that are set default to the public internet. Apparently I'm not applying the filters correctly. Any help would be greatly appreciated!

resources
| where type =~ "Microsoft.Network/routeTables"
| mv-expand rules = properties.routes
| join kind=leftouter (resourcecontainers 
| where type=='microsoft.resources/subscriptions' 
| project SubcriptionName=name, subscriptionId) on subscriptionId
| extend subnet_name = split((split(tostring(properties.subnets), '/'))[10], '"')[0]
| extend addressPrefix = "0.0.0.0/0"
| extend nextHopType = "Internet"
| extend nextHopIpAddress = tostring(rules.properties.nextHopIpAddress)
| extend hasBgpOverride = tostring(rules.properties.hasBgpOverride)
| extend provisioningState = tostring(rules.properties.provisioningState)
| extend udrname = rules.name
| extend rtname = name
| project SubcriptionName, resourceGroup, subnet_name, rtname, udrname, addressPrefix, nextHopType,        nextHopIpAddress, provisioningState, hasBgpOverride
| sort by SubcriptionName, resourceGroup asc, rtname asc, addressPrefix asc

Answer 1

To list out the routing tables that are set default to the public internet using kql query, I modified your query with the below set of possibilities and it worked for me as follows.

Approach-1:

Filter the routes which have the nexthoptype property as "Internet" type and the usual "0.0.0.0/0" IP prefix which is a default route for any network table.

resources
| where type =~ "Microsoft.Network/routeTables"
| mv-expand routerules = properties.routes
| where routerules != "null"  and routerules.properties.addressPrefix == "0.0.0.0/0"  and routerules.properties.nextHopType == "Internet"
| join kind=leftouter (resourcecontainers
| where type=='microsoft.resources/subscriptions'
| project SubcriptionName=name, subscriptionId) on subscriptionId
| extend subnet_name = split((split(tostring(properties.subnets), '/'))[10], '"')[0]
| extend addressPrefix = "0.0.0.0/0"
| extend nextHopType = "Internet"
| extend nextHopIpAddress = tostring(rules.properties.nextHopIpAddress)
| extend hasBgpOverride = tostring(rules.properties.hasBgpOverride)
| extend provisioningState = tostring(rules.properties.provisioningState)
| extend udrname = rules.name
| extend rtname = name
| project SubcriptionName, resourceGroup, subnet_name, rtname, udrname, addressPrefix, nextHopType, nextHopIpAddress, provisioningState, hasBgpOverride
| sort  by SubcriptionName, resourceGroup asc, rtname asc, addressPrefix asc

Approach-2:

Apply a query filter to the subnet array index's[0] which contains Ip address prefix 0.0.0.0which indicates, it is set to the default public internet.

resources
| where type =~ "Microsoft.Network/routeTables"
| mv-expand routerules = properties.routes
| where properties.subnets[0].properties.addressPrefix == "0.0.0.0/0"
| join kind=leftouter (resourcecontainers
| where type=='microsoft.resources/subscriptions'
| project SubcriptionName=name, subscriptionId) on subscriptionId
| extend subnet_name = split((split(tostring(properties.subnets), '/'))[10], '"')[0]
| extend addressPrefix = "0.0.0.0/0"
| extend nextHopType = "Internet"
| extend nextHopIpAddress = tostring(rules.properties.nextHopIpAddress)
| extend hasBgpOverride = tostring(rules.properties.hasBgpOverride)
| extend provisioningState = tostring(rules.properties.provisioningState)
| extend udrname = rules.name
| extend rtname = name
| project SubcriptionName, resourceGroup, subnet_name, rtname, udrname, addressPrefix, nextHopType, nextHopIpAddress, provisioningState, hasBgpOverride
| sort  by SubcriptionName, resourceGroup asc, rtname asc, addressPrefix asc