Parcourir les aide-mémoire · Microsoft Fabric
KQL for Microsoft Fabric Cheat Sheet
The Kusto Query Language (KQL) a Microsoft Fabric data engineer uses most in Eventhouse and KQL querysets: filter, shape, sort, summarize, time windows with bin(), joins, nulls and dynamic fields, with a SQL-to-KQL mapping and seven practice exercises.
Sur cette page
- Problem
- Quick answer
- From SQL to KQL
- Code / commands
- 0. Create a practice dataset
- 1. Filter with where
- 2. Shape columns: project, project-away, extend
- 3. Sort and limit: sort, order, top, take
- 4. Aggregate with summarize
- 5. Time: now(), ago(), datetime literals
- 6. Time windows with bin()
- 7. Joins: lookup and join
- 8. Conditional logic: iff() and case()
- 9. Missing values
- 10. Dynamic (JSON) fields and parsing
- 11. Latest value per key with arg_max()
- 12. Reuse with let
- Exercises
- Exercise 1: the last 30 minutes
- Exercise 2: failures by service
- Exercise 3: 5-minute windows
- Exercise 4: top failing devices and services
- Exercise 5: join to reference data
- Exercise 6: a calculated field
- Exercise 7: diagnose the wrong time window
- What the result means
- What to look for
- Production notes
- Common mistakes
- 1. Writing KQL in SQL order
- 2. Expecting sort by to be ascending
- 3. Trusting the default join
- 4. Treating ago(1d) as “today”
- 5. Testing strings with isnull()
- 6. Comparing a dynamic value without converting it
- Next step
- Related deep dives
Problem
In Microsoft Fabric, KQL is the language of Real-Time Intelligence: you use it to query an Eventhouse (its KQL databases), build Real-Time Dashboards, define Activator conditions, and read workspace monitoring logs.
If you know SQL, KQL feels familiar for about ten minutes. Then the differences start to matter:
- the query reads top to bottom as a pipeline of operators, starting from the table;
- string comparison with
==is case-sensitive; sort bysorts descending unless you say otherwise;- the default
joinremoves duplicate keys on the left side; - almost everything is about time:
ago(),bin()and time windows.
This sheet covers the KQL a Fabric data engineer needs most, not the whole language. Every example uses the same small dataset, so the queries build on each other.
Quick answer
Filter rows → where
Pick / drop columns → project, project-away, project-rename
Add a computed column → extend
Sort / limit → sort by (desc by default), top N by, take
Aggregate → summarize Agg = func() by Column
Time buckets → summarize ... by bin(Timestamp, 5m)
Relative time → ago(30m), now(), startofday(now())
Enrich with reference → lookup (small dimension) or join kind=...
Conditional values → iff(), case()
Missing values → isnull(), isempty(), coalesce()
JSON / dynamic fields → Properties.field, tostring(), parse_json()
Chart it → render timechart
The canonical shape of a real-time query:
TelemetryEvents
| where Timestamp > ago(1h)
| where Status == "Failed"
| summarize Failures = count()
by bin(Timestamp, 5m), Service
| order by Timestamp desc
Read it as: take the table → keep the last hour → keep failures → count per 5-minute bucket and service → newest first.
From SQL to KQL
| SQL idea | KQL | Where the mental model breaks |
|---|---|---|
FROM t |
t (first line) |
The table comes first; each ` |
SELECT a, b |
project a, b |
project can also rename and compute: project Svc = Service |
SELECT *, x AS y |
extend y = x |
extend keeps every column and adds new ones |
WHERE |
where |
== is case-sensitive; use =~ for case-insensitive equality |
GROUP BY + aggregates |
summarize ... by |
Aggregates and grouping columns live in one operator |
HAVING |
where after summarize |
There is no HAVING; just filter the summarized rows |
ORDER BY |
sort by / order by |
Default is descending, the opposite of SQL |
TOP n ... ORDER BY |
top n by col |
Also descending by default |
TOP n without order |
take n |
Arbitrary rows; fine for a peek, never for a result |
JOIN |
join kind=... |
Default kind is innerunique, which deduplicates left-side keys; always state the kind |
LEFT JOIN to a dimension |
lookup |
Built for enriching a large table with a small one |
NULL |
isnull(), isempty() |
string columns are never null, only empty; numbers and datetimes can be null |
CASE WHEN |
case() or iff() |
Functions inside extend, not a statement |
DATEADD(hour, -1, GETUTCDATE()) |
ago(1h) |
Timestamps are UTC; timespans are literals such as 5m, 1h, 7d |
Two habits help most: put the time filter first, and read every query top to bottom as a pipeline.
Code / commands
0. Create a practice dataset
Run these in a KQL database in an Eventhouse (each command separately). They create a telemetry table with about two days of synthetic events relative to now, plus a small reference table. The Real-Time Intelligence hands-on guide in Related deep dives loads the same shape from an eventstream instead.
.create table TelemetryEvents (
Timestamp: datetime,
DeviceId: string,
Service: string,
Region: string,
EventType: string,
DurationMs: long,
Status: string,
Value: real,
Properties: dynamic
)
.set-or-append TelemetryEvents <|
range i from 1 to 20000 step 1
| extend Timestamp = now() - i * 9s
| extend Service = tostring(dynamic(["checkout", "payments", "search", "catalog", "auth"])[i % 5])
| extend Region = tostring(dynamic(["westeurope", "eastus", "southeastasia"])[toint(rand(3))])
| extend DeviceId = strcat("dev-", tostring(toint(rand(40))))
| extend Failed = rand() < iff(Service == "payments", 0.12, 0.03)
| extend Status = iff(Failed, "Failed", "Succeeded")
| extend EventType = iff(Failed, "error", "request")
| extend DurationMs = tolong(40 + rand(300) + iff(Service == "search", 400, 0))
| extend Value = round(rand() * 100, 2)
| extend Properties = bag_pack("version", iff(i % 7 == 0, "1.5.0", "1.4.2"), "tier", iff(i % 3 == 0, "gold", ""))
| project Timestamp, DeviceId, Service, Region, EventType, DurationMs, Status, Value, Properties
.set-or-append ServiceCatalog <|
datatable(Service: string, Team: string, Tier: string, SloMs: long)
[
"checkout", "Commerce", "Tier 1", 400,
"payments", "Commerce", "Tier 1", 400,
"search", "Discovery", "Tier 2", 500,
"catalog", "Discovery", "Tier 2", 600
]
auth is deliberately missing from ServiceCatalog, so you can see what each join kind does with an unmatched key.
1. Filter with where
TelemetryEvents
| where Timestamp > ago(30m)
| where Service == "payments" and Status == "Failed"
TelemetryEvents
| where Timestamp between (ago(2h) .. ago(1h))
| where Region in ("westeurope", "eastus")
| where Service =~ "PAYMENTS"
==is case-sensitive,=~isn’t.inandin~work the same way.betweenincludes both ends.- For text search,
hasmatches whole terms and is faster thancontains, which matches any substring.
2. Shape columns: project, project-away, extend
TelemetryEvents
| where Timestamp > ago(1h)
| project Timestamp, Service, DurationMs, Status
TelemetryEvents
| where Timestamp > ago(1h)
| project-away Properties, Value
| project-rename Svc = Service
TelemetryEvents
| where Timestamp > ago(1h)
| extend DurationSec = DurationMs / 1000.0
| extend IsSlow = DurationMs > 500
Dividing a long by 1000 gives an integer; divide by 1000.0 to keep decimals.
3. Sort and limit: sort, order, top, take
TelemetryEvents
| where Timestamp > ago(15m)
| sort by Timestamp desc, DurationMs asc
TelemetryEvents
| where Timestamp > ago(1h)
| top 10 by DurationMs
TelemetryEvents
| take 20
order by is the same operator as sort by. Both default to descending, and so does top. take returns arbitrary rows: use it to look at the data, never to answer a question.
4. Aggregate with summarize
TelemetryEvents
| where Timestamp > ago(1h)
| summarize
Events = count(),
Failures = countif(Status == "Failed"),
Devices = dcount(DeviceId),
AvgMs = avg(DurationMs),
P95Ms = percentile(DurationMs, 95),
MaxMs = max(DurationMs)
by Service
| extend FailureRate = round(100.0 * Failures / Events, 2)
| order by FailureRate desc
summarizewithoutbyreturns one row; withby, one row per group.dcount()is an estimate, which is fine for monitoring.- Filter aggregated results with
whereaftersummarize(KQL has noHAVING). - Name every aggregate (
Failures = ...); otherwise you get columns likecount_.
5. Time: now(), ago(), datetime literals
TelemetryEvents
| where Timestamp >= startofday(now())
| summarize Events = count() by Service
TelemetryEvents
| where Timestamp between (datetime(2026-11-09 08:00) .. datetime(2026-11-09 09:00))
| summarize Events = count()
TelemetryEvents
| where Timestamp > ago(1h)
| extend LocalTime = datetime_utc_to_local(Timestamp, "Europe/Madrid")
| project Timestamp, LocalTime, Service
ago(1d)is a rolling 24 hours;startofday(now())is since midnight UTC.- Timespan literals:
30s,5m,1h,7d. - Datetimes are stored in UTC. Convert for display, not for filtering.
6. Time windows with bin()
TelemetryEvents
| where Timestamp > ago(3h)
| summarize
Requests = count(),
P95Ms = percentile(DurationMs, 95)
by bin(Timestamp, 5m), Service
| render timechart
TelemetryEvents
| where Timestamp > ago(3h)
| make-series Failures = countif(Status == "Failed") default = 0
on Timestamp from ago(3h) to now() step 5m
by Service
| render timechart
bin(Timestamp, 5m)rounds each timestamp down to a fixed, non-overlapping 5-minute bucket: a tumbling window.summarize ... by bin()returns no row for a bucket with no events.make-seriesfills gaps with a default, which matters for charts and alert thresholds.- Pick the bin size for the question: too small and the chart is noise, too large and a 2-minute outage disappears.
7. Joins: lookup and join
Enrich events with reference data. lookup is designed for a large table plus a small dimension, and keeps unmatched rows (left outer) by default:
TelemetryEvents
| where Timestamp > ago(1h)
| lookup kind=leftouter ServiceCatalog on Service
| summarize Events = count() by Service, Team, Tier
The same with join, stating the kind explicitly:
TelemetryEvents
| where Timestamp > ago(1h)
| summarize P95Ms = percentile(DurationMs, 95) by Service
| join kind=inner ServiceCatalog on Service
| where P95Ms > SloMs
| project Service, Team, P95Ms, SloMs
| Kind | Keeps | Watch out |
|---|---|---|
innerunique (default) |
Matches, after deduplicating left keys | Silently drops duplicate left keys; surprises SQL users |
inner |
Every matching pair | Duplicates multiply rows, as in SQL |
leftouter |
All left rows, matched or not | Unmatched right columns are empty or null |
leftanti |
Left rows with no match | Useful to find events for services missing from the catalog |
rightouter, fullouter, leftsemi, rightanti |
As their names say | Less common in monitoring queries |
Summarize before joining when you can: joining one row per service is cheaper than joining every event.
8. Conditional logic: iff() and case()
TelemetryEvents
| where Timestamp > ago(1h)
| extend LatencyBand = case(
DurationMs < 200, "fast",
DurationMs < 600, "ok",
"slow")
| extend IsError = iff(Status == "Failed", 1, 0)
| summarize Events = count(), Errors = sum(IsError) by Service, LatencyBand
case() evaluates conditions in order and needs a final default value.
9. Missing values
TelemetryEvents
| where Timestamp > ago(1h)
| extend Tier = tostring(Properties.tier)
| summarize
NoTier = countif(isempty(Tier)),
NoValue = countif(isnull(Value))
by Service
TelemetryEvents
| where Timestamp > ago(1h)
| extend Tier = tostring(Properties.tier)
| extend Tier = iff(isempty(Tier), "standard", Tier)
| extend Value = coalesce(Value, 0.0)
stringvalues are never null in KQL, only empty: test strings withisempty()/isnotempty().real,long,datetimeanddynamicvalues can be null: test them withisnull()/isnotnull().coalesce()returns the first non-null argument.
10. Dynamic (JSON) fields and parsing
TelemetryEvents
| where Timestamp > ago(1h)
| extend Version = tostring(Properties.version)
| summarize Failures = countif(Status == "Failed"), Events = count() by Version
datatable(Raw: string)
[
'{"service":"auth","durationMs":812,"status":"Failed"}'
]
| extend Doc = parse_json(Raw)
| project
Service = tostring(Doc.service),
DurationMs = tolong(Doc.durationMs),
Status = tostring(Doc.status)
Values read from a dynamic column are dynamic. Convert them (tostring(), tolong(), todatetime()) before you compare, group or join on them.
11. Latest value per key with arg_max()
TelemetryEvents
| where Timestamp > ago(1h)
| summarize arg_max(Timestamp, Status, DurationMs) by DeviceId
arg_max(Timestamp, *) returns the whole latest row for each key. It is also the usual way to remove duplicate events that arrive more than once.
12. Reuse with let
let window = 30m;
let failing =
TelemetryEvents
| where Timestamp > ago(window)
| summarize Failures = countif(Status == "Failed") by Service
| where Failures > 0;
failing
| lookup kind=leftouter ServiceCatalog on Service
| order by Failures desc
A let statement ends with ;. The query after the last let produces the result.
Exercises
Use the practice dataset from step 0. Write each query before you look at the suggested one.
Exercise 1: the last 30 minutes
Goal: list events from the last 30 minutes, newest first, with only the columns an on-call engineer needs.
TelemetryEvents
| where Timestamp > ago(30m)
| project Timestamp, Service, Region, Status, DurationMs
| sort by Timestamp desc
Interpretation: about 200 rows (one event every 9 seconds). If you see none, the dataset is older than 30 minutes: rerun the .set-or-append command.
Variation: add | take 5 before and after sort. Only one of them reliably shows the five newest events. Why?
Exercise 2: failures by service
Goal: count failures per service in the last hour.
TelemetryEvents
| where Timestamp > ago(1h)
| where Status == "Failed"
| summarize Failures = count() by Service
| order by Failures desc
Interpretation: payments should lead clearly, because the generator fails it about four times more often.
Variation: remove the second where and use countif(Status == "Failed") with count() to show a failure rate instead of a count. Which number would you put on a dashboard?
Exercise 3: 5-minute windows
Goal: show the number of requests per service in 5-minute buckets over the last 2 hours.
TelemetryEvents
| where Timestamp > ago(2h)
| summarize Requests = count() by bin(Timestamp, 5m), Service
| render timechart
Interpretation: fairly flat lines around 6–7 requests per service per bucket. This is a tumbling window: each event is counted in exactly one bucket.
Variation: change 5m to 1m and then 1h. Describe what each size hides.
Exercise 4: top failing devices and services
Goal: find the five device and service pairs with the most failures in the last 6 hours.
TelemetryEvents
| where Timestamp > ago(6h)
| where Status == "Failed"
| summarize Failures = count() by DeviceId, Service
| top 5 by Failures
Interpretation: expect mostly payments rows. In real telemetry, one device dominating the list usually points at the device, not the service.
Variation: return the top 5 by failure rate instead, only for pairs with at least 20 events. Where does the minimum belong in the pipeline?
Exercise 5: join to reference data
Goal: compare each service’s p95 latency with its SLO from ServiceCatalog.
TelemetryEvents
| where Timestamp > ago(1h)
| summarize P95Ms = percentile(DurationMs, 95) by Service
| lookup kind=leftouter ServiceCatalog on Service
| extend OverSlo = P95Ms > SloMs
| project Service, Team, P95Ms, SloMs, OverSlo
Interpretation: only search is over its SLO: the generator adds 400 ms to every search request, so its p95 lands around 700 ms against a 500 ms target. The other services stay near 330 ms. auth appears with an empty team and SLO, because it isn’t in the catalog.
Variation: use join kind=inner and then join kind=leftanti. Which one tells you the catalog is incomplete?
Exercise 6: a calculated field
Goal: classify every request as fast, ok or slow, and count each band per service.
TelemetryEvents
| where Timestamp > ago(1h)
| extend LatencyBand = case(DurationMs < 200, "fast", DurationMs < 600, "ok", "slow")
| summarize Events = count() by Service, LatencyBand
| order by Service asc, LatencyBand asc
Interpretation: search has no fast requests and many slow ones; the other services are split between fast and ok.
Variation: move the band thresholds into let statements, then reuse them in a second query.
Exercise 7: diagnose the wrong time window
Goal: a colleague wants “today’s failures” for a team in Madrid and wrote:
TelemetryEvents
| where Timestamp > ago(1d)
| where Status == "Failed"
| summarize Failures = count()
Interpretation: this counts the last 24 hours, not today, and “today” in Madrid doesn’t start at midnight UTC. In the morning it includes most of yesterday; in the evening it’s close to right, so the bug comes and goes.
A version that means “since midnight in Madrid”:
let madridMidnightUtc = datetime_local_to_utc(startofday(datetime_utc_to_local(now(), "Europe/Madrid")), "Europe/Madrid");
TelemetryEvents
| where Timestamp >= madridMidnightUtc
| where Status == "Failed"
| summarize Failures = count()
Variation: write the same filter for “yesterday in Madrid”, using a start and an end. Check both boundaries: does between include an event at exactly midnight on both sides?
What the result means
- Zero rows: check the time filter first (
ago()against data age, UTC vs local), then case-sensitive string comparisons. - More rows than expected after a join:
kind=innermultiplies duplicates;innerunique(the default) hides them. State the kind and check key uniqueness on the reference side. - Missing time buckets:
summarize ... by bin()doesn’t create buckets with no data. Usemake-serieswhen gaps must show as zero. - A metric jumps when the bin size changes: the bucket is part of the metric. A count per 1 minute and a count per 5 minutes are different numbers.
dcount()differs slightly from an exact count: it’s an estimate by design.
What to look for
- A
where Timestamp > ago(...)near the top of every query on a large table. - An explicit
kind=on everyjoin. - Comparisons on
dynamicfields that aren’t converted withtostring()ortolong(). takeused to produce an answer rather than to peek at data.containson large tables wherehaswould do.- Bin sizes chosen to match the alert or dashboard question, not by habit.
Production notes
- Filter on time first. Eventhouse data is organized by ingestion time, and time filters are what keep queries fast and cheap.
- Summarize before joining and keep the smaller table on the right of
lookup. - Name everything (
Failures = count()): dashboards, Activator rules and downstream queries depend on stable column names. - Keep UTC in storage; convert to local time only for display.
- Save reusable logic as functions in the KQL database instead of copying long queries between dashboards.
- Check the schema before trusting a query written for another environment:
TelemetryEvents | getschema.
Common mistakes
1. Writing KQL in SQL order
project before where works, but long queries become hard to read and easy to get wrong. Filter, then shape, then aggregate, then sort.
2. Expecting sort by to be ascending
KQL sorts descending by default. Write asc or desc every time.
3. Trusting the default join
The default innerunique deduplicates the left side’s keys. Use kind=inner, kind=leftouter or lookup deliberately.
4. Treating ago(1d) as “today”
It’s a rolling 24 hours in UTC. Use startofday() and an explicit time zone when the question is a calendar day.
5. Testing strings with isnull()
Strings are never null in KQL. Use isempty().
6. Comparing a dynamic value without converting it
Properties.version == "1.5.0" compares a dynamic value. tostring(Properties.version) == "1.5.0" says what you mean.
Ce contenu vous a-t-il été utile ?
Que pourrions-nous améliorer ?
Merci — cela m’aide à améliorer Javi Builds.
Restez au courant
Recevez uniquement les nouvelles de Javi Builds qui vous intéressent.
Consultez votre boîte de réception
Je vous ai envoyé un lien de confirmation. Votre abonnement sera actif après confirmation.