12.3 Write KQL Queries to Analyze Logs

KQL in the AI-200 monitoring toolkit

After OpenTelemetry data lands in Azure Monitor, you investigate with Kusto Query Language (KQL) in a Log Analytics workspace. AI-200 does not require you to memorize every function, but you must write and interpret queries that filter time, select columns, aggregate, and join related tables.

Mental model: table → filter → shape → aggregate → join

Most troubleshooting queries follow the same pipeline:

1. Pick a table (AppRequests, AppTraces, …)
2. where to filter time and predicates
3. project / extend to keep useful columns
4. summarize to aggregate
5. join when one table is not enough

Essential Application Insights tables

When using workspace-based Application Insights, queries often target the App* schema:

Classic names like requests / dependencies appear in some Application Insights query scopes; on exam stems, read the table names given and apply the same operators.

Container and platform logs

Not every clue lives in App*. Container stdout/stderr and platform logs matter when instrumentation is incomplete or the process crashes before exporting spans.

Pattern: if Application Insights is empty but the container is restarting, query console logs for crash stacks; if App Insights has requests but dependencies look wrong, stay in AppDependencies and fix instrumentation.

Time filtering with ago

Always constrain time. Unbounded queries are slow and noisy.

AppRequests
| where TimeGenerated > ago(2h)
| where Success == false
| project TimeGenerated, Name, ResultCode, DurationMs, OperationId=operation_Id, Url
| order by TimeGenerated desc
| take 50

Notes for exam reading:

• ago(2h) means “two hours before now”
• Prefer TimeGenerated (ingestion/event time field commonly used in Log Analytics)
• Success == false quickly finds failing requests; also filter ResultCode >= 500 when Success semantics differ

project vs summarize

project shapes rows without collapsing them—ideal when you need individual failing operations.

summarize collapses rows—ideal for rates and top offenders:

AppRequests
| where TimeGenerated > ago(24h)
| summarize Failures=countif(Success == false), Total=count(), AvgDuration=avg(DurationMs) by Name
| extend FailureRate = todouble(Failures) / Total
| order by FailureRate desc

This answers “which operation name is hurting us?” rather than listing every row.

join on operation_Id

Correlation shines with joins:

let failed = AppRequests
| where TimeGenerated > ago(1h)
| where Success == false
| project operation_Id, ReqName=Name, ResultCode, DurationMs;
AppTraces
| where TimeGenerated > ago(1h)
| join kind=inner failed on operation_Id
| project TimeGenerated, ReqName, ResultCode, Message, SeverityLevel
| order by TimeGenerated asc

Interpretation skills:

• inner join keeps only matching IDs (good when you already have failed requests)
• leftouter keeps requests even when traces are missing (detects instrumentation gaps)
• Always project a lean column set so results stay readable

You can similarly join AppRequests to AppDependencies to see which outbound call failed inside a request.

Searching messages and custom dimensions

Traces often need text filters:

AppTraces
| where TimeGenerated > ago(6h)
| where Message has "rate limit" or Message has "429"
| summarize count() by bin(TimeGenerated, 15m)

has is a practical token search. For structured custom dimensions, use dynamic accessors as shown in portal samples (stems usually give the field path).

Scenario: finding a bad Container Apps revision

Asha deployed revision chat-api--v12. Error rates climbed. She runs:

1. AppRequests summarize failures by cloud role / app version dimensions available in her schema
2. ContainerAppConsoleLogs_CL filtered to the revision name for panic/OOM lines
3. Join failed operation_Id values to AppExceptions

She finds exceptions only on v12 referencing a missing environment variable for the model deployment name. Rollback to the prior revision restores health. KQL made the revision association explicit.

Query hygiene for exams and incidents

• Start narrow (time + failure), then widen
• Name columns clearly when joining (ReqName vs Message)
• Beware of high-cardinality summarize by fields (raw URLs with IDs)
• Confirm you are in the correct workspace connected to the Application Insights resource
• Remember legal/privacy limits: do not dump full prompts in shared query results if policy forbids it

Practice shapes to memorize

Master these patterns before section 12.4, which applies the same operators to metrics-heavy and multi-stage AI troubleshooting scenarios.