Querying Application Insights and Log Analytics with KQL: where, summarize, bin, join, percentiles and Alert Queries

Version note: The examples in this post were run with .NET 10, OpenTelemetry .NET 1.21, Azure.Monitor.OpenTelemetry.Exporter 1.10 and the local KQL engine KustoLoco 1.5.1, which uses Microsoft's Kusto.Language 12.4.1 parser. No Azure account was used: the telemetry went to a local tool that stands in for the Application Insights ingestion endpoint, and the queries ran locally over that data. The log search alert rule and the workbook part were not run.

Guess First

In the production post we looked at the ways to connect Bookstore, the online bookstore used as the shared example across this site's series, to Azure Monitor. This extra post moves on to querying that data. Application Insights and Log Analytics are queried with KQL, the Kusto Query Language. The scenario: Bookstore.Api sends its telemetry to Application Insights with 25 percent sampling. The traffic tool placed 300 orders; because the payment provider was slow, 7 of them ended with a 502. What does the query below return, and how many checkout rows with success == "False" will you see?

requests
| where name == "POST /api/orders/"
| count

A Local Lab: Bookstore Telemetry in the Application Insights Schema

To learn KQL on real data we need Bookstore's own telemetry, but this post uses no Azure subscription. The setup has three parts. First, we add the Application Insights exporter to the API. Bookstore keeps its own OpenTelemetry setup with a resource, instrumentation and an OTLP exporter, so instead of the distro from the production post we only call UseAzureMonitorExporter() from the Azure.Monitor.OpenTelemetry.Exporter package; the same spans, metrics and logs go both to the Collector and to Application Insights:

IOpenTelemetryBuilder otel = builder.Services.AddOpenTelemetry()
    // ... resource, tracing, metrics and logging as in the earlier posts ...
    .UseOtlpExporter();

// Application Insights as a second destination, only when a connection string is configured
// (App Service setting or Key Vault in production; never in the repo).
string? appInsights = builder.Configuration["APPLICATIONINSIGHTS_CONNECTION_STRING"];
if (!string.IsNullOrEmpty(appInsights))
{
    otel.UseAzureMonitorExporter(options =>
    {
        options.ConnectionString = appInsights;
        // A fixed ratio instead of the default 5 traces per second; 1.0 keeps every trace.
        options.TracesPerSecond = null;
        options.SamplingRatio = 1.0F;
        // The exporter keeps a log written inside a trace only if its trace-flags are exactly "sampled" (01);
        // OpenTelemetry 1.21 also sets the W3C random flag (03), so all of those logs would be dropped.
        options.EnableTraceBasedLogsSampler = false;
        // AzureMonitor:SamplingRatio, AzureMonitor:EnableLiveMetrics... can be changed from configuration.
        builder.Configuration.GetSection("AzureMonitor").Bind(options);
    });
}

Second, we point the IngestionEndpoint in the connection string at a local address. The exporter POSTs its telemetry as JSON to that address's /v2.1/track path; tools/Bookstore.KqlLab in the repo writes every item it receives to a file. Third, the same tool turns those items into the Application Insights tables and their workspace counterparts and loads them into KustoLoco, an open source KQL engine that uses Microsoft's Kusto parser and runs the query inside a .NET process. The steps:

# Terminal 1: the stand-in for the ingestion endpoint, writes what it receives to the telemetry folder
dotnet run --project tools/Bookstore.KqlLab -- capture telemetry http://localhost:5096

# Terminal 2: a slow payment provider (5 seconds with a 35 percent chance)
dotnet run --project tools/Bookstore.FakePaymentProvider -- --Provider:SlowRatio=0.35 --Provider:SlowDelayMs=5000

# Terminal 3: the API sends its telemetry to the local endpoint
$env:APPLICATIONINSIGHTS_CONNECTION_STRING = "InstrumentationKey=00000000-0000-0000-0000-000000000000;IngestionEndpoint=http://localhost:5096/"
$env:AzureMonitor__EnableLiveMetrics = "false"
$env:APPLICATIONINSIGHTS_STATSBEAT_DISABLED = "true"
$env:APPLICATIONINSIGHTS_SDKSTATS_DISABLED = "true"
dotnet run --project src/Bookstore.Api

# Terminal 4: 300 orders, then the first query
dotnet run --project tools/Bookstore.Traffic -- 300 4
dotnet run --project tools/Bookstore.KqlLab -- query deploy/kql/tables-overview.kql telemetry

