KQL queries to get the details

Monalisa 20

We are using Azure Resource Graph Explorer. We have two queries we want to merge it .

First

###############

// Get all virtual networks and their subnets
resources
| where type =~ 'microsoft.network/virtualnetworks'
| project vnetId = id, vnetName = name, subnets = properties.subnets
| mv-expand subnets
| project vnetId, vnetName, subnetId = tostring(subnets.id), subnetName = subnets.name
| join kind=inner (
    resources
    | where type has 'networkinterfaces'
    | project nicId = id, nicName = name, ipConfigurations = properties.ipConfigurations, 
              resourceId = properties.virtualMachine.id, 
              resourceType = 'Microsoft.Compute/virtualMachines'
    | mv-expand ipConfigurations
    | extend subnetId = tostring(ipConfigurations.properties.subnet.id)
    | project nicId, nicName, resourceId, resourceType, privateIpAddress = ipConfigurations.properties.privateIPAddress, subnetId
) on $left.subnetId == $right.subnetId
| union (
    resources
    | where type has 'microsoft.network/loadBalancers'
    | project lbId = id, lbName = name, ipConfigurations = properties.frontendIPConfigurations
    | mv-expand ipConfigurations
    | extend subnetId = tostring(ipConfigurations.properties.subnet.id)
    | project resourceId = lbId, resourceName = lbName, resourceType = 'Microsoft.Network/loadBalancers', privateIpAddress = ipConfigurations.properties.privateIPAddress, subnetId
),
(
    resources
    | where type has 'microsoft.network/applicationGateways'
    | project agId = id, agName = name, ipConfigurations = properties.gatewayIPConfigurations
    | mv-expand ipConfigurations
    | extend subnetId = tostring(ipConfigurations.properties.subnet.id)
    | project resourceId = agId, resourceName = agName, resourceType = 'Microsoft.Network/applicationGateways', privateIpAddress = ipConfigurations.properties.privateIPAddress, subnetId
)

Second

##################

resources
| join kind=leftouter(
    ResourceContainers 
    | where type=='microsoft.resources/subscriptions' 
    | project subscriptionName=name, subscriptionId
) on subscriptionId
| where type =~ 'Microsoft.Network/virtualNetworks'
| extend addressPrefixes=array_length(properties.addressSpace.addressPrefixes)
| extend vNetAddressSpace=properties.addressSpace.addressPrefixes
| mv-expand subnet=properties.subnets
| extend virtualNetwork = name
| extend subnetPrefix = subnet.properties.addressPrefix
| extend SubnetCIDR=subnet.properties.addressPrefix
| extend subnets = properties.subnets
| extend subnetName = tostring(subnet.name)
| extend prefixLength = toint(split(subnetPrefix, "/")[1])
| extend addressPrefix = split(subnetPrefix, "/")[0]
| extend numberOfIpAddresses = trim_end(".0",tostring(pow(2, 32 - prefixLength) - 5))
| extend startIp = addressPrefix
| extend endIp = strcat(strcat_array((array_slice(split(addressPrefix, '.'), 0, 2)),"."),".",trim_end(".0",tostring(split(addressPrefix, '.')[3] + (pow(2, 32 - prefixLength) - 5))))
| extend endIPNew = case(prefixLength == 23, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'1.255'),
    prefixLength == 22, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'3.255'),
    prefixLength == 21, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'7.255'),
    prefixLength == 20, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'15.255'),
    prefixLength == 19, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'31.255'),
    prefixLength == 18, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'63.255'),
    prefixLength == 17, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'127.255'),
    prefixLength == 16, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'255.255'),
    'unknown'
)
| extend finalendIPaddress = iff(endIPNew == "unknown", endIp, endIPNew) 
| join kind=leftouter (
    // Number of connected devices per VNet and Subnet
    resources
    | join kind=leftouter(
        resourcecontainers 
        | where type=='microsoft.resources/subscriptions' 
        | project subscriptionName=name, subscriptionId
    ) on subscriptionId
    | where type =~ 'microsoft.network/networkinterfaces'
    | project id, ipConfigurations = properties.ipConfigurations, virtualMachine = tostring(split(properties.virtualMachine.id,"/",8)[0]), subscriptionName
    | mvexpand ipConfigurations
    | project id, subnetId = tostring(ipConfigurations.properties.subnet.id), subscriptionName, virtualMachine
    | parse kind=regex subnetId with '/virtualNetworks/' virtualNetwork '/subnets/' subnet
    | extend resourceGroup = tostring(split(subnetId,"/",4)[0])
    | extend subnetName = subnet
    | summarize usedIPAddresses = count() by subnetName, virtualNetwork, subscriptionName
)
on subnetName, virtualNetwork, subscriptionName
| extend usedIPAddresses_new = iff(isnull(usedIPAddresses),0,usedIPAddresses)
| extend privateIP = properties.privateIpAddress
| project subscriptionName, resourceGroup, virtualNetwork, SubnetName = subnet.name, IPRange = strcat(startIp, " - ", finalendIPaddress), numberOfIpAddresses, SubnetCIDR, usedIPAddresses, AvailableIPAddresses = (toint(numberOfIpAddresses) - usedIPAddresses_new)

Monalisa 20 Reputation points

2024-06-20T07:16:56.9866667+00:00

Hi @KapilAnanth-MSFT

Thanks for helping on above query. Our requirement is to have automation report where we are using kusto query . The query should return csv with below fields. The used IP address isolated or connected to which connected device and privateIPv4 attached to which backend type.

Kindly help on the same.
KapilAnanth-MSFT 39,051 Reputation points Microsoft Employee

2024-06-20T08:29:10.89+00:00
@Monalisa ,

Thanks for sharing the column headers.

But what should be the rows?

NICS?

App Gateways?

or Load Balancers.

or

Subnets?

Also, what are the column headers in K to O? Can you explain the headers?
KapilAnanth-MSFT 39,051 Reputation points Microsoft Employee

2024-06-20T12:32:56.4366667+00:00
@Monalisa ,

My question still remains unanswered.

#1

What should be the rows?

NICS?

App Gateways?

or Load Balancers.

or

Subnets?

#2

Also, isn't column L and N same?
Monalisa 20 Reputation points

