Forum Discussion
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
CliveWatson
Microsoft
Microsoft
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 :NAME VIRTUAL NETWORK PRIMARY PRIVATE IP ATTACHED TO RESOURCE GROUP LOCATION SUBSCRIPTION Subnet NetWork Security Group - CliveWatson
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 includeResources
| join (ResourceContainers | where type=='microsoft.resources/subscriptions' | project SubName=name, subscriptionId) on subscriptionIdAs 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?
