How to query all NIC details

Copper Contributor

Hello,

 

I am looking for KQL query to get the list of something as attached for NIC, can someone please help me.

 

Regards

Deb

9 Replies

@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

 

@CliveWatson 

I am looking for something :

NAMEVIRTUAL NETWORKPRIMARY PRIVATE IPATTACHED TORESOURCE GROUPLOCATIONSUBSCRIPTIONSubnetNetWork Security Group

@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


 

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?

 

 

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

Screenshot 2021-07-29 173225.png

 

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

 

@CliveWatson 

Thanks, My requirement is to get the data as Subscription Names, and How to get the details of all attached to components?

 

ResourceContainers 
| where type=='microsoft.resources/subscriptions' 
| project SubName=name, subscriptionId
| join 
(
	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), subscriptionId
) on subscriptionId
| 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, SubName, Subscription=subscriptionId, subnet, NSG=set_nsgName 

@deb0093 

@CliveWatson Could you please help me to get the below details along with Secondary Ip address and Public address & Name as well 

 

Im looking for you response 

 

Thanks you

@Rajeshsura 

 

Does this help?  I suspect this needs a re-write now, I'm not sure I have the time for that though. 

ResourceContainers 
| where type=='microsoft.resources/subscriptions' 
| project SubName=name, subscriptionId
| join 
(
	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)
    | parse tostring(nic.id) with * '/networkInterfaces/' nicName_
	| project vmId = id, vmName = name, vmSize=tostring(properties.hardwareProfile.vmSize), nicId = tostring(nic.id), subscriptionId, nics, nicName_
) on subscriptionId
| 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), PrivateIPaddr = tostring(ipconfig.properties.privateIPAddress), 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, SubName, Subscription=subscriptionId, subnet, NSG=set_nsgName , nicName_, nics
| summarize arg_max(vmName,virtualNetwork, publicIpId, attachedto="I dont know!", resourceGroup, location, SubName, Subscription=subscriptionId, subnet, NSG=set_nsgName , NicCount=nics),
			NameofNic=make_set( nicName_),
            PublicIPlist = make_set(publicIpId),
            PrivateIPaddr = make_set(PrivateIPaddr)
            by Name=vmName