by ezs | Jun 1, 2023 | evilzenscientist
Another one, again demonstrating the use of extend and mv-expand to pull out the multi-valued array.
resources
| join kind=leftouter (ResourceContainers | where type==’microsoft.resources/subscriptions’ | project SubName=name, subscriptionId) on subscriptionId
| where type == “microsoft.network/virtualnetworks”
| extend vnetCount =array_length(properties.addressSpace.addressPrefixes)
| mv-expand vnet = properties.addressSpace.addressPrefixes
| project SubName, resourceGroup, name, vnetCount, vnet
| order by tostring(vnet) asc
by ezs | Mar 1, 2021 | evilzenscientist
“The get everything about virtual machines” script.
This brings back pretty much everything – sub second queries. Far, far easier than the old methods using PowerShell.
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 subscriptionId, resourceGroup, vmId = id, vmName = name, vmSize=tostring(properties.hardwareProfile.vmSize), nicId = tostring(nic.id),location, tags.itowner, tags.businessowner, tags.application, tags.costcenter, tags.supportgroup, tags.[‘project’], powerstate=properties.extended.instanceView.powerState.displayStatus, os=properties.storageProfile.osDisk.osType, sku=properties.storageProfile.imageReference.sku
| join kind=leftouter (ResourceContainers | where type==’microsoft.resources/subscriptions’ | project SubName=name, 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, privIP = tostring(ipconfig.properties.privateIPAddress)) on nicId
| project-away subscriptionId, subscriptionId1, vmId, nicId, nicId1
Recent Comments