Live Metrics and SDK statistics connect to Microsoft's endpoints, so they are off locally; we checked that the API opened no outside connections during the run. The instrumentation key is an all-zero placeholder. We did two runs, one without sampling and one with $env:AzureMonitor__SamplingRatio = "0.25", each with 300 orders and 20 requests for an ISBN that does not exist. Unless stated otherwise, the output comes from the run without sampling.

On the first attempt the traces table only received the application's startup logs; none of the order logs arrived. The cause is the trace-based log sampler, which is on by default in the exporter: it sends a log written inside a trace only when the trace-flags are exactly "sampled" (01), and the exporter's LogFilteringProcessor checks this with an equality. OpenTelemetry .NET 1.21, however, also sets the random flag from W3C Trace Context Level 2; the traceparent sent to the payment provider ends with 01 on 1.20 and with 03 on 1.21. As a result, no log written inside a request reaches Application Insights. That is why the code sets EnableTraceBasedLogsSampler = false; the price is that logs of traces dropped by sampling are sent too.

This lab is not Log Analytics itself. Knowing the differences up front helps you read the output correctly:

• Our converter builds the tables from the items the exporter sends. Columns that the ingestion service adds (client_*, performanceBucket, appId, problemId) are missing; itemCount is 100 / sampleRate, as Microsoft describes it.
• KustoLoco has no parse and no multi-value percentiles(), make-series does not work without from, and the kind=array form of mv-expand returns empty rows. We also analyzed every query with Microsoft's Kusto.Language parser against the Application Insights schema; there were no errors.
• has behaves differently (see the pitfalls section); sumif returns an empty value in a group with no match; render draws no chart.
• app(), workspace(), _ResourceId, the portal's time picker, alert rules and workbooks are not available locally.

The KQL Model: Tables, Pipes and Two Sets of Table Names

A KQL query starts with a table, and each | passes the result of one step to the next operator. There is no single SELECT statement as in SQL; data flows from top to bottom and each line does one thing: take the table, filter, group, sort. The first query shows what the five tables contain:

// What is in the five tables? One row per telemetry type.
union requests, dependencies, traces, exceptions, customMetrics
| where timestamp > ago(1d)
| summarize rows = count(), first_seen = min(timestamp), last_seen = max(timestamp) by itemType
| order by rows desc

itemType      rows  first_seen                last_seen
------------  ----  ------------------------  ------------------------
dependency    9016  2026-11-14T21:15:25.148Z  2026-11-14T21:18:55.053Z
trace         2318  2026-11-14T21:15:25.058Z  2026-11-14T21:17:52.767Z
request       1842  2026-11-14T21:15:25.374Z  2026-11-14T21:17:52.865Z
exception     423   2026-11-14T21:15:29.839Z  2026-11-14T21:17:52.386Z
customMetric  261   2026-11-14T21:16:25.032Z  2026-11-14T21:18:25.030Z

Server spans go to requests, client and internal spans to dependencies (our own spans such as "check availability" with the InProc type), ILogger records to traces, logs that carry an exception and span exception events to exceptions, and metrics to customMetrics. The trace id from the distributed tracing post is operation_Id here, the parent span id operation_ParentId.

Table names depend on where you run the query. The Logs pane of an Application Insights resource accepts the classic names. For a workspace-based resource the data actually lives in the App* tables of a Log Analytics workspace, and you have to use those names in the workspace's Logs pane. The part of the mapping table on Microsoft Learn that matters for us:

KQL is case sensitive, so you have to change the column names as well as the table names. In the classic tables success is a string ("True"/"False"); Microsoft's own log-based metric queries write success == 'False'. The first real query counts requests and failures per endpoint:

// Requests per endpoint in the last day; sum(itemCount) instead of count() so sampled data is counted right.
requests
| where timestamp > ago(1d)
| summarize requests = sum(itemCount), failed = sumif(itemCount, success == "False") by name
| order by requests desc

name                                       requests  failed
-----------------------------------------  --------  ------
PUT /api/carts/{cartId:guid}/items/{isbn}  914
POST /api/carts/                           300
POST /api/orders/                          300       12
order-confirmations process                288
GET /api/books/{isbn}                      21        21
POST /api/inventory/receipts               18
GET /api/books                             1

