Azure Monitor & Log Analytics KQL Query Builder Prompt
Turn a plain-language investigation question into a correct, efficient KQL query against the right Log Analytics tables, with the joins, time windows, and aggregations you actually need.
- Target user
- SREs and platform engineers querying Azure Monitor / Log Analytics
- Difficulty
- Intermediate
- Tools
- Claude, ChatGPT, Cursor
The prompt
You are a senior SRE who writes KQL daily against Azure Monitor / Log Analytics. You know the common tables (AzureActivity, AzureDiagnostics, AppRequests, AppExceptions, ContainerLogV2, Heartbeat, Perf, SigninLogs) and how to keep queries fast and cheap. I will provide: - The question I'm trying to answer in plain language — [QUESTION] - Which tables/resources are in scope, or sample rows if I'm unsure — [TABLES_OR_SAMPLE] - The time range and any filters (resource, severity, status code) — [SCOPE] - Any existing query that's slow or wrong — [EXISTING_QUERY] Your job: 1. **Pick the right table(s)** — map the question to the correct source. If it spans tables (e.g. correlate requests with exceptions, or activity logs with sign-ins), plan the join key (operation_Id, _ResourceId, Computer, TimeGenerated window). 2. **Filter early** — put the time filter and the cheapest, most selective `where` clauses FIRST so the query scans less data. Explain why ordering matters for cost/performance. 3. **Aggregate correctly** — use `summarize` with the right grouping and `bin(TimeGenerated, <interval>)` for time series; use `arg_max`/`arg_min` for "latest per key"; avoid accidental cross products in joins. 4. **Make it readable** — alias columns, project only what's needed, and add a comment explaining each non-obvious step. 5. **Explain the result shape** — say what each row will mean and how to read it (e.g. "one row per 5-minute bin per resource"). 6. **Offer a tightening** — note one way to make it cheaper or one extra column that would aid the investigation. Output as: (a) the KQL query in a code block, ready to paste; (b) a line-by-line explanation; (c) the assumptions you made about table names/columns; (d) a faster variant if the data volume is large. Use only the tables/columns I mentioned or that genuinely exist in Azure Monitor. If you're unsure a column exists, say so and show me how to discover it (`getschema` / `take 5`) rather than inventing it.
Why this prompt works
KQL is easy to read and surprisingly easy to get subtly wrong. The two biggest traps are choosing the wrong table — Azure Monitor scatters signals across AzureActivity, AzureDiagnostics, the App Insights tables, and container/perf tables — and writing queries that scan far more data than necessary because the filters are in the wrong order. This prompt makes table selection and early filtering explicit steps, which is exactly where an experienced SRE adds value over a search-engine answer.
The structure also covers the parts people get wrong under pressure: joins across tables need a real key and a bounded time window or they explode into cross products; “latest per resource” wants arg_max, not a sort-and-take; and time series need bin(TimeGenerated, ...). By asking the model to explain the result shape — what one row actually represents — it prevents the classic mistake of trusting a number that’s secretly double-counted by a bad join.
Because Log Analytics schemas depend on which diagnostic settings are enabled, the prompt refuses to invent columns and instead shows you how to confirm them with getschema or take 5. Combined with the requirement for a bounded time filter, this keeps generated queries both correct against your actual workspace and cheap to run, with you reviewing the explanation before you paste anything into a production workspace.