Part 5: Building Observability Dashboards with KQL

From Queries to Dashboards

After mastering KQL queries, I learned that the real power comes from building dashboards that provide continuous visibility into system health. In this part, I'll share my experience creating production observability dashboards using Azure Workbooks and visualization techniques.

Understanding Azure Workbooks

Azure Workbooks transform KQL queries into interactive, visual dashboards. Think of them as parameterized, shareable notebooks combining queries, text, and visualizations.

Why I Use Workbooks:

1. Interactive parameters - Users can filter without editing queries

2. Multiple visualizations - Charts, grids, tiles, and more

3. Reusability - Share across teams and subscriptions

4. Real-time data - Queries run on-demand

5. Version control - Save and track changes

Creating Your First Workbook

Let me walk you through building a workbook from scratch.

Step 1: Access Workbooks

Navigate to your Log Analytics workspace → Workbooks → New

Step 2: Add Parameters

Parameters make dashboards interactive. Here's how I structure them:

// Time range parameter (built-in)
{TimeRange}

// Custom dropdown parameter for resource group
AzureActivity
| where TimeGenerated {TimeRange}
| distinct ResourceGroup
| order by ResourceGroup asc

Parameter configuration:

• Name: ResourceGroup

• Display name: "Resource Group"

• Parameter type: Drop down

• Allow multiple selections: Yes

• Default value: All

Step 3: Building Query Components

Each visualization starts with a query. Here's my standard structure:

// Use parameters in queries
AzureActivity
| where TimeGenerated {TimeRange}
| where ResourceGroup in ({ResourceGroup}) or '*' in ({ResourceGroup})
| summarize count() by bin(TimeGenerated, 1h), Level
| order by TimeGenerated asc

Visualization Types I Use

1. Time Charts - Trending Data

Perfect for showing metrics over time.

// CPU usage trend
Perf
| where TimeGenerated {TimeRange}
| where CounterName == "% Processor Time"
| where Computer in ({Computer}) or '*' in ({Computer})
| summarize AvgCpu = avg(CounterValue) by Computer, bin(TimeGenerated, 5m)
| render timechart

Visualization settings:

• Chart type: Line chart

• Y-axis: AvgCpu

• X-axis: TimeGenerated

• Series: Computer

2. Bar Charts - Comparisons

Great for comparing values across categories.

// Top error-prone operations
AzureActivity
| where TimeGenerated {TimeRange}
| where ActivityStatusValue in ("Failed", "Error")
| summarize ErrorCount = count() by OperationNameValue
| top 10 by ErrorCount
| render barchart

3. Pie Charts - Distribution

Show proportional data clearly.

// Request status distribution
AppRequests
| where TimeGenerated {TimeRange}
| summarize count() by ResultCode
| render piechart

4. Tables/Grids - Detailed Data

Essential for detailed information and drill-down.

// Active servers with metrics
let timeRange = {TimeRange};
Heartbeat
| where TimeGenerated > ago(5m)
| distinct Computer
| join kind=leftouter (
    Perf
    | where TimeGenerated > ago(15m)
    | where CounterName == "% Processor Time"
    | summarize AvgCpu = avg(CounterValue) by Computer
) on Computer
| join kind=leftouter (
    Perf
    | where TimeGenerated > ago(15m)
| where CounterName == "% Used Memory"
    | summarize AvgMemory = avg(CounterValue) by Computer
) on Computer
| project 
    Computer, 
    ['CPU %'] = round(AvgCpu, 1),
    ['Memory %'] = round(AvgMemory, 1),
    ['Health'] = case(
        AvgCpu > 90 or AvgMemory > 90, "🔴 Critical",
        AvgCpu > 80 or AvgMemory > 80, "🟡 Warning",
        "🟢 Healthy"
    )
| order by ['CPU %'] desc

Grid settings:

• Enable column formatting

• Add conditional formatting for health status

• Make columns sortable

5. Tiles - Key Metrics

Display important numbers prominently.

// Current error rate
AppRequests
| where TimeGenerated > ago(5m)
| summarize 
    Total = count(),
    Errors = countif(Success == false)
