A workbook to import into Azure Advisor to show resources that need zone resiliency enabled.

azure-advisor-zone-resiliency-workbook.json

{
  "_disclaimer": "This content is provided \"as is\", without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fitness for a particular purpose, and noninfringement. In no event shall the authors be liable for any claim, damages, or other liability arising from the use of this content. Use of this content is at your own risk. It is your responsibility to ensure compliance with all applicable laws, regulations, and internal policies. This content is licensed under the MIT License.",
  "version": "Notebook/1.0",
  "items": [
    {
      "type": 9,
      "content": {
        "version": "KqlParameterItem/1.0",
        "parameters": [
          {
            "id": "92f91730-9302-475e-8f98-7025deb73b78",
            "version": "KqlParameterItem/1.0",
            "name": "Subscriptions",
            "type": 6,
            "isRequired": true,
            "multiSelect": true,
            "quote": "'",
            "delimiter": ",",
            "typeSettings": {
              "additionalResourceOptions": [
                "value::all"
              ],
              "includeAll": true,
              "selectAllValue": "",
              "showDefault": false
            },
            "defaultValue": "value::all",
            "value": [
              "/subscriptions/6aa5688c-0030-4810-8c5a-5982b5474df9"
            ]
          }
        ],
        "style": "pills",
        "queryType": 0,
        "resourceType": "microsoft.operationalinsights/workspaces"
      },
      "name": "parameters - 7"
    },
    {
      "type": 1,
      "content": {
        "json": "# Enable Zone Redundancy\nThe following resources have Azure Advisor recommendations to enable Zones."
      },
      "name": "text - 1"
    },
    {
      "type": 3,
      "content": {
        "version": "KqlItem/1.0",
        "query": "advisorresources\n| where type == \"microsoft.advisor/recommendations\"\n| extend \n    problem = tostring(properties.shortDescription.problem),\n    solution = tostring(properties.shortDescription.solution),\n    impactedField = tostring(properties.impactedField),\n    region = tostring(properties.extendedProperties.Currentregion),\n    resourceid = tostring(properties.resourceMetadata.resourceId)\n| where subscriptionId in ({Subscriptions:subid})\n| where problem contains \"zone\" or problem contains \"zonal\"\n| project \n    Resource = resourceid,\n    ResourceType = impactedField,\n    AdvisorProblem = problem,\n    AdvisorSolution = solution,\n    TenantId = tenantId,\n    SubscriptionId = subscriptionId,\n    ResourceGroup = resourceGroup\n| order by Resource asc\n",
        "size": 0,
        "showExportToExcel": true,
        "queryType": 1,
        "resourceType": "microsoft.resourcegraph/resources",
        "crossComponentResources": [
          "{Subscriptions}"
        ],
        "gridSettings": {
          "sortBy": [
            {
              "itemKey": "$gen_link_Resource_0",
              "sortOrder": 1
            }
          ]
        },
        "sortBy": [
          {
            "itemKey": "$gen_link_Resource_0",
            "sortOrder": 1
          }
        ]
      },
      "name": "query - 0"
    },
    {
      "type": 1,
      "content": {
        "json": "## Zone Resilient Resource Potential\n\nThe following metrics are from the possible zone deployed and zonal resources compared to resources that are already configured for zones.\n\nThe formula is _(Zone Deployed + Zonal Resources) / (All Possible Zone Deployed and Zonal Resources)_.\n\nThis is a rough metric, and may not include all possible resources (e.g., SaaS resources that there is no control over the zone configuration)."
      },
      "name": "text - 7"
    },
    {
      "type": 3,
      "content": {
        "version": "KqlItem/1.0",
        "query": "Resources\n// based on https://learn.microsoft.com/azure/reliability/availability-zones-service-support\n| where subscriptionId in ({Subscriptions:subid})\n| where type in~ (\n  \"Microsoft.Compute/virtualMachines\",\n  \"Microsoft.Compute/virtualMachineScaleSets\",\n  \"Microsoft.Network/publicIPAddresses\",\n  \"Microsoft.Network/loadBalancers\",\n  \"Microsoft.Network/applicationGateways\",\n  \"Microsoft.Network/natGateways\",\n  \"Microsoft.Network/vpnGateways\",\n  \"Microsoft.Network/networkInterfaces\",\n  \"Microsoft.DBforPostgreSQL/flexibleServers\",\n  \"Microsoft.DBforMySQL/flexibleServers\",\n  \"Microsoft.Sql/managedInstances\",\n  \"Microsoft.DocumentDB/databaseAccounts\",\n  \"Microsoft.ContainerService/managedClusters\",\n  \"Microsoft.App/containerApps\",\n  \"Microsoft.Storage/storageAccounts\",\n  \"Microsoft.Bastion/bastionHosts\",\n  \"Microsoft.Web/serverfarms\" // App Service Plans (zoneRedundant opt-in)\n)\n// based on https://learn.microsoft.com/azure/reliability/regions-list\n| where location in~ \n    (\n    \"australiaeast\", \"brazilsouth\", \"canadacentral\", \"centralindia\", \"centralus\",\n    \"eastasia\", \"eastus\", \"eastus2\", \"francecentral\", \"germanywestcentral\",\n    \"japaneast\", \"japanwest\", \"koreacentral\", \"northeurope\", \"norwayeast\",\n    \"qatarcentral\", \"southafricanorth\", \"southcentralus\", \"southeastasia\",\n    \"swedencentral\", \"switzerlandnorth\", \"uaecentral\", \"uksouth\", \"westeurope\",\n    \"westus2\", \"westus3\"\n    )\n| extend zoneConfigured = case(\n    isnotnull(zones) and array_length(zones) > 0, 1,\n    isnotempty(properties.zoneRedundant) and properties.zoneRedundant == true, 1,\n    isnotempty(properties.statusOfSecondary) and isnotempty(properties.secondaryLocation), 1,\n    isnotempty(properties.replicationLocation), 1,\n    0\n)\n| summarize \n    ResourceCount = count(),\n    ZoneConfiguredResources = sum(zoneConfigured)\n    by Region = location\n| extend ZoneConfiguredPercentage = toreal(ZoneConfiguredResources) / toreal(ResourceCount) * 100, NonZoneConfiguredResources = (ResourceCount - ZoneConfiguredResources)\n| order by Region asc\n| project \n    [\"Azure Region\"] = Region,\n    [\"Configured for Zones\"] = ZoneConfiguredResources,\n    [\"Not Configured for Zones\"] = NonZoneConfiguredResources,\n    [\"Total Zone-Possible Resources\"] = ResourceCount,\n    [\"Zone Configured Percentage\"] = strcat(tostring(round(ZoneConfiguredPercentage, 2)), \"%\")",
        "size": 1,
        "queryType": 1,
        "resourceType": "microsoft.resourcegraph/resources",
        "crossComponentResources": [
          "{Subscriptions}"
        ]
      },
      "name": "query - 8"
    },
    {
      "type": 1,
      "content": {
        "json": "# Enable Azure Service Health\nThe following subscriptions need to have at least one [Azure Service Health](https://learn.microsoft.com/azure/service-health/overview) alert configured."
      },
      "name": "text - 2"
    },
    {
      "type": 3,
      "content": {
        "version": "KqlItem/1.0",
        "query": "advisorresources\n| where type == \"microsoft.advisor/recommendations\"\n| extend \n    problem = tostring(properties.shortDescription.problem),\n    resourceid = tostring(properties.resourceMetadata.resourceId)\n| where problem has \"service health\"\n| where subscriptionId in ({Subscriptions:subid})\n| project \n    Resource = resourceid,\n    TenantId = tenantId,\n    SubscriptionId = subscriptionId\n| order by Resource asc\n",
        "size": 0,
        "showExportToExcel": true,
        "queryType": 1,
        "resourceType": "microsoft.resourcegraph/resources",
        "crossComponentResources": [
          "{Subscriptions}"
        ],
        "gridSettings": {
          "sortBy": [
            {
              "itemKey": "$gen_link_Resource_0",
              "sortOrder": 1
            }
          ]
        },
        "sortBy": [
          {
            "itemKey": "$gen_link_Resource_0",
            "sortOrder": 1
          }
        ]
      },
      "name": "query - 0 - Copy"
    },
    {
      "type": 1,
      "content": {
        "json": "## Service Health Alert Configuration Summary by Subscription\n\nThe following shows subscriptions and their current configuration state, as well as how many notifications have been sent if configured."
      },
      "name": "text - 8"
    },
    {
      "type": 3,
      "content": {
        "version": "KqlItem/1.0",
        "query": "resources\n| distinct subscriptionId\n| join kind=leftouter(\n    resources\n    | where type =~ \"microsoft.insights/activitylogalerts\"\n    | mv-expand condition1 = properties.condition.allOf\n    | mv-expand condition2 = condition1.anyOf\n    | extend alertEnabled = tostring(properties.enabled)\n    | summarize set_condition1=make_set(condition1.equals), set_condition2=make_set(condition2.equals) by id, name, type, tenantId, resourceGroup, subscriptionId, alertEnabled\n    | where set_has_element(set_condition1, \"ServiceHealth\")\n    | extend category = \"ServiceHealth\"\n    | extend all = iff(set_has_element(set_condition1, \"ServiceHealth\") and array_length(set_condition2) == 0, true, false)\n    // Service issue\n    | extend incident = iff(all, true, iff(set_has_element(set_condition1, \"Incident\"), true, set_has_element(set_condition2, \"Incident\")))\n    // Planned maintenance\n    | extend maintenance = iff(all, true, iff(set_has_element(set_condition1, \"Maintenance\"), true, set_has_element(set_condition2, \"Maintenance\")))\n    // Health advisories\n    | extend informational = iff(all, true, iff(set_has_element(set_condition1, \"Informational\") or set_has_element(set_condition1, \"ActionRequired\"), true, set_has_element(set_condition2, \"Informational\") or set_has_element(set_condition2, \"ActionRequired\")))\n    // Security advisory\n    | extend security = iff(all, true, iff(set_has_element(set_condition1, \"Security\"), true, set_has_element(set_condition2, \"Security\")))\n    | project id,\n        name,\n        subscriptionId,\n        category,\n        tostring(alertEnabled),\n        tostring(incident),\n        tostring(maintenance),\n        tostring(informational),\n        tostring(security)\n    | summarize count_alertEnabled=countif(alertEnabled == \"true\"),\n        count_incident=countif(incident == \"True\"),\n        count_maintenance=countif(maintenance == \"True\"),\n        count_informational=countif(informational == \"True\"),\n        count_security=countif(security == \"True\")\n        by subscriptionId \n    )\n    on subscriptionId\n| where subscriptionId in ({Subscriptions:subid})\n| project subscriptionId,\n    alertEnabled=iff(isnotnull(count_alertEnabled),\n    count_alertEnabled, 0),\n    incident=iff(isnotnull(count_incident), count_incident, 0),\n    security=iff(isnotnull(count_security), count_security, 0),\n    maintenance=iff(isnotnull(count_maintenance), count_maintenance, 0),\n    informational=iff(isnotnull(count_informational), count_informational, 0)\n| order by incident, maintenance, informational, security desc\n",
        "size": 0,
        "showExportToExcel": true,
        "queryType": 1,
        "resourceType": "microsoft.resourcegraph/resources",
        "crossComponentResources": [
          "value::all"
        ],
        "gridSettings": {
          "formatters": [
            {
              "columnMatch": "subscriptionId",
              "formatter": 13,
              "formatOptions": {
                "linkTarget": "Resource",
                "showIcon": true
              }
            },
            {
              "columnMatch": "alertEnabled",
              "formatter": 18,
              "formatOptions": {
                "thresholdsOptions": "icons",
                "thresholdsGrid": [
                  {
                    "operator": "==",
                    "thresholdValue": "0",
                    "representation": "2",
                    "text": "{0}{1}"
                  },
                  {
                    "operator": ">=",
                    "thresholdValue": "1",
                    "representation": "success",
                    "text": "{0}{1}"
                  },
                  {
                    "operator": "Default",
                    "thresholdValue": null,
                    "representation": "unknown",
                    "text": "{0}{1}"
                  }
                ]
              }
            },
            {
              "columnMatch": "incident",
              "formatter": 18,
              "formatOptions": {
                "thresholdsOptions": "icons",
                "thresholdsGrid": [
                  {
                    "operator": "==",
                    "thresholdValue": "0",
                    "representation": "2",
                    "text": "{0}{1}"
                  },
                  {
                    "operator": ">=",
                    "thresholdValue": "1",
                    "representation": "success",
                    "text": "{0}{1}"
                  },
                  {
                    "operator": "Default",
                    "thresholdValue": null,
                    "representation": "unknown",
                    "text": "{0}{1}"
                  }
                ]
              }
            },
            {
              "columnMatch": "maintenance",
              "formatter": 18,
              "formatOptions": {
                "thresholdsOptions": "icons",
                "thresholdsGrid": [
                  {
                    "operator": "==",
                    "thresholdValue": "0",
                    "representation": "2",
                    "text": "{0}{1}"
                  },
                  {
                    "operator": ">=",
                    "thresholdValue": "1",
                    "representation": "success",
                    "text": "{0}{1}"
                  },
                  {
                    "operator": "Default",
                    "thresholdValue": null,
                    "representation": "unknown",
                    "text": "{0}{1}"
                  }
                ]
              }
            },
            {
              "columnMatch": "informational",
              "formatter": 18,
              "formatOptions": {
                "thresholdsOptions": "icons",
                "thresholdsGrid": [
                  {
                    "operator": "==",
                    "thresholdValue": "0",
                    "representation": "2",
                    "text": "{0}{1}"
                  },
                  {
                    "operator": ">=",
                    "thresholdValue": "1",
                    "representation": "success",
                    "text": "{0}{1}"
                  },
                  {
                    "operator": "Default",
                    "thresholdValue": null,
                    "representation": "unknown",
                    "text": "{0}{1}"
                  }
                ]
              }
            },
            {
              "columnMatch": "security",
              "formatter": 18,
              "formatOptions": {
                "thresholdsOptions": "icons",
                "thresholdsGrid": [
                  {
                    "operator": "==",
                    "thresholdValue": "0",
                    "representation": "2",
                    "text": "{0}{1}"
                  },
                  {
                    "operator": ">=",
                    "thresholdValue": "1",
                    "representation": "success",
                    "text": "{0}{1}"
                  },
                  {
                    "operator": "Default",
                    "thresholdValue": null,
                    "representation": "unknown",
                    "text": "{0}{1}"
                  }
                ]
              }
            },
            {
              "columnMatch": "Subscription",
              "formatter": 13,
              "formatOptions": {
                "linkTarget": "Resource",
                "showIcon": true
              }
            }
          ],
          "rowLimit": 10000,
          "labelSettings": [
            {
              "columnId": "subscriptionId",
              "label": "Subscription"
            },
            {
              "columnId": "alertEnabled",
              "label": "Alert Enabled"
            },
            {
              "columnId": "incident",
              "label": "Service Issues"
            },
            {
              "columnId": "security",
              "label": "Security Advisories"
            },
            {
              "columnId": "maintenance",
              "label": "Planned Maintenance"
            },
            {
              "columnId": "informational",
              "label": "Health Advisories"
            }
          ]
        }
      },
      "name": "Aggregate Summary by Subscription"
    }
  ],
  "fallbackResourceIds": [
    "Azure Advisor"
  ],
  "$schema": "https://github.com/Microsoft/Application-Insights-Workbooks/blob/master/schema/workbook.json"
}

To use, navigate to Azure Advisor Workbooks in the Azure Portal, create a new Workbook, open the advanced editor (shows as </> in the toolbar), and paste the raw content of this gist.