Observability (5) — KQL for incident investigation

The last part of the series about observability. All telemetry from the previous parts — Serilog logs, OpenTelemetry traces, custom metrics — resides in Application Insights and awaits queries. The query language is KQL. We learn it on a realistic incident, from alert to cause.

Table map

What we instrumented in the series and where it ended up:

The column that links them all: operation_Id — the distributed trace id. All logs, dependencies, and exceptions of a request share the same operation_Id. It is the key to any correlation.

Essential operators in 60 seconds

requests
| where timestamp > ago(1h)                  // temporal filtering -- always first
| where success == false
| project timestamp, name, resultCode, duration, operation_Id   // only useful columns
| sort by timestamp desc
| take 20

// summarize -- the heart of KQL: aggregations by groups
requests
| where timestamp > ago(24h)
| summarize
    total = count(),
    errors = countif(success == false),
    p95 = percentile(duration, 95)
  by name, bin(timestamp, 1h)
| extend errorRate = errors * 100.0 / total

With where, project, summarize, extend, and bin you cover 90% of investigations. render timechart at the end turns the result into a chart directly in the portal.

The incident: “chat is slow for some clients"

The alert from part 4 triggered: p95 on the chat endpoint exceeded 3 seconds. We investigate step by step.

Step 1: confirm and narrow down

requests
| where timestamp > ago(2h)
| where name == "POST /api/chat"
| summarize p50 = percentile(duration, 50),
            p95 = percentile(duration, 95),
            count()
  by bin(timestamp, 5m)
| render timechart

The chart shows: p95 jumped from 900ms to 4.2s about 40 minutes ago; p50 is unchanged. So not everyone is affected — a tail of the distribution suffers. Exactly the case where the average would have lied (part 1).

Step 2: who is affected?

The TenantId is in customDimensions on logs (middleware from part 2). We bring it next to request durations by joining on operation_Id:

requests
| where timestamp > ago(1h) and name == "POST /api/chat"
| where duration > 3000
| join kind=inner (
    traces
    | where timestamp > ago(1h)
    | extend TenantId = tostring(customDimensions.TenantId)
    | where isnotempty(TenantId)
    | distinct operation_Id, TenantId
) on operation_Id
| summarize slowRequests = count() by TenantId
| sort by slowRequests desc

Result: 94% of the slow requests belong to a single tenant. Complete delimitation — it is not a global degradation.

Step 3: where does the time go?

dependencies
| where timestamp > ago(1h)
| where operation_Id in ((
    requests
    | where timestamp > ago(1h) and name == "POST /api/chat" and duration > 3000
    | project operation_Id))
| summarize p95 = percentile(duration, 95), calls = count() by type, target
| sort by p95 desc

Verdict: the Cosmos DB dependency on the semantic cache container has a p95 of 3.1s (normal: 40ms). OpenAI is unchanged. The problem is at the database, on a single container.

Step 4: why?

traces
| where timestamp > ago(1h)
| extend TenantId = tostring(customDimensions.TenantId)
| where TenantId == "tenant-742"
| where severityLevel >= 2   // Warning+
| summarize count() by message = substring(message, 0, 120)
| sort by count_ desc

Top message: rate limiting 429 from Cosmos with retries. And the metric chat.cosmos.request_charge (part 4) on the operation dimension shows increased RUs on cache lookup for this tenant — a new, large tenant with an empty cache: each miss triggers an expensive vector search on a partition that has reached its RUs. Hot partition, exactly the anatomy from the Cosmos DB series.

From alert to cause: four queries, zero deploys, zero guessing. This is what you get with instrumentation from parts 1-4.

Queries to keep in your drawer

Investigations have patterns. Save them as shared functions (query packs) before an incident:

// "Show me everything about this operation_Id" -- first reflex on any incident
let opId = "abc123...";
union requests, dependencies, traces, exceptions
| where operation_Id == opId
| project timestamp, itemType,
    name = coalesce(name, message),
    duration, resultCode = tostring(resultCode)
| sort by timestamp asc

// Top new errors compared to last week
exceptions
| where timestamp > ago(1d)
| summarize today = count() by problemId
| join kind=leftouter (
    exceptions
    | where timestamp between (ago(8d) .. ago(1d))
    | summarize lastWeek = count() by problemId
) on problemId
| where isnull(lastWeek) or today > lastWeek * 3
| sort by today desc

Series conclusion

Five parts, one complete system: the pillars and division of labor between them, structured logs with automatic context, traces crossing services and queues, business metrics with dashboards and alerts, and the KQL that ties them all together at incident time.

With this, the big Cloud-native series with Azure and .NET closes its last major chapter: security without secrets, containers with elastic scaling, resilient messaging, Cosmos DB patterns, and now full observability. The system not only runs — it also tells you what it does.

If you have questions or want to discuss how to build observability in your project, write to me at contact@ludoprogramming.com.