2024-06-27T14:52:36.88+00:00

Hi @KapilAnanth-MSFT

Thanks a lot for your support. We got the output as per our requirement.

We also want to add another column DNS server after subnet name column which is coming under vnet left panel in above kql query.

Can you please help us with the same

KapilAnanth-MSFT 39,051 Microsoft Employee

@Monalisa ,

For DNS,

resources
| where type =~ 'microsoft.network/virtualnetworks'
| project vnetId = id, vnetName = name, subnets = properties.subnets, dns = properties.dhcpOptions.dnsServers
| mv-expand subnets
| project vnetId, vnetName, subnetId1 = tostring(subnets.id), subnetName = subnets.name, dns
| join kind=inner (
    resources
    | where type has 'networkinterfaces'
    | project nicId = id, nicName = name, ipConfigurations = properties.ipConfigurations, 
              virtualMachineResourceId = properties.virtualMachine.id, //iff(isempty(properties.virtualMachine.id), id ,'Microsoft.Compute/virtualMachines')
              resourceType = iff(isempty(properties.virtualMachine.id),'Microsoft.Network/networkInterfaces','Microsoft.Compute/virtualMachines')
    | mv-expand ipConfigurations
    | extend subnetId2 = tostring(ipConfigurations.properties.subnet.id)
    | extend resourceGroup1 = tostring(split(subnetId2,"/",4)[0])
    | extend resourceName = coalesce(tostring(split(virtualMachineResourceId,"/",8)[0]),tostring(split(nicId,"/",8)[0])) 
    | project nicId, nicName, virtualMachineResourceId, resourceType, privateIpAddress = ipConfigurations.properties.privateIPAddress, subnetId2, resourceGroup1, resourceName
) on $left.subnetId1 == $right.subnetId2
| project-away subnetId2
|
union (
    resources
    | where type has 'microsoft.network/loadBalancers'
    | project lbId = id, lbName = name, ipConfigurations = properties.frontendIPConfigurations
    | mv-expand ipConfigurations
    | extend subnetId1 = tostring(ipConfigurations.properties.subnet.id)
    | extend resourceGroup1 = tostring(split(lbId,"/",4)[0])
    | project loadBalancerResourceId = lbId, resourceName = lbName, resourceType = 'Microsoft.Network/loadBalancers', privateIpAddress = ipConfigurations.properties.privateIPAddress, subnetId1, resourceGroup1
),
(
    resources
    | where type has 'microsoft.network/applicationGateways' 
 | project agId = id, agName = name, ipConfigurations = properties.gatewayIPConfigurations, privateIpAddress=properties.frontendIPConfigurations[1].properties.privateIPAddress
    | mv-expand ipConfigurations
    | extend subnetId1 = tostring(ipConfigurations.properties.subnet.id)
    | extend resourceGroup1 = tostring(split(agId,"/",4)[0])
    | project appGwResourceId = agId, resourceName = agName, resourceType = 'Microsoft.Network/applicationGateways', subnetId1,privateIpAddress, resourceGroup1
)
| join kind=fullouter (
resources
| join kind=leftouter(
    resourcecontainers 
    | where type=='microsoft.resources/subscriptions' 
    | project subscriptionName=name, subscriptionId
) on subscriptionId
| where type =~ 'Microsoft.Network/virtualNetworks'
| extend addressPrefixes=array_length(properties.addressSpace.addressPrefixes)
| extend vNetAddressSpace=properties.addressSpace.addressPrefixes
| mv-expand subnet=properties.subnets
| extend virtualNetwork = name
| extend subnetPrefix = subnet.properties.addressPrefix
| extend SubnetCIDR=subnet.properties.addressPrefix
| extend subnets = properties.subnets
| extend subnetName = tostring(subnet.name)
| extend subnetId = tostring(subnet.id)
| extend prefixLength = toint(split(subnetPrefix, "/")[1])
| extend addressPrefix = split(subnetPrefix, "/")[0]
| extend numberOfIpAddresses = trim_end(".0",tostring(pow(2, 32 - prefixLength) - 5))
| extend startIp = addressPrefix
| extend endIp = strcat(strcat_array((array_slice(split(addressPrefix, '.'), 0, 2)),"."),".",trim_end(".0",tostring(split(addressPrefix, '.')[3] + (pow(2, 32 - prefixLength) - 5))))
| extend endIPNew = case( prefixLength == 24, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'1.255'),
    prefixLength == 23, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'1.255'),
    prefixLength == 22, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'3.255'),
    prefixLength == 21, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'7.255'),
    prefixLength == 20, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'15.255'),
    prefixLength == 19, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'31.255'),
    prefixLength == 18, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'63.255'),
    prefixLength == 17, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'127.255'),
    prefixLength == 16, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'255.255'),
    'unknown'
)
| extend finalendIPaddress = iff(endIPNew == "unknown", endIp, endIPNew) 
| join kind=leftouter (
    // Number of connected devices per VNet and Subnet
    resources
    | where type =~ 'microsoft.network/networkinterfaces'
    | project id, ipConfigurations = properties.ipConfigurations, virtualMachine = tostring(split(properties.virtualMachine.id,"/",8)[0])
    | mvexpand ipConfigurations
    | project id, subnetId = tostring(ipConfigurations.properties.subnet.id), virtualMachine
    | parse kind=regex subnetId with '/virtualNetworks/' virtualNetwork '/subnets/' subnet
    | extend resourceGroup = tostring(split(subnetId,"/",4)[0])
    | extend subnetName = subnet
    | summarize usedIPAddresses = count() by subnetName, virtualNetwork
)
on subnetName, virtualNetwork
| extend usedIPAddresses_new = iff(isnull(usedIPAddresses),0,usedIPAddresses)
| extend privateIP = properties.privateIpAddress
| project subscriptionName, resourceGroup, virtualNetwork, SubnetName = subnet.name, IPRange = strcat(startIp, " - ", finalendIPaddress), numberOfIpAddresses, SubnetCIDR, usedIPAddresses, AvailableIPAddresses = (toint(numberOfIpAddresses) - usedIPAddresses_new), subnetId
) on $left.subnetId1 == $right.subnetId
| project-away subnetId1
| project subscriptionName, resourceGroup=coalesce(resourceGroup,resourceGroup1), virtualNetwork, dns=coalesce(dns,"Default DNS Server"), SubnetName=coalesce(SubnetName,""), IPRange=coalesce(IPRange,"Isolated"), numberOfIpAddresses, SubnetCIDR=coalesce(SubnetCIDR,""), usedIPAddresses=coalesce(usedIPAddresses,0), AvailableIPAddresses=coalesce(AvailableIPAddresses,0), resourceType=coalesce(resourceType,"Subnet"), resourceName, privateIpAddress=iff(isnull(privateIpAddress),"No Private IP",privateIpAddress) //,
| sort by resourceName,resourceType, virtualNetwork, SubnetName

Accepted answer

KapilAnanth-MSFT 39,051 Microsoft Employee

@Monalisa ,

Welcome to the Microsoft Q&A Platform. Thank you for reaching out & I hope you are doing well.

I see the Query1 lists the NICs, Load Balancers and Application Gateways and

Query2 lists all the subnets along with their address range, available IP addresses,

I don't understand why you would have a requirement to combine these 2, however, was able to create a Join query with SubnetID (SubnetName) column of both the queries and it is as follows.

resources
| where type =~ 'microsoft.network/virtualnetworks'
| project vnetId = id, vnetName = name, subnets = properties.subnets
| mv-expand subnets
| project vnetId, vnetName, subnetId1 = tostring(subnets.id), subnetName = subnets.name
| join kind=inner (
    resources
    | where type has 'networkinterfaces'
    | project nicId = id, nicName = name, ipConfigurations = properties.ipConfigurations, 
              virtualMachineResourceId = properties.virtualMachine.id, 
              resourceType = 'Microsoft.Compute/virtualMachines'
    | mv-expand ipConfigurations
    | extend subnetId2 = tostring(ipConfigurations.properties.subnet.id)
    | project nicId, nicName, virtualMachineResourceId, resourceType, privateIpAddress = ipConfigurations.properties.privateIPAddress, subnetId2
) on $left.subnetId1 == $right.subnetId2
| project-away subnetId2
|
union (
    resources
    | where type has 'microsoft.network/loadBalancers'
    | project lbId = id, lbName = name, ipConfigurations = properties.frontendIPConfigurations
    | mv-expand ipConfigurations
    | extend subnetId1 = tostring(ipConfigurations.properties.subnet.id)
    | project loadBalancerResourceId = lbId, resourceName = lbName, resourceType = 'Microsoft.Network/loadBalancers', privateIpAddress = ipConfigurations.properties.privateIPAddress, subnetId1
),
(
    resources
    | where type has 'microsoft.network/applicationGateways'
    | project agId = id, agName = name, ipConfigurations = properties.gatewayIPConfigurations
    | mv-expand ipConfigurations
    | extend subnetId1 = tostring(ipConfigurations.properties.subnet.id)
    | project appGwResourceId = agId, resourceName = agName, resourceType = 'Microsoft.Network/applicationGateways', privateIpAddress = ipConfigurations.properties.privateIPAddress, subnetId1
)
| join kind=fullouter (
resources
| where type =~ 'Microsoft.Network/virtualNetworks'
| extend addressPrefixes=array_length(properties.addressSpace.addressPrefixes)
| extend vNetAddressSpace=properties.addressSpace.addressPrefixes
| mv-expand subnet=properties.subnets
| extend virtualNetwork = name
| extend subnetPrefix = subnet.properties.addressPrefix
| extend SubnetCIDR=subnet.properties.addressPrefix
| extend subnets = properties.subnets
| extend subnetName = tostring(subnet.name)
| extend subnetId = tostring(subnet.id)
| extend prefixLength = toint(split(subnetPrefix, "/")[1])
| extend addressPrefix = split(subnetPrefix, "/")[0]
| extend numberOfIpAddresses = trim_end(".0",tostring(pow(2, 32 - prefixLength) - 5))
| extend startIp = addressPrefix
| extend endIp = strcat(strcat_array((array_slice(split(addressPrefix, '.'), 0, 2)),"."),".",trim_end(".0",tostring(split(addressPrefix, '.')[3] + (pow(2, 32 - prefixLength) - 5))))
| extend endIPNew = case(prefixLength == 23, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'1.255'),
    prefixLength == 22, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'3.255'),
    prefixLength == 21, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'7.255'),
    prefixLength == 20, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'15.255'),
    prefixLength == 19, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'31.255'),
    prefixLength == 18, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'63.255'),
    prefixLength == 17, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'127.255'),
    prefixLength == 16, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'255.255'),
    'unknown'
)
| extend finalendIPaddress = iff(endIPNew == "unknown", endIp, endIPNew) 
| join kind=leftouter (
    // Number of connected devices per VNet and Subnet
    resources
    | where type =~ 'microsoft.network/networkinterfaces'
    | project id, ipConfigurations = properties.ipConfigurations, virtualMachine = tostring(split(properties.virtualMachine.id,"/",8)[0])
    | mvexpand ipConfigurations
    | project id, subnetId = tostring(ipConfigurations.properties.subnet.id), virtualMachine
    | parse kind=regex subnetId with '/virtualNetworks/' virtualNetwork '/subnets/' subnet
    | extend resourceGroup = tostring(split(subnetId,"/",4)[0])
    | extend subnetName = subnet
    | summarize usedIPAddresses = count() by subnetName, virtualNetwork
)
on subnetName, virtualNetwork
| extend usedIPAddresses_new = iff(isnull(usedIPAddresses),0,usedIPAddresses)
| extend privateIP = properties.privateIpAddress
| project resourceGroup, virtualNetwork, SubnetName = subnet.name, IPRange = strcat(startIp, " - ", finalendIPaddress), numberOfIpAddresses, SubnetCIDR, usedIPAddresses, AvailableIPAddresses = (toint(numberOfIpAddresses) - usedIPAddresses_new), subnetId
) on $left.subnetId1 == $right.subnetId
| project-away subnetId1
| sort by subnetId

Kindly let us know if this helps or you need further assistance on this issue.

Thanks,

Kapil

Please don’t forget to close the thread by clicking "Accept the answer" wherever the information provided helps you, as this can be beneficial to other community members.

Monalisa 20 Reputation points

