I am trying to query the number of connected devices under each virtual network using Azure Resource Graph Explorer. I wish to see how efficiently the allocated CIDR IPs are used.

This is as far as I got:

resources
| where type =~ 'microsoftwork/virtualnetworks'
| extend cidr = properties.addressSpace.addressPrefixes
| extend no_cidr = array_length(cidr)
| extend subnets = properties.subnets
| extend no_subnets = array_length(subnets)
| extend devices =  pack_array(subnets[0].properties.ipConfigurations, subnets[1].properties.ipConfigurations)
| extend no_devices = array_length(devices[0]) + array_length(devices[1])

The last two lines is not correct.

It only takes the device arrays from the first two subnets. I wish to get all the devices from all the subnets. Also if a subnet has no connected device the last line ruins the calculation.

How can I grab all the connected devices?

The data format for the subnets is something like this:

[
    {
        "name": "subnet_name_1",
        "properties": {
            "addressPrefix": "aaa.bbbc.ddd/ee",
            "ipConfigurations": [
                {
                    "id": "/subscriptions/...device1"
                },
                {
                    "id": "/subscriptions/...device2"
                }
            ],....
        ...
        }
        ...
    },    
    {
        "name": "subnet_name_2",
        "properties": {
            "addressPrefix": "aaa.bbbc.ddd/ee",
            "ipConfigurations": [
                {
                    "id": "/subscriptions/...device3"
                },
                {
                    "id": "/subscriptions/...device4"
                }
            ],....
        ...
        }
        ...
    },
    ...
]

I am trying to query the number of connected devices under each virtual network using Azure Resource Graph Explorer. I wish to see how efficiently the allocated CIDR IPs are used.

This is as far as I got:

resources
| where type =~ 'microsoftwork/virtualnetworks'
| extend cidr = properties.addressSpace.addressPrefixes
| extend no_cidr = array_length(cidr)
| extend subnets = properties.subnets
| extend no_subnets = array_length(subnets)
| extend devices =  pack_array(subnets[0].properties.ipConfigurations, subnets[1].properties.ipConfigurations)
| extend no_devices = array_length(devices[0]) + array_length(devices[1])

The last two lines is not correct.

It only takes the device arrays from the first two subnets. I wish to get all the devices from all the subnets. Also if a subnet has no connected device the last line ruins the calculation.

How can I grab all the connected devices?

The data format for the subnets is something like this:

[
    {
        "name": "subnet_name_1",
        "properties": {
            "addressPrefix": "aaa.bbbc.ddd/ee",
            "ipConfigurations": [
                {
                    "id": "/subscriptions/...device1"
                },
                {
                    "id": "/subscriptions/...device2"
                }
            ],....
        ...
        }
        ...
    },    
    {
        "name": "subnet_name_2",
        "properties": {
            "addressPrefix": "aaa.bbbc.ddd/ee",
            "ipConfigurations": [
                {
                    "id": "/subscriptions/...device3"
                },
                {
                    "id": "/subscriptions/...device4"
                }
            ],....
        ...
        }
        ...
    },
    ...
]

• azure-data-explorer
• azure-resource-graph

Share Improve this question Follow edited Feb 21 at 3:28 H_W asked Feb 21 at 3:19 H_WH_W 1344 bronze badges 1

• Without using mv-expand, you're essentially trying to count elements within an array without first breaking that array into individual rows. The count() function, in the context of summarize by, counts the number of rows, not the number of elements within an array in a column. – Balaji Commented Mar 7 at 3:31

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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 =~ 'microsoftwork/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: