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

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

Query for unencrypted storage:

Resources

| where type =~ 'microsoft.storage/storageaccounts'

| where properties.encryption.services.blob.enabled != true

| project name, resourceGroup, location

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

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

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

}

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

Step 2: Identify Missing Services

Query all resource types:

Resources

| distinct type

| order by type asc

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"

}

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

Real Example: SQL Databases

Original section (basic):

Resources

| where type =~ 'microsoft.sql/servers/databases'

| project name, resourceGroup, location

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

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

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

UI/UX Improvements

Conditional Visibility

Only show sections with resources:

{

  "conditionalVisibility": {

    "parameterName": "HasOpenAI",

    "comparison": "isEqualTo",

    "value": "true"

  }

}

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"

}

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')]"

  }

}

Deploy with:

az deployment group create --resource-group Monitoring-RG --template-file workbook.json

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.