2024-06-20T11:38:13.18+00:00

Hi KapilAnanth-MSFT

Column A to J already we are getting and we have query for that .We need to add column K to O .

Please find the dummy data below for better understanding.

We need to query usedIPAddress , display connected or issolated to in column k,in column l its backend name where its connected, column M is private IPv4 , column N backend name, column O is for connected device type exp: virtual machine.
Monalisa 20 Reputation points

2024-06-20T15:15:56.73+00:00

Hi @KapilAnanth-MSFT

Thanks for your patience and support during this analysis.

The requirement has been revised. We do not need to get the K to O column.

The above query you have shared giving the expected result as per our new requirement.

The issue we are facing now that some subnet names are missing.

kindly provide your input if possible

Thanks and Regards

Monalisa
KapilAnanth-MSFT 39,051 Reputation points Microsoft Employee

2024-06-21T08:43:06.7933333+00:00
@Monalisa ,

Glad we could make some progress.

Are you sure some subnets are actually missing?

or

certain rows are just empty?

This is because Load Balancers are not part of a VNET or Subnet themselves and per your original request, I am clubbing two queries
#1 Lists Subnets and their address spaces
#2 Lists NICS (VMs), Load Balancers and App gateways

In #2, note that Load Balancers would return an empty value for Subnet and this is an expected behavior.

In case it's the former,

Are you sure those subnets are being listed with the original query?

Because I did a lab and the query I provided listed all the subnets I had

Hope this helps.

Cheers,

Kapil
Monalisa 20 Reputation points

2024-06-21T13:21:55.93+00:00
Hi @KapilAnanth-MSFT yes subnets are missing.

This is because Load Balancers are not part of a VNET or Subnet themselves and per your original request, I am clubbing two queries #1 Lists Subnets and their address spaces #2 Lists NICS (VMs), Load Balancers and App gateways

In #2, note that Load Balancers would return an empty value for Subnet and this is an expected behavior.

Ans : I will check on above bullet points.

Are you sure those subnets are being listed with the original query?

Ans: I have done the look up of subnets we are getting in first query (Lists Subnets and their address spaces) and can see some subnets are missing. This might be the reason that loadbalancers are not attached with any virtual network.
KapilAnanth-MSFT 39,051 Reputation points Microsoft Employee

2024-06-24T10:55:37.3833333+00:00
@Monalisa ,

Wrt, "I have done the look up of subnets we are getting in first query (Lists Subnets and their address spaces) and can see some subnets are missing"

Please make sure you have access to all the RGs/subscriptions

Because when I ran the Query#2 you gave, I was able to list every subnet I had

Wrt "This might be the reason that loadbalancers are not attached with any virtual network."

Load Balancers are never attached to any Subnet

So even if you were to get all the subnets with Query#2, I don't think we can associate LBs to Subnets because as mentioned, LBs are not attached to subnet in the first place.

Thanks,

Kapil

Please Accept an answer if correct.

Original posters help the community find answers faster by identifying the correct answer.
Monalisa 20 Reputation points

2024-06-24T11:37:17.3733333+00:00
Hi @KapilAnanth-MSFT

Thanks a lot to help us to make this much progress in this requirement.

There are two things we are adding in requirement.

Add column header with subscription name

Change the Null keyword to "isolated" in the IP column where it is not getting used.

Can you please help us with above requirement.

KapilAnanth-MSFT 39,051 Microsoft Employee

Hi @Monalisa ,

I have modified the query to meet your requirement.

I was able to get the resource group name of the Load Balancer
I was able to replace the "Null" field for Subnet CIDR with "Isolated"