where filters rows, summarize groups them and order by sorts. All 21 requests to GET /api/books/{isbn} show as failed: they returned 404 for an ISBN that does not exist, and Application Insights counts 4xx responses other than 401 as failed. "order-confirmations process" is not an HTTP endpoint; it is the span of the background consumer that sends the confirmation e-mail, and consumer spans land in requests as well.

summarize, percentile, bin and make-series

An average hides the story of the slow requests. That is why we chose a histogram in the post on producing metrics; in KQL we ask the same question over the raw request records with percentile():

// Latency per endpoint: p50, p95 and p99 of duration (milliseconds).
requests
| where timestamp > ago(1d)
| summarize requests = sum(itemCount), p50 = percentile(duration, 50), p95 = percentile(duration, 95),
    p99 = percentile(duration, 99) by name
| order by p95 desc

name                                       requests  p50     p95      p99
-----------------------------------------  --------  ------  -------  --------
POST /api/orders/                          300       116.93  8604.01  10031.52
order-confirmations process                288       39.97   57.29    59.37
GET /api/books/{isbn}                      21        1.06    38.05    82.53
GET /api/books                             1         18.43   18.43    18.43
POST /api/inventory/receipts               18        2.93    15.96    23
PUT /api/carts/{cartId:guid}/items/{isbn}  914       5.88    11.13    16.68
POST /api/carts/                           300       2.79    5.19     86.27

The checkout median is 117 ms, but p95 is 8.6 seconds: the slow payment provider creates a long tail. The traffic tool's own measurement also said 8589 ms for p95. In Log Analytics you can get all three values at once with percentiles(duration, 50, 95, 99); the result columns are named like percentile_duration_95. The local engine does not support that form, so we wrote them one by one.

For the time axis you use bin(): bin(timestamp, 1m) rounds every record down to the start of its minute and, together with summarize ... by, produces one row per minute:

// Exceptions per minute: which type, and who reported it (a log category, or an exception event on a span).
exceptions
| where timestamp > ago(1d)
| extend source = iff(isempty(tostring(customDimensions.CategoryName)), "(span event)", tostring(customDimensions.CategoryName))
| summarize exceptions = sum(itemCount) by bin(timestamp, 1m), type, source
| order by timestamp asc, exceptions desc

timestamp             type                                          source                           exceptions
--------------------  --------------------------------------------  -------------------------------  ----------
2026-11-14T21:15:00Z  Polly.Timeout.TimeoutRejectedException        Polly                            56
2026-11-14T21:15:00Z  System.Threading.Tasks.TaskCanceledException  (span event)                     34
2026-11-14T21:15:00Z  System.Threading.Tasks.TaskCanceledException  Polly                            6
2026-11-14T21:15:00Z  Polly.Timeout.TimeoutRejectedException        Bookstore.Ordering.OrderService  6
2026-11-14T21:15:00Z  Polly.Timeout.TimeoutRejectedException        (span event)                     6
2026-11-14T21:16:00Z  Polly.Timeout.TimeoutRejectedException        Polly                            104
2026-11-14T21:16:00Z  System.Threading.Tasks.TaskCanceledException  (span event)                     54
2026-11-14T21:16:00Z  Polly.Timeout.TimeoutRejectedException        (span event)                     2
2026-11-14T21:16:00Z  System.Threading.Tasks.TaskCanceledException  Polly                            2
2026-11-14T21:16:00Z  Polly.Timeout.TimeoutRejectedException        Bookstore.Ordering.OrderService  2
2026-11-14T21:17:00Z  Polly.Timeout.TimeoutRejectedException        Polly                            90
2026-11-14T21:17:00Z  System.Threading.Tasks.TaskCanceledException  (span event)                     49
2026-11-14T21:17:00Z  System.Threading.Tasks.TaskCanceledException  Polly                            4
2026-11-14T21:17:00Z  Polly.Timeout.TimeoutRejectedException        Bookstore.Ordering.OrderService  4
2026-11-14T21:17:00Z  Polly.Timeout.TimeoutRejectedException        (span event)                     4

There are 423 rows in exceptions for 12 failed checkouts: 137 timeouts are written as 137 exception events on HttpClient spans and 262 Polly log records; the remaining 24 rows are OrderService's own span and log. The exception count is not the number of failures but the number of places a failure was reported; compute the error rate from requests or from metrics. For a chart, make-series is a better fit: it fills empty minutes with the default value and returns one array per group:

