APM Tips

KQL · failures

Top failing operations, this week vs last week

Compare failed requests per operation in the last 7 days with the 7 days before, sampling-adjusted, to see which routes got worse.

Updated

requests failures 

let lookback = 14d;
let split = ago(7d);
requests
| where timestamp > ago(lookback)
| summarize
    this_week = sumif(itemCount, tostring(success) =~ "false" and timestamp > split),
    last_week = sumif(itemCount, tostring(success) =~ "false" and timestamp <= split),
    requests_this_week = sumif(itemCount, timestamp > split)
    by operation_Name
| where this_week > 0 or last_week > 0
| extend change = this_week - last_week
| extend change_pct = iff(last_week > 0, round(100.0 * change / last_week, 1), real(null))
| top 20 by this_week desc

Returns: Up to 20 operations with the most failed requests in the last 7 days, each with last week's failures, the change in count and percent, and this week's total requests.

Pitfalls

  • These are rolling weeks ending now, not calendar weeks. startofweek() gives calendar weeks, which start on Sunday unless you pass an offset.
  • A failure count rises with traffic. requests_this_week is there so you can tell 'more failures' from 'more of everything'; the variation below compares rates.
  • change_pct is empty for operations that had no failures last week. Those are new problems, and the change column shows their full count.
  • The comparison needs 14 days of data. Default retention covers it, but a resource that is a week old has nothing to compare against.

How it works

A weekly review needs a comparison, not a snapshot. This query reads two weeks of requests once and splits them in the summarize, so each operation gets this week’s failures, last week’s, and the difference side by side. It is the table to bring to a Monday reliability meeting.

Line by line

  • let lookback = 14d; reads both weeks. let split = ago(7d); is the boundary between them; everything after it is “this week”.
  • sumif(itemCount, tostring(success) =~ "false" and timestamp > split) counts this week’s failed requests, sampling-adjusted. The last_week column uses the same test with <= split. Reading the table once with conditional sums is cheaper and simpler than two queries and a join.
  • requests_this_week counts all requests this week, failed or not, for scale.
  • where this_week > 0 or last_week > 0 drops operations that never failed.
  • change is the difference in failures. change_pct divides by last week’s count; iff() returns an empty real value when last week was zero, rather than dividing by zero.
  • top 20 by this_week desc ranks by this week’s failures. Sort by change instead to rank by how much worse things got.

Reading the result

A large change with flat traffic is a regression: look at what shipped between the two weeks. A large change with traffic that also doubled may be no regression at all. Operations with failures this week and an empty change_pct are new problems, often a new route or a new caller.

For the trend inside the week, chart the worst operations with failure rate by operation. New exception types over the same period are covered by new exception types this week. If deployments are tagged with a version, the version and build information guide shows how to line failures up with releases.

Variations

  • Compare rates: add requests_last_week = sumif(itemCount, timestamp <= split) and compare 100.0 * this_week / requests_this_week with the same ratio for last week.
  • Calendar weeks: set let split = startofweek(now()); and change the time filter to where timestamp >= split - 7d, so last week is the whole previous Sunday-to-Saturday week and this week runs from Sunday to now.
  • Workspace tables: use AppRequests, TimeGenerated, Success == false, ItemCount and OperationName.

Sources, checked

  1. sumif() (Kusto)learn.microsoft.com · checked
  2. iff() (Kusto)learn.microsoft.com · checked
  3. startofweek() (Kusto)learn.microsoft.com · checked
  4. top operator (Kusto)learn.microsoft.com · checked
  5. Telemetry sampling in Application Insightslearn.microsoft.com · checked

  • Failure rate by operation as a timechart

    requests failures

    Returns: One line per operation for the five operations with the most failed requests, showing the percentage of its requests that failed in each 15-minute interval.

  • 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.

  • New exception types this week

    exceptions exceptions

    Returns: One row per exception type seen in the last 7 days but not in the 28 days before, with its count, the time it first appeared, how many operations threw it, and an example.