resources
| where type =~ 'microsoft.network/virtualnetworks'
| project vnetId = id, vnetName = name, subnets = properties.subnets
| mv-expand subnets
| project vnetId, vnetName, subnetId1 = tostring(subnets.id), subnetName = subnets.name
| join kind=inner (
    resources
    | where type has 'networkinterfaces'
    | project nicId = id, nicName = name, ipConfigurations = properties.ipConfigurations, 
              virtualMachineResourceId = properties.virtualMachine.id, //iff(isempty(properties.virtualMachine.id), id ,'Microsoft.Compute/virtualMachines')
              resourceType = iff(isempty(properties.virtualMachine.id),'Microsoft.Network/networkInterfaces','Microsoft.Compute/virtualMachines')
    | mv-expand ipConfigurations
    | extend subnetId2 = tostring(ipConfigurations.properties.subnet.id)
    | extend resourceGroup1 = tostring(split(subnetId2,"/",4)[0])
    | project nicId, nicName, virtualMachineResourceId, resourceType, privateIpAddress = ipConfigurations.properties.privateIPAddress, subnetId2, resourceGroup1
) on $left.subnetId1 == $right.subnetId2
| project-away subnetId2
|
union (
    resources
    | where type has 'microsoft.network/loadBalancers'
    | project lbId = id, lbName = name, ipConfigurations = properties.frontendIPConfigurations
    | mv-expand ipConfigurations
    | extend subnetId1 = tostring(ipConfigurations.properties.subnet.id)
    | extend resourceGroup1 = tostring(split(lbId,"/",4)[0])
    | project loadBalancerResourceId = lbId, resourceName = lbName, resourceType = 'Microsoft.Network/loadBalancers', privateIpAddress = ipConfigurations.properties.privateIPAddress, subnetId1, resourceGroup1
),
(
    resources
    | where type has 'microsoft.network/applicationGateways'
    | project agId = id, agName = name, ipConfigurations = properties.gatewayIPConfigurations
    | mv-expand ipConfigurations
    | extend subnetId1 = tostring(ipConfigurations.properties.subnet.id)
    | extend resourceGroup1 = tostring(split(agId,"/",4)[0])
    | project appGwResourceId = agId, resourceName = agName, resourceType = 'Microsoft.Network/applicationGateways', privateIpAddress = ipConfigurations.properties.privateIPAddress, subnetId1, resourceGroup1
)
| join kind=fullouter (
resources
| join kind=leftouter(
    ResourceContainers 
    | where type=='microsoft.resources/subscriptions' 
    | project subscriptionName=name, subscriptionId
) on subscriptionId
| where type =~ 'Microsoft.Network/virtualNetworks'
| extend addressPrefixes=array_length(properties.addressSpace.addressPrefixes)
| extend vNetAddressSpace=properties.addressSpace.addressPrefixes
| mv-expand subnet=properties.subnets
| extend virtualNetwork = name
| extend subnetPrefix = subnet.properties.addressPrefix
| extend SubnetCIDR=subnet.properties.addressPrefix
| extend subnets = properties.subnets
| extend subnetName = tostring(subnet.name)
| extend subnetId = tostring(subnet.id)
| extend prefixLength = toint(split(subnetPrefix, "/")[1])
| extend addressPrefix = split(subnetPrefix, "/")[0]
| extend numberOfIpAddresses = trim_end(".0",tostring(pow(2, 32 - prefixLength) - 5))
| extend startIp = addressPrefix
| extend endIp = strcat(strcat_array((array_slice(split(addressPrefix, '.'), 0, 2)),"."),".",trim_end(".0",tostring(split(addressPrefix, '.')[3] + (pow(2, 32 - prefixLength) - 5))))
| extend endIPNew = case( prefixLength == 24, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'1.255'),
    prefixLength == 23, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'1.255'),
    prefixLength == 22, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'3.255'),
    prefixLength == 21, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'7.255'),
    prefixLength == 20, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'15.255'),
    prefixLength == 19, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'31.255'),
    prefixLength == 18, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'63.255'),
    prefixLength == 17, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'127.255'),
    prefixLength == 16, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'255.255'),
    'unknown'
)
| extend finalendIPaddress = iff(endIPNew == "unknown", endIp, endIPNew) 
| join kind=leftouter (
    // Number of connected devices per VNet and Subnet
    resources
    | where type =~ 'microsoft.network/networkinterfaces'
    | project id, ipConfigurations = properties.ipConfigurations, virtualMachine = tostring(split(properties.virtualMachine.id,"/",8)[0])
    | mvexpand ipConfigurations
    | project id, subnetId = tostring(ipConfigurations.properties.subnet.id), virtualMachine
    | parse kind=regex subnetId with '/virtualNetworks/' virtualNetwork '/subnets/' subnet
    | extend resourceGroup = tostring(split(subnetId,"/",4)[0])
    | extend subnetName = subnet
    | summarize usedIPAddresses = count() by subnetName, virtualNetwork
)
on subnetName, virtualNetwork
| extend usedIPAddresses_new = iff(isnull(usedIPAddresses),0,usedIPAddresses)
| extend privateIP = properties.privateIpAddress
| project subscriptionName, resourceGroup, virtualNetwork, SubnetName = subnet.name, IPRange = strcat(startIp, " - ", finalendIPaddress), numberOfIpAddresses, SubnetCIDR, usedIPAddresses, AvailableIPAddresses = (toint(numberOfIpAddresses) - usedIPAddresses_new), subnetId
) on $left.subnetId1 == $right.subnetId
| project-away subnetId1
| project subscriptionName, resourceGroup=coalesce(resourceGroup,resourceGroup1), virtualNetwork, SubnetName=coalesce(SubnetName,""), IPRange=coalesce(IPRange,"Isolated"), numberOfIpAddresses, SubnetCIDR=coalesce(SubnetCIDR,""), usedIPAddresses=coalesce(usedIPAddresses,0), AvailableIPAddresses=coalesce(AvailableIPAddresses,0) //,
| sort by virtualNetwork, SubnetName

However, I don't think we can get the "Subscription name"

"Subscription name" is not natively available in Azure Graph Explorer and we have to run JOINS to get it.
It appears a single query can have a maximum of 4 Joins.
To have "Subscription name" in such a complex query which queries 4 types of resources (Subnet/VNET, NICs, App gateways, LBs) - I am afraid we cannot afford 4 separate joins and still correlate the query.

Cheers,

Kapil

Please don’t forget to close the thread by clicking "Accept the answer" wherever the information provided helps you, as this can be beneficial to other community members.

Monalisa 20 Reputation points

2024-06-24T15:14:01.8866667+00:00
Hi @KapilAnanth-MSFT

I am very much thankful for your patience and support towards us

In above query I am unable to get below two columns

RESOURCETYPE

PRIVATEIPADDRESS

If it is difficult to append subscription name ,we can remove subscription name from column

Below requirement will be same .

Change the Null keyword to "isolated" in the IP column where it is not getting used.

KapilAnanth-MSFT 39,051 Microsoft Employee

Hi @Monalisa ,

You are welcome :)

Per your requirement,

RESOURCETYPE was added
PRIVATEIPADDRESS was added and resources which don't have a Private IP (Such as Public LB and Public App Gateway) , it will say "No Private IP"
Change the Null keyword to "isolated" in the IP column where it is not getting used - You can use the coalesce() to change null values to whatever string/int value you want
- I have used it in "IPRange" field to change it to "Isolated"
- You can use the same in whichever field(s) you want to be "Isolated"

I would highly appreciate if you could Accept the answer.

Original posters help the community find answers faster by identifying the correct answer.