// The same trend as a series: one array per type, empty minutes filled with 0, ready for a chart.
let start = bin(ago(1h), 1m);
exceptions
| where timestamp > start
| make-series exceptions = sum(itemCount) default = 0 on timestamp from start to now() step 1m by type
| project type, exceptions
| render timechart

type                                          exceptions
--------------------------------------------  -------------------------------------------------------------------------------------------------------------------------------
Polly.Timeout.TimeoutRejectedException        [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,68,108,98,0,0,0,0,0,0,0,0,0,0,0]
System.Threading.Tasks.TaskCanceledException  [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,40,56,53,0,0,0,0,0,0,0,0,0,0,0]
render: timechart

In the portal, render timechart draws these arrays. summarize ... by bin() returns no row for minutes without data, so a chart joins the line across the gap; make-series fills them with 0.

Now the answer to Guess First. We ran the same query on both runs:

// Rows stored vs requests they represent: count() counts rows, sum(itemCount) estimates the real number.
requests
| where timestamp > ago(1d) and name == "POST /api/orders/"
| summarize rows = count(), estimated = sum(itemCount), p95 = round(percentile(duration, 95), 0),
    failed_rows = countif(success == "False"), failed_estimated = sum(iff(success == "False", itemCount, 0))

# AzureMonitor:SamplingRatio = 1.0 (no sampling)
rows  estimated  p95   failed_rows  failed_estimated
----  ---------  ----  -----------  ----------------
300   300        8604  12           12

# AzureMonitor:SamplingRatio = 0.25
rows  estimated  p95   failed_rows  failed_estimated
----  ---------  ----  -----------  ----------------
82    328        8414  0            0

With 25 percent sampling, 82 of the 300 checkouts were kept; count() says 82. Application Insights samples whole traces and writes on every kept record how many records it stands for in itemCount; at 25 percent that value is 4. That is why sum(itemCount) says 328, while the real number is 300. More importantly, none of the 7 failed checkouts is in the sample: an error rate query returns 0 percent. In the same run, the metric side is complete:

// The checkout metric is not sampled: every checkout is in the histogram's count.
customMetrics
| where timestamp > ago(1d) and name == "bookstore.checkout.duration"
| summarize checkouts = sum(valueCount) by outcome = tostring(customDimensions["bookstore.checkout.outcome"])

outcome         checkouts
--------------  ---------
placed          293
payment_failed  7

Metrics are aggregated in the SDK and sampling does not touch them. Ask metrics about counts and rates, and ask traces and logs for the story of a single request; the rule we stated for Tempo in the production post applies here too.

join, lookup and the End-to-End Story of One Checkout

Tables join on operation_Id. In the trace backend post we added an attempt timeout and retries to the payment call; a join shows how many attempts each checkout needed and how long it kept the customer waiting:

// How many payment attempts did each checkout need, and what did that cost the customer?
requests
| where timestamp > ago(1d) and name == "POST /api/orders/"
| project operation_Id, order_ms = duration, resultCode
| join kind=inner (
    dependencies
    | where timestamp > ago(1d) and type == "Http" and name == "POST /v1/charges"
    | summarize attempts = count(), timeouts = countif(success == "False") by operation_Id
  ) on operation_Id
| summarize orders = count(), failed_orders = countif(resultCode == "502"), p50_ms = round(percentile(order_ms, 50), 0),
    max_ms = round(max(order_ms), 0) by attempts
| order by attempts asc

attempts  orders  failed_orders  p50_ms  max_ms
--------  ------  -------------  ------  ------
1         205     0              98      223
2         65      0              4286    4374
3         30      12             8604    10046

68 percent of the checkouts finish in one attempt at around 100 ms. When a second attempt is needed the time goes up to 4.3 seconds; of those that needed three attempts, 12 hit the 10-second total timeout and failed. We wrote kind=inner on purpose: the default join kind in KQL is innerunique, which first deduplicates the keys on the left side and loses rows in queries written with SQL habits.

When the right side is a small dictionary table, lookup fits better. Microsoft's own advice is to use lookup instead of join when you enrich a large table with a small dimension table:

// lookup: enrich the result with a small dimension table (which module owns which endpoint).
let owners = datatable(name: string, module: string) [
    "POST /api/orders/", "Ordering",
    "POST /api/carts/", "Ordering",
    "PUT /api/carts/{cartId:guid}/items/{isbn}", "Ordering",
    "GET /api/books", "Catalog",
    "GET /api/books/{isbn}", "Catalog",
    "POST /api/inventory/receipts", "Inventory",
    "order-confirmations process", "Notifications"
];
requests
| where timestamp > ago(1d)
| summarize requests = sum(itemCount), p95 = round(percentile(duration, 95), 0) by name
| lookup kind=leftouter owners on name
| summarize requests = sum(requests), worst_p95 = max(p95) by module
| order by requests desc

module         requests  worst_p95
-------------  --------  ---------
Ordering       1514      8604
Notifications  288       57
Catalog        22        38
Inventory      18        16

let gives an expression a name; datatable defines a small table inside the query. The same tools let us line up every record of a single request in order. In the Debugging and Problem Solving series we built a timeline of events from a trace id; the KQL equivalent is an operation_Id chosen with toscalar and a union of four tables:

// The whole story of one operation: the slowest checkout, step by step (SQL calls and HttpClient's own logs left out).
let slowest = toscalar(
    requests
    | where timestamp > ago(1d) and name == "POST /api/orders/"
    | top 1 by duration desc
    | project operation_Id);
union requests, dependencies, traces, exceptions
| where timestamp > ago(1d) and operation_Id == slowest
| where not(itemType == "dependency" and type == "SQL")
| where not(tostring(customDimensions.CategoryName) startswith "System.Net.Http")
| extend what = substring(case(itemType == "trace", message, itemType == "exception", type, name), 0, 48)
| project timestamp, itemType, what, duration = round(duration, 0), success
| order by timestamp asc

timestamp                 itemType    what                                              duration  success
------------------------  ----------  ------------------------------------------------  --------  -------
2026-11-14T21:15:34.349Z  request     POST /api/orders/                                 10046     False
2026-11-14T21:15:34.352Z  dependency  check availability                                1         True
2026-11-14T21:15:34.354Z  dependency  reserve stock                                     3         True
2026-11-14T21:15:34.363Z  dependency  authorize payment                                 10030     False
2026-11-14T21:15:34.363Z  dependency  POST /v1/charges                                  4002      False
2026-11-14T21:15:38.365Z  exception   System.Threading.Tasks.TaskCanceledException
2026-11-14T21:15:38.365Z  trace       Resilience event occurred. EventName: 'OnTimeout
2026-11-14T21:15:38.366Z  exception   Polly.Timeout.TimeoutRejectedException
2026-11-14T21:15:38.367Z  exception   Polly.Timeout.TimeoutRejectedException
2026-11-14T21:15:38.547Z  dependency  POST /v1/charges                                  4002      False
2026-11-14T21:15:42.549Z  exception   System.Threading.Tasks.TaskCanceledException
2026-11-14T21:15:42.550Z  trace       Resilience event occurred. EventName: 'OnTimeout
2026-11-14T21:15:42.551Z  exception   Polly.Timeout.TimeoutRejectedException
2026-11-14T21:15:42.552Z  exception   Polly.Timeout.TimeoutRejectedException
2026-11-14T21:15:42.655Z  dependency  POST /v1/charges                                  1709      False
2026-11-14T21:15:44.364Z  exception   System.Threading.Tasks.TaskCanceledException
2026-11-14T21:15:44.365Z  exception   System.Threading.Tasks.TaskCanceledException
2026-11-14T21:15:44.365Z  trace       Resilience event occurred. EventName: 'OnTimeout
2026-11-14T21:15:44.366Z  exception   Polly.Timeout.TimeoutRejectedException
2026-11-14T21:15:44.367Z  exception   Polly.Timeout.TimeoutRejectedException
2026-11-14T21:15:44.393Z  trace       Order not placed: PaymentFailed, took 10044 ms

The first two attempts time out after 4 seconds, the third is cut off in the remaining 1.7 seconds of the 10-second total budget, and the order ends with PaymentFailed. Every timeout is followed by more than one exception record; that is where the 423 comes from.

Getting Fields Out of Log Text: mv-expand, parse and extract

In the log backends post we saw the value of structured logging: every value in an ILogger message template travels as an attribute. In Application Insights these live in customDimensions. mv-expand shows which fields a record carries; it expands a property bag into single-key bags:

// Structured fields first: which properties does an "order placed" log record carry?
traces
| where timestamp > ago(1d) and message matches regex @"^Order \d+ placed"
| take 1
| mv-expand field = customDimensions
| extend key = tostring(bag_keys(field)[0])
| project key, field

key           field
------------  --------------------------------------------------
OrderId       {"OrderId":"19"}
ItemCount     {"ItemCount":"2"}
Total         {"Total":"608.90"}
Currency      {"Currency":"TRY"}
ElapsedMs     {"ElapsedMs":"200"}
CategoryName  {"CategoryName":"Bookstore.Ordering.OrderService"}

With these fields there is no need to parse text: toint(customDimensions.ElapsedMs) gives you a number directly. For logs that only have text, use parse when the text repeats in the same format and extract when the format varies. The local engine has no parse, so we only validated this query with Microsoft's parser:

// parse: for logs that only have text (old code, third-party libraries).
traces
| where timestamp > ago(1d) and message matches regex @"^Order \d+ placed"
| parse message with "Order " order_id:long " placed: " items:int " items, " total:real " " currency:string ", took " elapsed_ms:long " ms"
| extend order_type = iff(items >= 10, "institutional", "retail")
| summarize orders = count(), avg_total = round(avg(total), 2), p95_ms = percentile(elapsed_ms, 95) by order_type, currency

We ran the same thing locally with extract:

// The same with extract (regular expressions): one capture group per value.
traces
| where timestamp > ago(1d) and message matches regex @"^Order \d+ placed"
| extend items = toint(extract(@"placed: (\d+) items", 1, message)),
    total = todouble(extract(@"items, ([\d.]+) ", 1, message)),
    currency = extract(@"items, [\d.]+ ([A-Z]{3})", 1, message),
    elapsed_ms = tolong(extract(@"took (\d+) ms", 1, message))
| extend order_type = iff(items >= 10, "institutional", "retail")
| summarize orders = count(), avg_total = round(avg(total), 2), p95_ms = percentile(elapsed_ms, 95) by order_type, currency

order_type     currency  orders  avg_total  p95_ms
-------------  --------  ------  ---------  ------
retail         TRY       258     656.88     8386
institutional  TRY       30      3854.64    8483

Text processing is also how you find personal data. In the production post we searched Loki for e-mail addresses; the KQL equivalent:

// The KQL version of the PII check from the production post: e-mail addresses in log messages.
traces
| where timestamp > ago(1d)
| extend email = extract(@"[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}", 0, message)
| where isnotempty(email)
| summarize records = count(), distinct_addresses = dcount(email) by category = tostring(customDimensions.CategoryName)

category                                         records  distinct_addresses
-----------------------------------------------  -------  ------------------
Bookstore.Notifications.OrderConfirmationSender  288      50

In the production post the Collector hashed the e-mail address, but UseAzureMonitorExporter() sends directly from inside the application, so this path skips the Collector: 288 records of the confirmation log hold 50 distinct addresses. Either never write the address into the log or send to Azure through the Collector as well; masking should happen in one place.

customMetrics and Their PromQL Counterparts

The bookstore.checkout.duration histogram from the post on producing metrics is stored in customMetrics not with its buckets but with a sum, a count, a minimum and a maximum per export interval:

// customMetrics holds pre-aggregated points: sum, count, min, max per export interval; no buckets, so no p95.
customMetrics
| where timestamp > ago(1d) and name == "bookstore.checkout.duration"
| extend outcome = tostring(customDimensions["bookstore.checkout.outcome"]), order_type = tostring(customDimensions["bookstore.order.type"])
| summarize checkouts = sum(valueCount), avg_s = round(sum(valueSum) / sum(valueCount), 3), max_s = round(max(valueMax), 3) by outcome, order_type
| order by checkouts desc

outcome         order_type     checkouts  avg_s  max_s
--------------  -------------  ---------  -----  -----
placed          retail         258        1.54   8.66
placed          institutional  30         1.78   8.62
payment_failed  retail         12         10.03  10.04

You get the average and the maximum from this, but not p95: without buckets there is no equivalent of histogram_quantile. For percentiles you look at requests (sampled raw records) or at Prometheus. A rough mapping to the queries in the Prometheus post:

The last row returned 288, the same as the number of successful orders. The basic difference: PromQL works on time series, KQL on event records; you build the window yourself with bin().