| extend ErrorRate = round(100.0 * Errors / Total, 2)
| project ErrorRate

Tile settings:

• Title: "Error Rate (Last 5 min)"

• Subtitle: "%"

• Use color thresholds:

  • Green: < 1

  • Yellow: 1-5

  • Red: > 5

Production Dashboard: Application Health

Let me share a comprehensive application health dashboard I built:

Dashboard Structure:

1. Parameters Section

   • Time range

   • Application name

   • Environment

2. Overview Tiles

   • Request rate

   • Error rate

   • Average latency

   • Active instances

3. Charts Section

   • Request volume over time

   • Response time percentiles

   • Error rate trend

   • Dependency health

4. Detail Tables

   • Recent errors

   • Slowest operations

   • Top dependencies

Implementation:

1. Parameters:

// Time range parameter
{TimeRange:label}

// Application parameter
AppRequests
| where TimeGenerated {TimeRange}
| distinct AppRoleName
| order by AppRoleName asc

// Environment tag parameter
AppRequests  
| where TimeGenerated {TimeRange}
| extend Environment = tostring(parse_json(Properties).Environment)
| distinct Environment

2. Request Rate Tile:

let currentRate = AppRequests
| where TimeGenerated > ago(5m)
| where AppRoleName in ({AppRoleName}) or '*' in ({AppRoleName})
| summarize Rate = count() / 5.0;
let previousRate = AppRequests
| where TimeGenerated between (ago(10m) .. ago(5m))
| where AppRoleName in ({AppRoleName}) or '*' in ({AppRoleName})
| summarize Rate = count() / 5.0;
currentRate
| extend Previous = toscalar(previousRate)
| extend Change = round(100.0 * (Rate - Previous) / Previous, 1)
| project 
    ['Requests/min'] = round(Rate, 1),
    ['Change %'] = Change

3. Error Rate Tile:

AppRequests
| where TimeGenerated > ago(5m)
| where AppRoleName in ({AppRoleName}) or '*' in ({AppRoleName})
| summarize 
    Total = count(),
    Errors = countif(Success == false)
| extend ErrorRate = round(100.0 * Errors / Total, 3)
| project ErrorRate

4. Response Time Chart:

AppRequests
| where TimeGenerated {TimeRange}
| where AppRoleName in ({AppRoleName}) or '*' in ({AppRoleName})
| summarize 
    P50 = percentile(DurationMs, 50),
    P95 = percentile(DurationMs, 95),
    P99 = percentile(DurationMs, 99)
    by bin(TimeGenerated, 5m)
| render timechart

5. Top Errors Table:

AppExceptions
| where TimeGenerated {TimeRange}
| where AppRoleName in ({AppRoleName}) or '*' in ({AppRoleName})
| summarize 
    Count = count(),
    SampleMessage = any(OuterMessage),
    FirstSeen = min(TimeGenerated),
    LastSeen = max(TimeGenerated)
    by ProblemId, Type
| order by Count desc
| take 20

6. Dependency Health:

AppDependencies
| where TimeGenerated {TimeRange}
| where AppRoleName in ({AppRoleName}) or '*' in ({AppRoleName})
| summarize 
    Calls = count(),
    Failures = countif(Success == false),
    AvgDuration = avg(DurationMs),
    P95Duration = percentile(DurationMs, 95)
    by Target, Type
| extend 
    SuccessRate = round(100.0 * (Calls - Failures) / Calls, 2),
    Status = case(
        SuccessRate < 95, "🔴 Unhealthy",
        SuccessRate < 99, "🟡 Degraded",
        "🟢 Healthy"
    )
| project Target, Type, Calls, SuccessRate, AvgDuration, P95Duration, Status
| order by SuccessRate asc

Infrastructure Monitoring Dashboard

Here's my standard infrastructure dashboard:

Dashboard Components:

1. Server Health Overview Tile:

Heartbeat
| where TimeGenerated > ago(5m)
| summarize LastSeen = max(TimeGenerated) by Computer
| extend Status = case(
    datetime_diff('minute', now(), LastSeen) < 3, "Online",
    datetime_diff('minute', now(), LastSeen) < 10, "Stale",
    "Offline"
)
| summarize 
    Total = count(),
    Online = countif(Status == "Online"),
    Stale = countif(Status == "Stale"),
    Offline = countif(Status == "Offline")