resources
| where type =~ 'microsoft.network/virtualnetworks'
| project vnetId = id, vnetName = name, subnets = properties.subnets
| mv-expand subnets
| project vnetId, vnetName, subnetId1 = tostring(subnets.id), subnetName = subnets.name
| join kind=inner (
    resources
    | where type has 'networkinterfaces'
    | project nicId = id, nicName = name, ipConfigurations = properties.ipConfigurations, 
              virtualMachineResourceId = properties.virtualMachine.id, //iff(isempty(properties.virtualMachine.id), id ,'Microsoft.Compute/virtualMachines')
              resourceType = iff(isempty(properties.virtualMachine.id),'Microsoft.Network/networkInterfaces','Microsoft.Compute/virtualMachines')
    | mv-expand ipConfigurations
    | extend subnetId2 = tostring(ipConfigurations.properties.subnet.id)
    | extend resourceGroup1 = tostring(split(subnetId2,"/",4)[0])
    | project nicId, nicName, virtualMachineResourceId, resourceType, privateIpAddress = ipConfigurations.properties.privateIPAddress, subnetId2, resourceGroup1
) on $left.subnetId1 == $right.subnetId2
| project-away subnetId2
|
union (
    resources
    | where type has 'microsoft.network/loadBalancers'
    | project lbId = id, lbName = name, ipConfigurations = properties.frontendIPConfigurations
    | mv-expand ipConfigurations
    | extend subnetId1 = tostring(ipConfigurations.properties.subnet.id)
    | extend resourceGroup1 = tostring(split(lbId,"/",4)[0])
    | project loadBalancerResourceId = lbId, resourceName = lbName, resourceType = 'Microsoft.Network/loadBalancers', privateIpAddress = ipConfigurations.properties.privateIPAddress, subnetId1, resourceGroup1
),
(
    resources
    | where type has 'microsoft.network/applicationGateways' 
 | project agId = id, agName = name, ipConfigurations = properties.gatewayIPConfigurations, privateIpAddress=properties.frontendIPConfigurations[1].properties.privateIPAddress
    | mv-expand ipConfigurations
    | extend subnetId1 = tostring(ipConfigurations.properties.subnet.id)
    | extend resourceGroup1 = tostring(split(agId,"/",4)[0])
    | project appGwResourceId = agId, resourceName = agName, resourceType = 'Microsoft.Network/applicationGateways', subnetId1,privateIpAddress, resourceGroup1
)
| join kind=fullouter (
resources
| join kind=leftouter(
    ResourceContainers 
    | where type=='microsoft.resources/subscriptions' 
    | project subscriptionName=name, subscriptionId
) on subscriptionId
| where type =~ 'Microsoft.Network/virtualNetworks'
| extend addressPrefixes=array_length(properties.addressSpace.addressPrefixes)
| extend vNetAddressSpace=properties.addressSpace.addressPrefixes
| mv-expand subnet=properties.subnets
| extend virtualNetwork = name
| extend subnetPrefix = subnet.properties.addressPrefix
| extend SubnetCIDR=subnet.properties.addressPrefix
| extend subnets = properties.subnets
| extend subnetName = tostring(subnet.name)
| extend subnetId = tostring(subnet.id)
| extend prefixLength = toint(split(subnetPrefix, "/")[1])
| extend addressPrefix = split(subnetPrefix, "/")[0]
| extend numberOfIpAddresses = trim_end(".0",tostring(pow(2, 32 - prefixLength) - 5))
| extend startIp = addressPrefix
| extend endIp = strcat(strcat_array((array_slice(split(addressPrefix, '.'), 0, 2)),"."),".",trim_end(".0",tostring(split(addressPrefix, '.')[3] + (pow(2, 32 - prefixLength) - 5))))
| extend endIPNew = case( prefixLength == 24, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'1.255'),
    prefixLength == 23, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'1.255'),
    prefixLength == 22, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'3.255'),
    prefixLength == 21, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'7.255'),
    prefixLength == 20, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'15.255'),
    prefixLength == 19, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'31.255'),
    prefixLength == 18, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'63.255'),
    prefixLength == 17, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'127.255'),
    prefixLength == 16, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'255.255'),
    'unknown'
)
| extend finalendIPaddress = iff(endIPNew == "unknown", endIp, endIPNew) 
| join kind=leftouter (
    // Number of connected devices per VNet and Subnet
    resources
    | where type =~ 'microsoft.network/networkinterfaces'
    | project id, ipConfigurations = properties.ipConfigurations, virtualMachine = tostring(split(properties.virtualMachine.id,"/",8)[0])
    | mvexpand ipConfigurations
    | project id, subnetId = tostring(ipConfigurations.properties.subnet.id), virtualMachine
    | parse kind=regex subnetId with '/virtualNetworks/' virtualNetwork '/subnets/' subnet
    | extend resourceGroup = tostring(split(subnetId,"/",4)[0])
    | extend subnetName = subnet
    | summarize usedIPAddresses = count() by subnetName, virtualNetwork
)
on subnetName, virtualNetwork
| extend usedIPAddresses_new = iff(isnull(usedIPAddresses),0,usedIPAddresses)
| extend privateIP = properties.privateIpAddress
| project subscriptionName, resourceGroup, virtualNetwork, SubnetName = subnet.name, IPRange = strcat(startIp, " - ", finalendIPaddress), numberOfIpAddresses, SubnetCIDR, usedIPAddresses, AvailableIPAddresses = (toint(numberOfIpAddresses) - usedIPAddresses_new), subnetId
) on $left.subnetId1 == $right.subnetId
| project-away subnetId1
| project subscriptionName, resourceGroup=coalesce(resourceGroup,resourceGroup1), virtualNetwork, SubnetName=coalesce(SubnetName,""), IPRange=coalesce(IPRange,"Isolated"), numberOfIpAddresses, SubnetCIDR=coalesce(SubnetCIDR,""), usedIPAddresses=coalesce(usedIPAddresses,0), AvailableIPAddresses=coalesce(AvailableIPAddresses,0), resourceType=coalesce(resourceType,"Subnet"), privateIpAddress=iff(isnull(privateIpAddress),"No Private IP",privateIpAddress) //,
| sort by resourceType, virtualNetwork, SubnetName

Monalisa 20

Hi KapilAnanth-MSFT

Thanks a lot for your support . I accepted the answer.

I also wanted to add resource name so I modified accordingly . Please find the query below but in some entry resource name showing blank.

