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.