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