resources
| where type =~ 'microsoft.network/virtualnetworks'
| project vnetId = id, vnetName = name, subnets = properties.subnets
| mv-expand subnets
| project vnetId, vnetName, subnetId1 = tostring(subnets.id), subnetName = subnets.name
| join kind=inner (
    resources
    | where type has 'networkinterfaces'
    | project nicId = id, nicName = name, ipConfigurations = properties.ipConfigurations, 
              virtualMachineResourceId = properties.virtualMachine.id, //iff(isempty(properties.virtualMachine.id), id ,'Microsoft.Compute/virtualMachines')
              resourceType = iff(isempty(properties.virtualMachine.id),'Microsoft.Network/networkInterfaces','Microsoft.Compute/virtualMachines')
    | mv-expand ipConfigurations
    | extend subnetId2 = tostring(ipConfigurations.properties.subnet.id)
    | extend resourceGroup1 = tostring(split(subnetId2,"/",4)[0])
    | project nicId, nicName, virtualMachineResourceId, resourceType, privateIpAddress = ipConfigurations.properties.privateIPAddress, subnetId2, resourceGroup1
) on $left.subnetId1 == $right.subnetId2
| project-away subnetId2
|
union (
    resources
    | where type has 'microsoft.network/loadBalancers'
    | project lbId = id, lbName = name, ipConfigurations = properties.frontendIPConfigurations
    | mv-expand ipConfigurations
    | extend subnetId1 = tostring(ipConfigurations.properties.subnet.id)
    | extend resourceGroup1 = tostring(split(lbId,"/",4)[0])
    | project loadBalancerResourceId = lbId, resourceName = lbName, resourceType = 'Microsoft.Network/loadBalancers', privateIpAddress = ipConfigurations.properties.privateIPAddress, subnetId1, resourceGroup1
),
(
    resources
    | where type has 'microsoft.network/applicationGateways' 
 | project agId = id, agName = name, ipConfigurations = properties.gatewayIPConfigurations, privateIpAddress=properties.frontendIPConfigurations[1].properties.privateIPAddress
    | mv-expand ipConfigurations
    | extend subnetId1 = tostring(ipConfigurations.properties.subnet.id)
    | extend resourceGroup1 = tostring(split(agId,"/",4)[0])
    | project appGwResourceId = agId, resourceName = agName, resourceType = 'Microsoft.Network/applicationGateways', subnetId1,privateIpAddress, resourceGroup1
)
| join kind=fullouter (
resources
| join kind=leftouter(
    resourcecontainers 
    | where type=='microsoft.resources/subscriptions' 
    | project subscriptionName=name, subscriptionId
) on subscriptionId
| where type =~ 'Microsoft.Network/virtualNetworks'
| extend addressPrefixes=array_length(properties.addressSpace.addressPrefixes)
| extend vNetAddressSpace=properties.addressSpace.addressPrefixes
| mv-expand subnet=properties.subnets
| extend virtualNetwork = name
| extend subnetPrefix = subnet.properties.addressPrefix
| extend SubnetCIDR=subnet.properties.addressPrefix
| extend subnets = properties.subnets
| extend subnetName = tostring(subnet.name)
| extend subnetId = tostring(subnet.id)
| extend prefixLength = toint(split(subnetPrefix, "/")[1])
| extend addressPrefix = split(subnetPrefix, "/")[0]
| extend numberOfIpAddresses = trim_end(".0",tostring(pow(2, 32 - prefixLength) - 5))
| extend startIp = addressPrefix
| extend endIp = strcat(strcat_array((array_slice(split(addressPrefix, '.'), 0, 2)),"."),".",trim_end(".0",tostring(split(addressPrefix, '.')[3] + (pow(2, 32 - prefixLength) - 5))))
| extend endIPNew = case( prefixLength == 24, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'1.255'),
    prefixLength == 23, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'1.255'),
    prefixLength == 22, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'3.255'),
    prefixLength == 21, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'7.255'),
    prefixLength == 20, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'15.255'),
    prefixLength == 19, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'31.255'),
    prefixLength == 18, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'63.255'),
    prefixLength == 17, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'127.255'),
    prefixLength == 16, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'255.255'),
    'unknown'
)
| extend finalendIPaddress = iff(endIPNew == "unknown", endIp, endIPNew) 
| join kind=leftouter (
    // Number of connected devices per VNet and Subnet
    resources
    | where type =~ 'microsoft.network/networkinterfaces'
    | project id, ipConfigurations = properties.ipConfigurations, virtualMachine = tostring(split(properties.virtualMachine.id,"/",8)[0])
    | mvexpand ipConfigurations
    | project id, subnetId = tostring(ipConfigurations.properties.subnet.id), virtualMachine
    | parse kind=regex subnetId with '/virtualNetworks/' virtualNetwork '/subnets/' subnet
    | extend resourceGroup = tostring(split(subnetId,"/",4)[0])
    | extend subnetName = subnet
    | summarize usedIPAddresses = count() by subnetName, virtualNetwork
)
on subnetName, virtualNetwork
| extend usedIPAddresses_new = iff(isnull(usedIPAddresses),0,usedIPAddresses)
| extend privateIP = properties.privateIpAddress
| project subscriptionName, resourceGroup, virtualNetwork, SubnetName = subnet.name, IPRange = strcat(startIp, " - ", finalendIPaddress), numberOfIpAddresses, SubnetCIDR, usedIPAddresses, AvailableIPAddresses = (toint(numberOfIpAddresses) - usedIPAddresses_new), subnetId
) on $left.subnetId1 == $right.subnetId
| project-away subnetId1
| project subscriptionName, resourceGroup=coalesce(resourceGroup,resourceGroup1), virtualNetwork, SubnetName=coalesce(SubnetName,""), IPRange=coalesce(IPRange,"Isolated"), numberOfIpAddresses, SubnetCIDR=coalesce(SubnetCIDR,""), usedIPAddresses=coalesce(usedIPAddresses,0), AvailableIPAddresses=coalesce(AvailableIPAddresses,0), resourceType=coalesce(resourceType,"Subnet"), resourceName, privateIpAddress=iff(isnull(privateIpAddress),"No Private IP",privateIpAddress) //,
| sort by resourceName,resourceType, virtualNetwork, SubnetName

can you please help us to add resource name in column

Monalisa 20 Reputation points

2024-06-25T13:26:39.4333333+00:00

Hi @KapilAnanth-MSFT

can you please help us to add resourceName in above queryand resourceName should be last value of entire string

/subscriptions/xxxxxx/resourceGroups/cccccc/providers/Microsoft.Compute/virtualMachines/BTHJHEJBHBF34

resource name should only print BTHJHEJBHBF34 from above string

KapilAnanth-MSFT 39,051 Microsoft Employee

Hi @Monalisa ,

Please use the below, I added query for VM Name, Nic Name and LB Name.

