Forum Discussion

deb0093's avatar
deb0093
Copper Contributor
Jul 27, 2021

How to query all NIC details

Hello,   I am looking for KQL query to get the list of something as attached for NIC, can someone please help me.   Regards Deb
azure monitor
CliveWatson's avatar
CliveWatson
Former Employee
Jul 28, 2021

deb0093 

 

Maybe start with a Azure Resource Graph query using KQL, this is an example the ARG have:

 

/ List virtual machines with their network interface and public IP
// Returns a list of virtual machines, their related network interfaces, and any public IP address related to those network interfaces.
// Click the "Run query" command above to execute the query and see results.
Resources
| where type =~ 'microsoft.compute/virtualmachines'
| extend nics=array_length(properties.networkProfile.networkInterfaces)
| mv-expand nic=properties.networkProfile.networkInterfaces
| where nics == 1 or nic.properties.primary =~ 'true' or isempty(nic)
| project vmId = id, vmName = name, vmSize=tostring(properties.hardwareProfile.vmSize), nicId = tostring(nic.id)
| join kind=leftouter (
  Resources
  | where type =~ 'microsoft.network/networkinterfaces'
  | extend ipConfigsCount=array_length(properties.ipConfigurations)
  | mv-expand ipconfig=properties.ipConfigurations
  | where ipConfigsCount == 1 or ipconfig.properties.primary =~ 'true'
  | project nicId = id, publicIpId = tostring(ipconfig.properties.publicIPAddress.id)) on nicId
| project-away nicId1
| summarize by vmId, vmName, vmSize, nicId, publicIpId
| join kind=leftouter (
  Resources
  | where type =~ 'microsoft.network/publicipaddresses'
  | project publicIpId = id, publicIpAddress = properties.ipAddress) on publicIpId
| project-away publicIpId1

 

deb0093's avatar
deb0093
Copper Contributor
Jul 28, 2021

CliveWatson 

I am looking for something :

NAMEVIRTUAL NETWORKPRIMARY PRIVATE IPATTACHED TORESOURCE GROUPLOCATIONSUBSCRIPTIONSubnetNetWork Security Group
  • CliveWatson's avatar
    CliveWatson
    Former Employee
    Jul 28, 2021

    deb0093 

    More like this example?  I hope this helps you as an example (sorry but I cant answer every request I get, or fully deliver a full script, but I hope this is enough to get you started).  You will need to work out what to put in the "attached to" column as I didn't know what that mapped to. 

    
Resources
| where type =~ 'microsoft.compute/virtualmachines'
| extend nics=array_length(properties.networkProfile.networkInterfaces)
| mv-expand nic=properties.networkProfile.networkInterfaces
| where nics == 1 or nic.properties.primary =~ 'true' or isempty(nic)
| project vmId = id, vmName = name, vmSize=tostring(properties.hardwareProfile.vmSize), nicId = tostring(nic.id)
| join kind=leftouter (
Resources
  | where type =~ 'microsoft.network/networkinterfaces'
  | extend ipConfigsCount=array_length(properties.ipConfigurations)
  | mv-expand ipconfig=properties.ipConfigurations
  | where ipConfigsCount == 1 or ipconfig.properties.primary =~ 'true'
  | project nicId = id, publicIpId = tostring(ipconfig.properties.publicIPAddress.id) , name, location, subscriptionId, subnetId = tostring(ipconfig.properties.subnet.id), resourceGroup
| parse kind=regex subnetId with '/virtualNetworks/' virtualNetwork '/subnets/' subnet ) on nicId  
| join (
resources
| where type =~ 'microsoft.network/networkinterfaces'
| mv-expand properties.networkSecurityGroup
| extend nsg_ = tostring(properties_networkSecurityGroup.id)
| parse kind=regex nsg_ with '/networkSecurityGroups/' nsgName 
| summarize make_set(nsgName) by name
) on name
| project-away name1, vmSize, vmId
| project Name=vmName, virtualNetwork, publicIpId, attachedto="I dont know!", resourceGroup, location, Subscription=subscriptionId, subnet, NSG=set_nsgName


     

    • deb0093's avatar
      deb0093
      Copper Contributor
      Jul 29, 2021

      Thanks CliveWatson 
      Where to include

      Resources
      | join (ResourceContainers | where type=='microsoft.resources/subscriptions' | project SubName=name, subscriptionId) on subscriptionId

      As above to get the subscription names in KQL query you have mentioned.

      And my attached to list out are all the list of resources under that subscription.

       

      NSG Value I am getting as attached, seems to be null, is it because I have nothing as attached to?

       

       

      • CliveWatson's avatar
        CliveWatson
        Former Employee
        Jul 29, 2021

        deb0093 Yes, it looks like you don't have an NSG attached. The query above already displays the subscriptionID, so why do you want it from another type?

         

        You can check NSG associated to NICs with this code section

        resources
| where type =~ 'microsoft.network/networkinterfaces'
| mv-expand properties.networkSecurityGroup
| extend nsg_ = tostring(properties_networkSecurityGroup.id)
| parse kind=regex nsg_ with '/networkSecurityGroups/' nsgName 
| where isnotempty(nsg_)
| project nsg_, nsgName

         

Resources