Select Page

More Kusto

You can tell where I’ve been working by the Kusto queries against Azure Resource Graph that I leave behind.

All Azure VNets and subnets, with subnet details:

// get all Azure VNETS and SUBNETS with associated subnets


| join kind=leftouter (ResourceContainers | where type==’microsoft.resources/subscriptions’ | project SubName=name, subscriptionId) on subscriptionId

| where type == “”

| mv-expand subs=properties.subnets

| extend subnetname =

| extend subnetprefix=

| extend vnetprefix = substring(tostring(properties.addressSpace.addressPrefixes),2,strlen(tostring(properties.addressSpace.addressPrefixes))-4)

| project SubName, resourceGroup, name, vnetprefix, subnetname, subnetprefix

| sort by SubName, resourceGroup asc, name

This one for pulling back network security groups and metadata (this was originally at Thomas Balkeståhl’s blog) – tidied up:


| where type =~ “”

| join kind=leftouter (ResourceContainers | where type==’microsoft.resources/subscriptions’ | project SubName=name, subscriptionId) on subscriptionId

| mv-expand rules=properties.securityRules

| extend direction = tostring(

| extend priority = toint(

| extend description =

| extend destprefix =

| extend destport =

| extend sourceprefix =

| extend sourceport =

| extend subnet_name = split((split(tostring(properties.subnets), ‘/’))[10], ‘”‘)[0]

| project SubName, resourceGroup, subnet_name, name, direction, priority, destprefix, destport, sourceprefix, sourceport, description

| sort by SubName, resourceGroup asc, name, direction asc, priority asc

More Kusto – The Everything Script

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


| where type =~ ‘microsoft.compute/virtualmachines’

| extend nics=array_length(properties.networkProfile.networkInterfaces)

| mv-expand nic=properties.networkProfile.networkInterfaces

| where nics == 1 or =~ ‘true’ or isempty(nic)

| project subscriptionId, resourceGroup, vmId = id, vmName = name, vmSize=tostring(properties.hardwareProfile.vmSize), nicId = tostring(,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 (


  | where type =~ ‘’

  | extend ipConfigsCount=array_length(properties.ipConfigurations)

  | mv-expand ipconfig=properties.ipConfigurations

  | where ipConfigsCount == 1 or =~ ‘true’

  | project nicId = id, privIP = tostring( on nicId

| project-away subscriptionId, subscriptionId1, vmId, nicId, nicId1

Hands off keyboards! Automation and metadata validation


Tag taxonomy cleanup.

Another great example of “hands off keyboards” and needing to deliver via automation. Avoid errors, enforce validation of metadata.


Azure Resource Graph explorer – find the scope and scale of the problem. I’ll add the usual gripe around tags being case sensitive in some places (API, PowerShell) and not in others (Azure Portal!).


| where tags.businesowner != ”

| project name, subscriptionId, resourceGroup, tags.businessowner, tags.businesowner

Cleaning up Azure storage – an ongoing tale

Back to “Azure Grim Reaper” – flagging and deleting unused storage in Azure.

There is no “nice” way to do this – the Azure Metrics help a lot.

Here’s some code that enumerates a list of subscriptions and pulls out storage accounts, transactions over a period of time, count of storage containers, tables and queues – and reads tags.

It’s generic enough for use in most places.

# setup
#import-module az
# creds
# set up array of subs

$subs= ‘<subscription ID>’, ‘<subscription ID>’

# today
$nowdate = Get-Date

#initialise output

$stgdata =@()

Write-Host “Enumerating” $subs.count “subscription(s)”

# loop through the subscription(s)

foreach ($subscription in $subs) {
# in subscription, next read the storage accounts
#set context
write-host “Switching to subscription” $subscription
set-azcontext -subscription $subscription |out-null
write-host “enumerating storage accounts”
$stgacclist = get-azstorageaccount
write-host “Total of” $stgacclist.count “storage accounts in subscription” $subscription

# loop through each storage account

foreach ($stgacc in $stgacclist) {
# in storage account, get all storage containers, tables and queues
# stgacc entity has StorageAccountNme and ResourceGroupName and tags

set-azcurrentstorageaccount -Name $stgacc.StorageAccountName -ResourceGroupName $stgacc.ResourceGroupName

write-host “enumerating storage account ” $stgacc.StorageAccountName ” in resource group” $stgacc.ResourceGroupName
write-host “storage containers”
$stgcontainers = get-azstoragecontainer
write-host “table service”
$tblservice = get-azstoragetable
write-host “queue service”
$qservice = get-azstoragequeue

#get transactions

$transactions = Get-AzMetric -ResourceId $ -TimeGrain 0.1:00:00 -starttime ((get-date).AddDays(-60)) -endtime (get-date) -MetricNames “Transactions” -WarningAction SilentlyContinue
write-host “60 days of transactions” $(($transactions.Data | Measure-Object -Property total -Sum).sum)

#reset usage array
$lastused = @()

#loop through each blob service container
foreach ($container in $stgcontainers) {
# in blob storage container
write-host “collecting blob storage data”

$lastused += [PSCustomObject]@{
Subscription = $subscription
ResourceGroup = $stgacc.ResourceGroupName
StorageAccount = $stgacc.StorageAccountName
StorageContainer = $
LastModified = $container.lastmodified.Date
Age = ($nowdate – $container.LastModified.Date).Days


#let’s find any unmanaged disks in the container
$allblobs = get-azstorageblob -container $
$vhdblobs = $allblobs | Where-Object {$_.BlobType -eq ‘PageBlob’ -and $_.Name.EndsWith(‘.vhd’)}


write-host “Total of ” $stgcontainers.count “containers. Minimum age ” ($lastused.Age |Measure -Minimum).minimum “maximum age” ($lastused.Age |Measure -Maximum).maximum

#append to the array

$stgdata += [PSCustomObject]@{
Subscription = $subscription
ResourceGroup = $stgacc.ResourceGroupName
StorageAccount = $stgacc.StorageAccountName
Transactions = $(($transactions.Data | Measure-Object -Property total -Sum).sum)
ContainerCount = $stgcontainers.Count
TableCount = $tblservice.Count
QueueCount = $qservice.Count
AgingMin = ($lastused.Age |Measure -Minimum).minimum
AgingMax = ($lastused.Age |Measure -Maximum).maximum
vhd = $vhdblobs.count
blobs = $allblobs.count

#tag metadata
itowner = $stgacc.tags.itowner
businessowner = $stgacc.tags.businessowner
application =$stgacc.tags.application
costcenter = $stgacc.tags.costcenter

#output to csv

$stgdata | export-csv <somelocation>\storageacc.csv -force -NoTypeInformation

PowerBI, DAX and the Azure EA billing API

More fun this morning with grappling DAX to pull out the data I need.

We write multiple tags to Azure resources – from the resource group down to anything that takes them. Makes showback, accountability, support so much cleaner.

The tags are returned, in a non deterministic order, via the billing API – and look like this (data cleaned up), a list of tag/values. JSON-like ish kinda.

application“: “Shared SQL“,”project“: “Shared Database Engineering“,”costcenter“: “1234“,”itowner“: “Buckley, Martin“,”supportgroup“: “Database Engineering“,”businessowner“: “Buckley, Martin

I was looking at some DAX to pull this apart; PowerBI has a native “extract text between delimiters” function. It’s not going to build a tree of arbitary values; but I can now cleanly extract the tags I need to build this datamodel.

After I get this working, it might be time to pull all of the discrete DAX steps together to make it more efficient against a vast dataset.

Fun times on a Saturday.

Follow up to this. Extracting multiple pieces of insight from the tags – that drive KPIs for service improvement:

– resource groups with no costcenter tag (this is the cardinal sin – no costcenter and showback isn’t there, getting to health on this is easy)

– resource groups where itowner or businessowner don’t meet business rules (ICs can’t be owners)

– resource groups where the supportgroup is not valid (it doesn’t match a ServiceNow Assigment group)

Most of all – I can find everything tagged with “placeholder” or “set by policy” and sweep that broom through the tagging.

All in all – a good few hours of work.