| extend HealthyPercent = round(100.0 * Online / Total, 1)
| project HealthyPercent

2. CPU Usage Heat Map:

Perf
| where TimeGenerated {TimeRange}
| where CounterName == "% Processor Time"
| summarize AvgCpu = avg(CounterValue) by Computer, bin(TimeGenerated, 10m)
| render columnchart with (kind=stacked)

3. Memory Pressure Table:

Perf
| where TimeGenerated > ago(15m)
| where CounterName in ("% Used Memory", "Available MBytes")
| extend MetricType = CounterName
| summarize AvgValue = avg(CounterValue) by Computer, MetricType
| evaluate pivot(MetricType, avg(AvgValue))
| extend 
    MemoryUsedPercent = round(['% Used Memory'], 1),
    AvailableMB = round(['Available MBytes'], 0),
    Status = case(
        ['% Used Memory'] > 90, "🔴 Critical",
        ['% Used Memory'] > 80, "🟡 Warning",
        "🟢 Normal"
    )
| project Computer, MemoryUsedPercent, AvailableMB, Status
| order by MemoryUsedPercent desc

4. Disk Space Monitoring:

Perf
| where TimeGenerated > ago(15m)
| where ObjectName == "LogicalDisk" and CounterName == "% Free Space"
| where InstanceName !in ("_Total", "HarddiskVolume1")
| summarize FreeSpace = avg(CounterValue) by Computer, InstanceName
| extend 
    UsedPercent = round(100 - FreeSpace, 1),
    Status = case(
        FreeSpace < 10, "🔴 Critical",
        FreeSpace < 20, "🟡 Warning",
        "🟢 OK"
    )
| project Computer, Drive = InstanceName, UsedPercent, Status
| order by UsedPercent desc

5. Network Traffic Chart:

Perf
| where TimeGenerated {TimeRange}
| where CounterName in ("Bytes Sent/sec", "Bytes Received/sec")
| extend MBps = CounterValue / 1024 / 1024
| summarize AvgMBps = avg(MBps) by Computer, CounterName, bin(TimeGenerated, 5m)
| render timechart

Kubernetes Monitoring Dashboard

For AKS clusters, I use this dashboard structure:

Cluster Health Overview:

// Pod status summary
KubePodInventory
| where TimeGenerated > ago(5m)
| summarize arg_max(TimeGenerated, *) by Name, Namespace
| summarize 
    Total = count(),
    Running = countif(PodStatus == "Running"),
    Pending = countif(PodStatus == "Pending"),
    Failed = countif(PodStatus == "Failed")
| extend HealthPercent = round(100.0 * Running / Total, 1)
| project HealthPercent, Running, Pending, Failed

Container Resource Usage:

// Top CPU consuming containers
Perf
| where TimeGenerated > ago(15m)
| where ObjectName == "K8SContainer"
| where CounterName == "cpuUsageNanoCores"
| extend CpuCores = CounterValue / 1000000000
| summarize 
    AvgCpu = avg(CpuCores),
    MaxCpu = max(CpuCores)
    by InstanceName
| extend 
    PodName = extract(@"^([^/]+)", 1, InstanceName),
    ContainerName = extract(@"/([^/]+)$", 1, InstanceName)
| top 20 by AvgCpu desc
| project PodName, ContainerName, ['Avg CPU Cores'] = round(AvgCpu, 3), ['Max CPU Cores'] = round(MaxCpu, 3)

Error Log Analysis:

ContainerLog
| where TimeGenerated > ago(1h)
| where LogEntry contains "error" or LogEntry contains "exception" or LogEntry contains "fatal"
| extend Severity = case(
    LogEntry contains "fatal", "Critical",
    LogEntry contains "error", "Error",
    "Warning"
)
| summarize 
    Count = count(),
    SampleLog = any(LogEntry)
    by Name, Namespace, Severity
| order by Count desc

Alert Query Patterns

Queries designed for Azure Monitor alerts need to return a single value or time-series.

