Select Page

Hosting updates again

This blog (and several others) have been Azure hosted, on a monolithic SLES 15 virtual machine for a good few years.

I seem to have done the rounds on various flavours of Azure hosting. App Service with Project Nami, straight IaaS (like today), App Service with WordPress as a Microsoft provided service.

This last weekend I pulled the database out from the various blog VMs and moved that to a PaaS MySQL environment. It’s cheap, burstable, and seems more than performant for what I need. The other cool feature is VNET isolation – so the database engine is only accessible from my infrastructure running in Azure.

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