How to query all NIC details

%3CLINGO-SUB%20id%3D%22lingo-sub-2591778%22%20slang%3D%22en-US%22%3EHow%20to%20query%20all%20NIC%20details%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2591778%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20KQL%20query%20to%20get%20the%20list%20of%20something%20as%20attached%20for%20NIC%2C%20can%20someone%20please%20help%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EDeb%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2593644%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20query%20all%20NIC%20details%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2593644%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1084141%22%20target%3D%22_blank%22%3E%40deb0093%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMaybe%20start%20with%20a%20Azure%20Resource%20Graph%20query%20using%20KQL%2C%20this%20is%20an%20example%20the%20ARG%20have%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3E%2F%20List%20virtual%20machines%20with%20their%20network%20interface%20and%20public%20IP%0A%2F%2F%20Returns%20a%20list%20of%20virtual%20machines%2C%20their%20related%20network%20interfaces%2C%20and%20any%20public%20IP%20address%20related%20to%20those%20network%20interfaces.%0A%2F%2F%20Click%20the%20%22Run%20query%22%20command%20above%20to%20execute%20the%20query%20and%20see%20results.%0AResources%0A%7C%20where%20type%20%3D~%20'microsoft.compute%2Fvirtualmachines'%0A%7C%20extend%20nics%3Darray_length(properties.networkProfile.networkInterfaces)%0A%7C%20mv-expand%20nic%3Dproperties.networkProfile.networkInterfaces%0A%7C%20where%20nics%20%3D%3D%201%20or%20nic.properties.primary%20%3D~%20'true'%20or%20isempty(nic)%0A%7C%20project%20vmId%20%3D%20id%2C%20vmName%20%3D%20name%2C%20vmSize%3Dtostring(properties.hardwareProfile.vmSize)%2C%20nicId%20%3D%20tostring(nic.id)%0A%7C%20join%20kind%3Dleftouter%20(%0A%20%20Resources%0A%20%20%7C%20where%20type%20%3D~%20'microsoft.network%2Fnetworkinterfaces'%0A%20%20%7C%20extend%20ipConfigsCount%3Darray_length(properties.ipConfigurations)%0A%20%20%7C%20mv-expand%20ipconfig%3Dproperties.ipConfigurations%0A%20%20%7C%20where%20ipConfigsCount%20%3D%3D%201%20or%20ipconfig.properties.primary%20%3D~%20'true'%0A%20%20%7C%20project%20nicId%20%3D%20id%2C%20publicIpId%20%3D%20tostring(ipconfig.properties.publicIPAddress.id))%20on%20nicId%0A%7C%20project-away%20nicId1%0A%7C%20summarize%20by%20vmId%2C%20vmName%2C%20vmSize%2C%20nicId%2C%20publicIpId%0A%7C%20join%20kind%3Dleftouter%20(%0A%20%20Resources%0A%20%20%7C%20where%20type%20%3D~%20'microsoft.network%2Fpublicipaddresses'%0A%20%20%7C%20project%20publicIpId%20%3D%20id%2C%20publicIpAddress%20%3D%20properties.ipAddress)%20on%20publicIpId%0A%7C%20project-away%20publicIpId1%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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

7 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

 

@Clive Watson 

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 @Clive Watson 
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

 

@Clive Watson 

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