KQL queries to get the details

Monalisa 60 Reputation points
2024-06-19T14:37:25.7866667+00:00

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)

Azure Virtual Network
Azure Virtual Network
An Azure networking service that is used to provision private networks and optionally to connect to on-premises datacenters.
2,291 questions
{count} votes

Accepted answer
  1. KapilAnanth-MSFT 41,071 Reputation points Microsoft Employee
    2024-06-20T06:06:59.5966667+00:00

    @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.


0 additional answers

Sort by: Most helpful