Pattern 1: Threshold Alert

// Alert when error rate exceeds 5% for 5 minutes
AppRequests
| where TimeGenerated > ago(5m)
| summarize 
    Total = count(),
    Errors = countif(Success == false)
| extend ErrorRate = 100.0 * Errors / Total
| where ErrorRate > 5.0
| project ErrorRate, Total, Errors

Pattern 2: Anomaly Detection Alert

// Alert on CPU anomalies
Perf
| where TimeGenerated > ago(7d)
| where CounterName == "% Processor Time"
| make-series CpuUsage = avg(CounterValue) default = 0 on TimeGenerated step 5m
| extend (anomalies, score, baseline) = series_decompose_anomalies(CpuUsage, 1.5)
| mv-expand TimeGenerated, CpuUsage, anomalies, score
| where TimeGenerated > ago(10m)
| where todouble(anomalies) != 0
| summarize AnomalyCount = count(), AvgScore = avg(todouble(score))

Pattern 3: Missing Data Alert

// Alert when heartbeat is missing
Heartbeat
| where TimeGenerated > ago(10m)
| summarize LastHeartbeat = max(TimeGenerated) by Computer
| where datetime_diff('minute', now(), LastHeartbeat) > 5
| count
| where Count > 0

Dashboard Design Best Practices

From my experience, here's what makes dashboards effective:

1. Information Hierarchy

Top section (always visible):

• Critical health indicators (tiles)

• Overall system status

• High-level KPIs

Middle section (primary focus):

• Time-series charts showing trends

• Key metrics over time

• Comparison visualizations

Bottom section (details):

• Detailed tables

• Drill-down data

• Advanced analytics

2. Color Coding Standards I Use

🟢 Green: Healthy, normal operation (< 80% utilization)
🟡 Yellow: Warning, attention needed (80-90%)
🔴 Red: Critical, immediate action (> 90%)
⚫ Gray: Unknown, no data

3. Query Performance

Keep dashboard responsive:

• Use time filters on all queries

• Limit result sets (take, top)

• Cache expensive calculations with let

• Use summarization instead of raw data

Example optimization:

// Slower - processes all data then filters
Perf
| summarize avg(CounterValue) by Computer, CounterName, bin(TimeGenerated, 1m)
| where TimeGenerated > ago(1h)

// Faster - filters first
Perf
| where TimeGenerated > ago(1h)
| summarize avg(CounterValue) by Computer, CounterName, bin(TimeGenerated, 1m)

4. Parameter Strategy

Essential parameters:

• Time range (always include)

• Resource filter (subscription, resource group, resource)

• Environment (prod, dev, test)

Optional parameters:

• Severity level

• Status filter

• Custom dimensions

5. Documentation Within Dashboards

Add markdown blocks to explain:

• What each section shows

• How to interpret visualizations

• Links to runbooks for common issues

• SLA targets and thresholds

Sharing and Collaboration

Workbook Templates

Save workbooks as templates for reuse:

1. Gallery template - Public, for common patterns

2. Custom template - Private, for team use

3. ARM template - For deployment automation

Access Control

Control who can view and edit:

• Reader access - View only

• Contributor access - View and edit

• Owner access - Full control

Version Control

Track changes:

• Use meaningful version descriptions

• Test changes in a copy first

• Document breaking changes

Exporting and Integration

Export Options:

1. Excel - For offline analysis

2. Power BI - For advanced analytics

3. ARM templates - For automation

4. Workbook Gallery - For sharing

Integration with Azure Dashboards:

Pin workbook visualizations to Azure dashboards for executive views:

• Navigate to visualization

• Click "Pin to dashboard"

• Select or create dashboard

Key Takeaways

• Azure Workbooks transform queries into interactive dashboards

• Use parameters to make dashboards flexible

• Choose visualization types based on data characteristics

• Follow information hierarchy: KPIs → trends → details

• Standardize color coding for consistency

• Optimize queries for dashboard performance

• Document dashboard purpose and interpretation

• Save as templates for reusability

In Part 6, we'll focus on KQL best practices and performance optimization techniques that ensure your queries run efficiently at scale.