APM Tips

KQL · failures

Failed requests joined to their exceptions, by problem ID

Join failed requests to the exceptions recorded in the same operation and group them by problemId, to see which code paths cause the failures.

Updated

requestsexceptions failures 

let lookback = 24h;
let failedRequests = requests
    | where timestamp > ago(lookback)
    | where tostring(success) =~ "false"
    | summarize resultCode = take_any(resultCode) by operation_Id;
exceptions
| where timestamp > ago(lookback)
| join kind=inner failedRequests on operation_Id
| summarize
    exceptions = sum(itemCount),
    failed_operations = dcount(operation_Id),
    example_type = take_any(type),
    example_message = take_any(outerMessage)
    by problemId, operation_Name, resultCode
| order by exceptions desc

Returns: One row per problem ID, operation and result code among failed requests, with the sampling-adjusted exception count, how many failed operations it appeared in, and an example exception type and message.

Pitfalls

  • Not every failed request has an exception. A handler that returns a 500 without throwing, or an exception your code catches and never tracks, leaves the request with no match here; compare the total with failed requests by result code.
  • join defaults to innerunique, which deduplicates the left side on the key. kind=inner keeps every exception, which is what you want when counting them.
  • The right side is one row per operation_Id on purpose. Joining to raw request rows would repeat each exception once per failed request in that operation.
  • Sampling keeps or drops whole operations together, because it decides by operation ID, so a sampled-in failed request normally brings its exceptions with it.

How it works

A failed request tells you that something broke; the exception recorded in the same operation usually tells you what. This query puts the two together and groups by problemId, which the data model describes as the field that identifies where in code an exception was thrown, typically the exception type plus a function from the call stack. One problem ID is one bug, however many requests it broke.

Line by line

  • let failedRequests = ...; collects failed requests in the window and reduces them to one row per operation_Id, keeping one resultCode. operation_Id is shared by every telemetry item in the same operation, so it is the key that links a request to its exceptions.
  • join kind=inner failedRequests on operation_Id keeps only exceptions from operations that contain a failed request. Microsoft’s own Classic API examples join exceptions to requests on operation_Id the same way.
  • exceptions = sum(itemCount) counts exceptions the sampling-aware way.
  • failed_operations = dcount(operation_Id) counts distinct operations. At its default accuracy, dcount() is exact for small sets (up to about 1,000 values) and an estimate above that.
  • example_type and example_message give one exception type and outer message per row, enough to recognise the bug without opening anything.
  • order by exceptions desc puts the noisiest problem first.

Reading the result

A problem ID that appears across many operations is shared code: a database helper, an auth check, a serializer. One that appears in a single operation is that route’s own bug. Rows with the same problem ID but different result codes are worth a look: the same exception surfacing as a 500 in one place and a 400 in another usually means one code path catches and translates it and another doesn’t.

Take any problem ID into exception details for a problem ID for messages, stack frames and affected users. The ASP.NET MVC and Web API guide explains which exceptions the classic SDK records on its own and which you track yourself, the usual reason a failed request has no exception.

Variations

  • Failures without an exception: swap kind=inner for kind=rightanti and group the result by resultCode to count failed operations that have no exception at all.
  • Workspace tables: use AppRequests and AppExceptions, TimeGenerated, Success, OperationId, ProblemId, ExceptionType (the classic type), OuterMessage and ItemCount.

Sources, checked

  1. join operator (Kusto)learn.microsoft.com · checked
  2. Application Insights telemetry data model (exception problemId)learn.microsoft.com · checked
  3. Application Insights Classic API 2.x (exceptions joined to requests; sampling by operation ID)learn.microsoft.com · checked
  4. dcount() (Kusto)learn.microsoft.com · checked
  5. Migrate an Application Insights classic resource to a workspace-based resource (table schemas)learn.microsoft.com · checked

  • Failed requests by result code

    requests failures

    Returns: One row per result code among failed requests, with the sampling-adjusted failure count, the number of distinct operations affected and one example operation.

  • Exceptions by type and operation

    exceptions exceptions

    Returns: Up to 50 rows, one per exception type and operation, with the sampling-adjusted count, the number of distinct problem IDs, one outer message and one innermost exception type.

  • Exception details for one problem ID

    exceptions exceptions

    Returns: One row for the problem ID with its sampling-adjusted count, first and last time seen, up to ten operations, roles and versions, the number of distinct users and up to five distinct messages.

  • End-to-end trace for one operation_Id

    requestsdependenciestracesexceptionspageViewscustomEvents operations

    Returns: Every telemetry item recorded with the given operation_Id in the last 7 days, oldest first, with its type, service, name or message, duration, outcome, severity, and the IDs that link parent and child.