resources
| where type =~ 'microsoft.network/virtualnetworks'
| project vnetId = id, vnetName = name, subnets = properties.subnets
| mv-expand subnets
| project vnetId, vnetName, subnetId1 = tostring(subnets.id), subnetName = subnets.name
| join kind=inner (
    resources
    | where type has 'networkinterfaces'
    | project nicId = id, nicName = name, ipConfigurations = properties.ipConfigurations, 
              virtualMachineResourceId = properties.virtualMachine.id, //iff(isempty(properties.virtualMachine.id), id ,'Microsoft.Compute/virtualMachines')
              resourceType = iff(isempty(properties.virtualMachine.id),'Microsoft.Network/networkInterfaces','Microsoft.Compute/virtualMachines')
    | mv-expand ipConfigurations
    | extend subnetId2 = tostring(ipConfigurations.properties.subnet.id)
    | extend resourceGroup1 = tostring(split(subnetId2,"/",4)[0])
    | extend resourceName = coalesce(tostring(split(virtualMachineResourceId,"/",8)[0]),tostring(split(nicId,"/",8)[0])) 
    | project nicId, nicName, virtualMachineResourceId, resourceType, privateIpAddress = ipConfigurations.properties.privateIPAddress, subnetId2, resourceGroup1, resourceName
) on $left.subnetId1 == $right.subnetId2
| project-away subnetId2
|
union (
    resources
    | where type has 'microsoft.network/loadBalancers'
    | project lbId = id, lbName = name, ipConfigurations = properties.frontendIPConfigurations
    | mv-expand ipConfigurations
    | extend subnetId1 = tostring(ipConfigurations.properties.subnet.id)
    | extend resourceGroup1 = tostring(split(lbId,"/",4)[0])
    | project loadBalancerResourceId = lbId, resourceName = lbName, resourceType = 'Microsoft.Network/loadBalancers', privateIpAddress = ipConfigurations.properties.privateIPAddress, subnetId1, resourceGroup1
),
(
    resources
    | where type has 'microsoft.network/applicationGateways' 
 | project agId = id, agName = name, ipConfigurations = properties.gatewayIPConfigurations, privateIpAddress=properties.frontendIPConfigurations[1].properties.privateIPAddress
    | mv-expand ipConfigurations
    | extend subnetId1 = tostring(ipConfigurations.properties.subnet.id)
    | extend resourceGroup1 = tostring(split(agId,"/",4)[0])
    | project appGwResourceId = agId, resourceName = agName, resourceType = 'Microsoft.Network/applicationGateways', subnetId1,privateIpAddress, resourceGroup1
)
| join kind=fullouter (
resources
| join kind=leftouter(
    resourcecontainers 
    | where type=='microsoft.resources/subscriptions' 
    | project subscriptionName=name, subscriptionId
) on subscriptionId
| where type =~ 'Microsoft.Network/virtualNetworks'
| extend addressPrefixes=array_length(properties.addressSpace.addressPrefixes)
| extend vNetAddressSpace=properties.addressSpace.addressPrefixes
| mv-expand subnet=properties.subnets
| extend virtualNetwork = name
| extend subnetPrefix = subnet.properties.addressPrefix
| extend SubnetCIDR=subnet.properties.addressPrefix
| extend subnets = properties.subnets
| extend subnetName = tostring(subnet.name)
| extend subnetId = tostring(subnet.id)
| extend prefixLength = toint(split(subnetPrefix, "/")[1])
| extend addressPrefix = split(subnetPrefix, "/")[0]
| extend numberOfIpAddresses = trim_end(".0",tostring(pow(2, 32 - prefixLength) - 5))
| extend startIp = addressPrefix
| extend endIp = strcat(strcat_array((array_slice(split(addressPrefix, '.'), 0, 2)),"."),".",trim_end(".0",tostring(split(addressPrefix, '.')[3] + (pow(2, 32 - prefixLength) - 5))))
| extend endIPNew = case( prefixLength == 24, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'1.255'),
    prefixLength == 23, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'1.255'),
    prefixLength == 22, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'3.255'),
    prefixLength == 21, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'7.255'),
    prefixLength == 20, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'15.255'),
    prefixLength == 19, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'31.255'),
    prefixLength == 18, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'63.255'),
    prefixLength == 17, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'127.255'),
    prefixLength == 16, strcat(strcat(strcat_array((array_slice(split(startIp,'.'), 0, 1)), "."), "."),'255.255'),
    'unknown'
)
| extend finalendIPaddress = iff(endIPNew == "unknown", endIp, endIPNew) 
| join kind=leftouter (
    // Number of connected devices per VNet and Subnet
    resources
    | where type =~ 'microsoft.network/networkinterfaces'
    | project id, ipConfigurations = properties.ipConfigurations, virtualMachine = tostring(split(properties.virtualMachine.id,"/",8)[0])
    | mvexpand ipConfigurations
    | project id, subnetId = tostring(ipConfigurations.properties.subnet.id), virtualMachine
    | parse kind=regex subnetId with '/virtualNetworks/' virtualNetwork '/subnets/' subnet
    | extend resourceGroup = tostring(split(subnetId,"/",4)[0])
    | extend subnetName = subnet
    | summarize usedIPAddresses = count() by subnetName, virtualNetwork
)
on subnetName, virtualNetwork
| extend usedIPAddresses_new = iff(isnull(usedIPAddresses),0,usedIPAddresses)
| extend privateIP = properties.privateIpAddress
| project subscriptionName, resourceGroup, virtualNetwork, SubnetName = subnet.name, IPRange = strcat(startIp, " - ", finalendIPaddress), numberOfIpAddresses, SubnetCIDR, usedIPAddresses, AvailableIPAddresses = (toint(numberOfIpAddresses) - usedIPAddresses_new), subnetId
) on $left.subnetId1 == $right.subnetId
| project-away subnetId1
| project subscriptionName, resourceGroup=coalesce(resourceGroup,resourceGroup1), virtualNetwork, SubnetName=coalesce(SubnetName,""), IPRange=coalesce(IPRange,"Isolated"), numberOfIpAddresses, SubnetCIDR=coalesce(SubnetCIDR,""), usedIPAddresses=coalesce(usedIPAddresses,0), AvailableIPAddresses=coalesce(AvailableIPAddresses,0), resourceType=coalesce(resourceType,"Subnet"), resourceName, privateIpAddress=iff(isnull(privateIpAddress),"No Private IP",privateIpAddress) //,
| sort by resourceName,resourceType, virtualNetwork, SubnetName

Share via

KQL queries to get the details

0 additional answers