Jul 27 2021
08:34 AM
- last edited on
Apr 08 2022
10:51 AM
by
TechCommunityAP
Jul 27 2021
08:34 AM
- last edited on
Apr 08 2022
10:51 AM
by
TechCommunityAP
Hello,
I am looking for KQL query to get the list of something as attached for NIC, can someone please help me.
Regards
Deb
Jul 28 2021 12:51 AM
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
Jul 28 2021 01:28 AM
@CliveWatson
I am looking for something :
NAME | VIRTUAL NETWORK | PRIMARY PRIVATE IP | ATTACHED TO | RESOURCE GROUP | LOCATION | SUBSCRIPTION | Subnet | NetWork Security Group |
Jul 28 2021 06:16 AM
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
Jul 29 2021 03:39 AM - edited Jul 29 2021 04:00 AM
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?
Jul 29 2021 09:35 AM
@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
Aug 02 2021 10:14 PM - edited Aug 02 2021 10:15 PM
@CliveWatson
Thanks, My requirement is to get the data as Subscription Names, and How to get the details of all attached to components?
Aug 03 2021 02:02 AM
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
Mar 28 2023 11:47 AM
@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
Mar 29 2023 04:24 AM
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