The first Application Insights post on this blog the App Insights post covered what it is and how to wire it up - the setup half. This post covers the other half, the one most tutorials skip: what to actually type into the query box once something is broken and there are three minutes to find out why. KQL was used for exactly this kind of troubleshooting and reporting on an Azure integration platform at Blue Yonder - these are the queries that got real use in production, not a generic KQL syntax tour.
The Tables That Matter
Application Insights stores telemetry across several tables, and nearly every troubleshooting query starts by picking one of them and filtering or aggregating it. The requests table holds every incoming HTTP request, success or failure, with duration. The dependencies table holds every outgoing call an application makes - database calls, external API calls, storage calls. The exceptions table holds unhandled exceptions along with their stack traces. The traces table holds custom log messages, typically whatever gets written through ILogger. The customEvents table holds custom application events defined by the developer.
Query 1: Failed Requests by Status Code
The first question after "something is broken" is almost always "what is actually failing, and how often."
requests
| where timestamp > ago(1h)
| where success == false
| summarize FailureCount = count() by resultCode, name
| order by FailureCount desc
This query looks at the last hour of requests, filters down to only the failed ones, and groups the results by both status code and operation name together. Grouping by operation name matters here specifically, because "500 errors" alone doesn't say which endpoint is actually failing - two completely different endpoints could both be throwing 500s for entirely unrelated reasons, and lumping them together hides that. Sorting by failure count descending puts the most frequent problem at the top of the results. This is the query worth running first, every time, because it tells you where to look next rather than requiring a guess.
Query 2: Slow Requests Using Percentile, Not a Flat Threshold
A common mistake is filtering for "requests over one second" as if that number means the same thing for every endpoint in an application. A percentile-based query finds genuine outliers relative to each endpoint's own normal behavior instead of applying one arbitrary number across everything.
requests
| where timestamp > ago(1h)
| summarize
P50 = percentile(duration, 50),
P95 = percentile(duration, 95),
P99 = percentile(duration, 99),
Count = count()
by name
| order by P95 desc
This groups requests by operation name and calculates the 50th, 95th, and 99th percentile duration for each one, then sorts so the endpoints with the worst tail latency appear first. The reasoning behind using percentiles instead of a flat threshold is worth spelling out: an endpoint that is normally 200 milliseconds and occasionally spikes to 800 milliseconds has a real P95 problem worth investigating, since something is clearly going wrong some percentage of the time. An endpoint that is normally 900 milliseconds and stays consistently there might simply be a naturally heavier operation by design, not necessarily broken at all. A flat "over one second" filter cannot distinguish between these two very different situations, but percentile-based analysis can.
Query 3: Grouping Exceptions So Ten Thousand Rows Become Five Problems
exceptions
| where timestamp > ago(24h)
| summarize
Count = count(),
LastSeen = max(timestamp)
by type, outerMessage
| order by Count desc
| take 20
This groups every exception by its type and message together, rather than by type alone - a NullReferenceException thrown in one method represents a genuinely different problem than the same exception type thrown somewhere else entirely, and grouping by type alone would incorrectly merge those into one bucket. The query counts how many times each distinct problem occurred and shows the most recent occurrence, which tells you whether something is actively happening right now or happened once several days ago and hasn't recurred since. Limiting the result to the top twenty is usually more than sufficient to surface the real problems. A full day of production exceptions is often thousands of individual rows that collapse down to five or six genuinely distinct issues once grouped this way - scrolling through raw exception rows one at a time is a dramatically slower way to arrive at the same information.
Query 4: Isolating Dependency Failures From Application Failures
This is the query that saves the most real debugging time in practice. A dependency failure and an application failure look identical from the outside - an API simply returned an error either way - and this query is what actually tells the two apart.
dependencies
| where timestamp > ago(1h)
| where success == false
| summarize FailureCount = count() by target, type, resultCode
| order by FailureCount desc
This looks specifically at outgoing calls an application made - not requests coming into the application, but calls going out to something else - and groups them by which target system was called, what kind of dependency it was (SQL, HTTP, Azure Storage, and so on), and the result code returned. This surfaces failures that were actually caused by something being called, rather than a bug in the calling code itself. In a real production troubleshooting scenario, an API endpoint returning 500 errors looks identical whether the underlying bug lives in the endpoint's own code or in a downstream system that endpoint depends on. Running this dependency query alongside the failed-requests query from earlier immediately reveals whether request failures line up in time with a dependency also failing during that same window - if an external system's API was returning errors during that exact period, the actual problem was never in the calling application's code at all.
Query 5: Joining Requests and Dependencies to Trace a Specific Failure
requests
| where timestamp > ago(1h)
| where success == false
| where name == "POST /api/orders"
| join kind=inner (
dependencies
| where success == false
) on operation_Id
| project
timestamp,
requestName = name,
dependencyTarget = target,
dependencyType = type,
resultCode1
This takes failed requests to one specific endpoint and joins them to any failed dependency calls that occurred as part of that same request, using operation_Id - a shared identifier that links a request to everything it triggered downstream. The result shows exactly which downstream call failed for each individual failed request. This is the query for the specific situation of "this one endpoint is failing, and I need to know exactly what it's calling that's actually breaking" - joining on operation_Id this way is considerably faster than manually correlating timestamps across two separate query results by hand.
Query 6: A Basic Availability Percentage Over a Rolling Window
requests
| where timestamp > ago(24h)
| summarize
Total = count(),
Failed = countif(success == false)
| extend AvailabilityPercent =
round(100.0 * (Total - Failed) / Total, 2)
| project Total, Failed, AvailabilityPercent
This counts total requests and failed requests over a 24-hour window and calculates a simple availability percentage from the two. It works well as a quick sanity check before diving into the more detailed queries above - answering the basic question of whether this is a minor blip or a genuine outage before spending time on deeper investigation. Extending this into a day-over-day trend is a small change: wrapping the same logic in a bin(timestamp, 1d) grouping instead of a single flat summarize produces a daily trend line instead of one static number.
A Starter Query for a Custom Dashboard
requests
| where timestamp > ago(7d)
| summarize
RequestCount = count(),
FailureCount = countif(success == false),
P95Duration = percentile(duration, 95)
by bin(timestamp, 1h), name
| order by timestamp desc
This is the query behind a genuinely useful monitoring dashboard panel - request volume, failure count, and P95 latency, bucketed hourly, broken down per endpoint, over the last week. Pinning this result as a chart on an Azure Dashboard turns it into a real at-a-glance health view rather than something that has to be remembered and manually queried whenever a concern comes up.
Key Lessons
Starting troubleshooting with the failed-requests query is worth doing as a habit, since it points toward where to look next rather than requiring a guess about where the problem might be.
Percentile-based duration queries find genuine outliers in a way a flat threshold like "over one second" simply cannot, because a flat number has no way to distinguish a real problem from an endpoint that is naturally heavier by design.
Grouping exceptions by type and message together, rather than type alone, is what actually collapses a large volume of raw exception rows into a handful of real, distinct problems worth investigating.
Dependency failures and application failures look identical from the outside, and querying the dependencies table specifically, rather than assuming the bug lives in application code, is what tells the two apart.
operation_Id is the key that links a request to every dependency call it triggered, which is what turns a vague "this endpoint is failing" into a specific, actionable "this endpoint is failing because of this particular downstream call."
Summary
Wiring up Application Insights is the easy half of the work. Knowing what to actually query once something breaks is the skill that matters in day-to-day practice. These six queries cover situations that come up constantly in real production troubleshooting - failed requests, slow requests, grouped exceptions, dependency failures, tracing one specific failure end to end, and a basic availability check. Keeping a working version of each one saved somewhere accessible, ready to paste in and adjust the time window, turns "something is broken, where do I even start" into a noticeably shorter investigation.
Originally published at TechStack Blog: https://www.techstackblog.com/post.html?slug=azure-monitoring-kql-queries
Related reading on TechStack Blog: https://www.techstackblog.com/post.html?slug=azure-app-insights-deep-dive
More from TechStack Blog: Azure: https://www.techstackblog.com/category.html?cat=azure
Top comments (0)