Force an Azure Resource Graoh to show 0 - KQL - Azure monitor

user_1786298 0
Viewed 52

I want to create a pie chart showing the counts of open (not closed) alerts which is working. However, I want it to default to 0 in the chart when there is no alert for a particular severity

    alertsmanagementresources
|extend Sev = tostring(parse_json(properties.essentials.severity)),
        LastModifiedTime = todatetime(properties.essentials.lastModifiedDateTime)
| where tostring(parse_json(properties.essentials.alertState)) <> 'Closed'
| where resourceGroup =='ai-eazyfuel-eu-prd-rg'
| where Sev =='Sev0'
|where LastModifiedTime >=datetime(2022/07/26)
|summarize count() by Sev

Is this even possible because I understand there are no results to show but you know what end users are like

azure-monitor kql
1 Answers

While it's feasible to write the KQL query:

  1. Azure Resource Graph uses only a limited subset of KQL which makes the query syntax cumbersome.
  2. Azure Resource Graph cannot display 0 size slice.

P.S.
Please note the removal of unnecessary transformations of properties and the use of ISO format for datetime.

resources
| take          1
| mv-expand     severity = range(0,4) to typeof(string)
| project       severity = strcat("Sev", severity)
| join          kind=leftouter 
                (
                        alertsmanagementresources
                        | extend    severity                = tostring(properties.essentials.severity)
                                   ,lastModifiedDateTime    = todatetime(properties.essentials.lastModifiedDateTime)
                        | where     properties.essentials.alertState <> "Closed" 
                                and resourceGroup == "ai-eazyfuel-eu-prd-rg" 
                                and severity == "Sev0" 
                                and lastModifiedDateTime >= datetime("2022-07-26")
                        | summarize count() by severity
                ) on severity
| project       severity, count_ = coalesce(count_, 0)
user_63364790
Related