Alert Queries, Workbooks and Query Cost

A log search alert runs a KQL query at regular intervals and compares the result with a threshold. The principle from the alerting post still holds: alert on the symptom the user sees, not on the cause. The query for the checkout error rate:

// Log search alert: checkout error rate (5xx) in the window the alert rule gives; fires when error_rate > 2.
requests
| where name == "POST /api/orders/"
| summarize total = sum(itemCount), failed = sum(iff(toint(resultCode) >= 500, itemCount, 0))
| extend error_rate = round(100.0 * failed / total, 2)

total  failed  error_rate
-----  ------  ----------
300    12      4

The rule supplies the window: Measure error_rate, Aggregation type Maximum, Aggregation granularity 15 minutes, threshold 2, Frequency of evaluation 5 minutes. According to the documentation, the frequency can be anywhere from one minute to one day and ago() only takes a constant timespan in an alert query. In our data the rate is 4 percent, so the rule would fire. The burn-rate alert in the SRE and Production Incidents series uses the same pattern over two windows. With 25 percent sampling this query would return 0; for rare failures a standard metric alert (Failed requests), which sampling does not affect, is more reliable.

Workbooks combine the same queries with parameters (time range, cloud_RoleName) on a report page. Like the dashboards in the Grafana post, keep the workbook JSON in Git. We could not run this part without Azure.

Cost and speed depend on how much data a query scans. Microsoft's best practices page gives this order: first filter on the datetime column (Kusto can often skip unrelated data shards entirely), then string filters at the term level (has), and last the ones that need a scan (contains). Moving a high-volume table (for example AppTraces) from Analytics to the Basic or Auxiliary plan makes ingestion cheaper, but queries are billed separately and with Auxiliary, alerts stop working.

In Production

Manage queries like code. The files under deploy/kql are the source of alert rules, workbooks and runbooks; when a query changes, it should go through review. In the runbooks from the alerting post, write down which KQL query to start an investigation with.

Check the tables after package upgrades. As this post showed, a small version change in OpenTelemetry combined with the exporter's log filter can silently cut off every application log. A "no logs" alert built on a query like traces | summarize count() by bin(timestamp, 1h) is the Azure counterpart of the "no telemetry" alert from the alerting post.

Common Pitfalls

Writing queries without a time filter. In the portal the time picker protects you, but when the same query runs from the API, a workbook or a script, it scans the whole retention period. Start every query with where timestamp > ago(...).

Treating contains and has as the same. has looks for a whole term: Kusto splits a string into sequences of alphanumeric characters and indexes the terms of at least 3 characters. The terms of "grace1@example.com" are grace1, example and com; has "grace" returns false and contains "grace" returns true. has is fast because it uses the index, contains scans the column. Our local engine differs from Kusto here:

// has looks for a whole term; contains looks for a substring anywhere in the text.
traces
| where timestamp > ago(1d)
| summarize has_grace = countif(message has "grace"), contains_grace = countif(message contains "grace"),
    has_grace1 = countif(message has "grace1"), has_example = countif(message has "example")

has_grace  contains_grace  has_grace1  has_example
---------  --------------  ----------  -----------
288        288             63          288

By Kusto's term definition the expected results are 0, 288, 6 and 288; KustoLoco matches has against the start of a term, so it found 288 and 63. Verify has behavior with the documentation and a real workspace, not with the local engine.

Using count() on sampled data. The number of rows is not the number of requests; use sum(itemCount), and count rare events (failures, timeouts) from metrics anyway.

A Small Experiment

★☆☆ Rewrite failed-dependencies.kql with the table and column names of the Log Analytics workspace (AppDependencies, DependencyType, Success, ItemCount) and show in the lab that both versions give the same result.

★★☆ Get the number of "real failures" out of exceptions: reduce it to one row per operation_Id and count only the operations that failed in requests. Confirm that the result is 12.

★★★ Write a two-window burn-rate query for checkout (1 hour and 5 minutes, 99.5 percent target) that computes both error rates in a single row. Run it on the data with 25 percent sampling and compare it with a version based on customMetrics.

With this extra post the series also covers the Azure side of the questions we asked with PromQL, TraceQL and LogQL on the open source stack. Whichever backend you choose, the question stays the same: what did the user experience, and which signal shows it most accurately?