How do I query the number of connected devices under each virtual network in Azure Graph Explorer?
Query you tried extracts data from the first two subnets subnets[0]
and subnets[1]
. If a VNet has more subnets, they are ignored. If ipConfigurations
is empty for a subnet, subnets[n].properties.ipConfigurations
may be null
, and summing up array_length(null)
can cause errors.
Try with the below query it counts all devices, and we need to flatten the subnets
array and count all ipConfigurations
dynamically. Below query uses mv-expand
to break subnets
into separate rows, so we can count all devices from all subnets. Also iif(isnull(devices), 0, array_length(devices))
to avoid breaking when there are no connected devices. Now it will counts the total devices and total subnets per VNet as shown in the below output.
resources
| where type =~ 'microsoft.network/virtualnetworks'
| extend cidr = properties.addressSpace.addressPrefixes
| extend no_cidr = array_length(cidr)
| mv-expand subnets = properties.subnets
| extend subnetName = subnets.name
| extend devices = subnets.properties.ipConfigurations
| extend no_devices = iif(isnull(devices), 0, array_length(devices))
| summarize TotalDevices = sum(no_devices), TotalSubnets = count() by name
| project name, TotalSubnets, TotalDevices
| order by TotalDevices desc
Output: