DEV Community

David
David

Posted on • Originally published at azure-noob.com

Modernizing Azure Workbooks - Taking Billy York's Inventory from 50 to 200+ Services

#azure #monitoring #workbooks #kql

The Problem with Azure Inventory

Azure Portal: Shows resources one subscription at a time

Need: See ALL resources across 44 subscriptions

Billy York's solution: Azure Monitor Workbook with Resource Graph queries

Problem: Only covers ~50 Azure services. Azure has 200+.

What Billy York Built (And Why It's Great)

Original workbook:

  • Queries Azure Resource Graph
  • Shows VMs, storage, networking
  • Organized by resource type
  • Open source on GitHub

Why it's excellent:

  • Actually works (many don't)
  • Clean UI
  • Copy-paste KQL queries
  • Free

Why it needs enhancement:

  • Missing 150+ services
  • No security hygiene checks
  • No cost context
  • Manual updates for new Azure services

What We Added

150+ Additional Services

Original: VMs, Storage, VNets, NSGs, Load Balancers

Added:

  • Azure AI (OpenAI, Cognitive Services, ML)
  • Databases (SQL, Cosmos, PostgreSQL, MySQL)
  • Integration (Logic Apps, Service Bus, Event Grid)
  • Security (Key Vault, Defender, Sentinel)
  • Monitoring (Application Insights, Log Analytics)
  • Arc (Hybrid servers, Kubernetes)

Total: 200+ service types

Security Hygiene Checks

Query for public-facing resources:

Resources
| where type =~ 'microsoft.compute/virtualmachines'
| extend hasPublicIP = isnotnull(properties.networkProfile.networkInterfaces[0].properties.ipConfigurations[0].properties.publicIPAddress)
| where hasPublicIP == true
| project name, resourceGroup, location
Enter fullscreen mode Exit fullscreen mode

Query for unencrypted storage:

Resources
| where type =~ 'microsoft.storage/storageaccounts'
| where properties.encryption.services.blob.enabled != true
| project name, resourceGroup, location
Enter fullscreen mode Exit fullscreen mode

Query for expired certificates:

Resources
| where type =~ 'microsoft.keyvault/vaults/certificates'
| extend expiry = todatetime(properties.attributes.exp)
| where expiry < now() + 30d
| project name, expiry, resourceGroup
Enter fullscreen mode Exit fullscreen mode

Cost Context

Added cost data to resource views:

Resources
| where type =~ 'microsoft.compute/virtualmachines'
| extend vmSize = tostring(properties.hardwareProfile.vmSize)
| join kind=leftouter (
    CostManagementExports
    | summarize MonthlyCost = sum(Cost) by ResourceId
) on $left.id == $right.ResourceId
| project name, vmSize, MonthlyCost, resourceGroup
Enter fullscreen mode Exit fullscreen mode

Global Filters

Original: Filter per-section

Enhanced: Global subscription/resource group filter applies to ALL sections

Implementation:

{
  "type": "dropdown",
  "name": "Subscriptions",
  "query": "ResourceContainers | where type == 'microsoft.resources/subscriptions' | project name, subscriptionId",
  "isMultiSelect": true
}
Enter fullscreen mode Exit fullscreen mode

Result: Select subscriptions once, affects entire workbook

The Enhancement Process

Step 1: Clone Billy York's Repo 

git clone https://github.com/scautomation/Azure-Inventory-Workbook
Enter fullscreen mode Exit fullscreen mode

Step 2: Identify Missing Services

Query all resource types:

Resources
| distinct type
| order by type asc
Enter fullscreen mode Exit fullscreen mode

Compare to workbook: Find gaps

Step 3: Add Service Sections

Template for new section:

{
  "type": "section",
  "title": "Azure OpenAI",
  "query": "Resources | where type =~ 'microsoft.cognitiveservices/accounts' | where kind == 'OpenAI' | project name, sku.name, location, resourceGroup"
}
Enter fullscreen mode Exit fullscreen mode

Step 4: Add Security Checks

For each service, add:

  • Public exposure check
  • Encryption status
  • Certificate expiration
  • Compliance flags

Step 5: Add Cost Columns

Join cost data to resource queries:

Resources
| where type =~ '[service-type]'
| join kind=leftouter (
    CostManagementExports
    | where TimeGenerated > ago(30d)
    | summarize MonthlyCost = sum(Cost) by ResourceId
) on $left.id == $right.ResourceId
Enter fullscreen mode Exit fullscreen mode

Real Example: SQL Databases

Original section (basic):

Resources
| where type =~ 'microsoft.sql/servers/databases'
| project name, resourceGroup, location
Enter fullscreen mode Exit fullscreen mode

Enhanced section (with security + cost):

Resources
| where type =~ 'microsoft.sql/servers/databases'
| extend 
    serverName = split(id, '/')[8],
    tier = properties.sku.tier,
    encrypted = properties.transparentDataEncryption.status,
    publicAccess = properties.publicNetworkAccess
| join kind=leftouter (
    CostManagementExports
    | where TimeGenerated > ago(30d)
    | summarize MonthlyCost = sum(Cost) by ResourceId
) on $left.id == $right.ResourceId
| project 
    Database = name, 
    Server = serverName,
    Tier = tier,
    Encrypted = encrypted,
    PublicAccess = publicAccess,
    MonthlyCost = round(MonthlyCost, 2),
    ResourceGroup = resourceGroup,
    Location = location
| order by MonthlyCost desc
Enter fullscreen mode Exit fullscreen mode

Result:

  • Shows SQL databases
  • Security status (encryption, public access)
  • Monthly cost
  • Sorted by expense

Performance Optimization

Problem: Workbook Timeout

Original: Single massive query for all subscriptions

Enhanced: Paginated queries with limits

Resources
| where type =~ 'microsoft.compute/virtualmachines'
| take 1000  // Pagination
Enter fullscreen mode Exit fullscreen mode

Problem: Slow Joins

Original: Join every resource to cost data

Enhanced: Pre-aggregate cost data

// Pre-aggregate costs
let costs = CostManagementExports
| where TimeGenerated > ago(30d)
| summarize MonthlyCost = sum(Cost) by ResourceId;

// Then join
Resources
| where type =~ 'microsoft.compute/virtualmachines'
| join kind=leftouter costs on $left.id == $right.ResourceId
Enter fullscreen mode Exit fullscreen mode

UI/UX Improvements

Conditional Visibility

Only show sections with resources:

{
  "conditionalVisibility": {
    "parameterName": "HasOpenAI",
    "comparison": "isEqualTo",
    "value": "true"
  }
}
Enter fullscreen mode Exit fullscreen mode

Color Coding

Red: Security issues (public access, unencrypted)
Yellow: Warnings (expiring certs, high cost)
Green: Compliant resources

Export Functionality

Added: Export to CSV for each section

{
  "type": "button",
  "action": "export",
  "format": "csv"
}
Enter fullscreen mode Exit fullscreen mode

Deployment

Option 1: Import JSON

  1. Go to Azure Monitor → Workbooks
  2. Click "New"
  3. Click "Advanced Editor"
  4. Paste enhanced JSON
  5. Save

Option 2: ARM Template 

{
  "type": "Microsoft.Insights/workbooks",
  "apiVersion": "2021-03-08",
  "name": "[guid('azure-inventory-enhanced')]",
  "location": "[resourceGroup().location]",
  "properties": {
    "displayName": "Azure Inventory Enhanced",
    "serializedData": "[parameters('workbookContent')]"
  }
}
Enter fullscreen mode Exit fullscreen mode

Deploy with:

az deployment group create --resource-group Monitoring-RG --template-file workbook.json
Enter fullscreen mode Exit fullscreen mode

Real Results

Before (Billy York original):

  • 50 service types
  • No security context
  • No cost data
  • Good for small environments

After (enhanced):

  • 200+ service types
  • Security hygiene checks
  • Cost context
  • Production-ready for enterprise

Time to generate inventory:

  • Before: Multiple tools, 2 hours
  • After: Single workbook, 30 seconds

Community Contribution

Billy York's original: https://github.com/scautomation/Azure-Inventory-Workbook

Our enhancements: Fork + pull request with:

  • 150+ additional services
  • Security checks
  • Cost integration
  • Performance fixes

Attribution: Billy York deserves credit for building the foundation. We just scaled it.

Full Enhanced Workbook

Complete workbook JSON, deployment templates, and contribution guide:

👉 Enhanced Azure Inventory Workbook

Also on GitHub: [github.com/dswann101164/azure-inventory-workbook-enhanced]

Using Azure Workbooks? Start with community workbooks like Billy York's, then enhance for your needs. Don't build from scratch—extend what works.

Top comments (0)