Lesson 24.3 · Azure III: Monitor, KQL & Cost · 18 min read

KQL 1: pipes, filters and strings

Q: What is the difference between has and contains?

has looks for a whole term: KQL splits text into terms at non-alphanumeric characters and indexes them, so has "payments" matches "POST /api/payments/refund" quickly using the index. contains looks for any substring and scans every row, so contains "stream" matches "upstream" but has "stream" doesn't. Both are case-insensitive. Use has by default and contains only when you really need part of a word.

Q: Why does take give different rows each time?

Because take returns arbitrary rows, in no defined order, whatever the engine finds first. It's a quick way to peek at a table's shape. For "the latest N", use top N by TimeGenerated desc, or sort by TimeGenerated desc take N. And put take at the end: take 10 where ... filters only those ten random rows, which almost never is what you meant.

Q: Why does ResultCode >= 500 fail?

Because ResultCode in AppRequests is a string, and comparing a string with a number gives "Cannot compare values of types string and long". Use ResultCode startswith "5" or toint(ResultCode) = 500. When a filter behaves strangely, AppRequests getschema shows every column's type. Types differ between tables, so don't assume a column called status or code is numeric.

Q: Write a KQL query to find the error logs from one namespace in the last 30 minutes.

A table, then operators separated by , like a bash pipeline of rows. What makes it right: - Time first - the workspace stores data in time chunks; a time filter skips most of it. Without one the query scans the whole retention. - Cheap, selective filters next (== on a column), project early to keep rows narrow. - String operators: == is case-sensitive, =~ is not. has matches a whole term using the index - the default choice; contains scans for any substring. has "status=5" finds nothing, because the terms are status and 503. - Types: ResultCode in AppRequests is a string - startswith "5" or toint(), not = 500. - take returns arbitrary rows; "latest N" is top N by TimeGenerated.

In plain words

Imagine sorting a giant box of LEGO. First you tip out only the bricks from today's set, then keep only the red ones, then only the ones with four studs, then line them up by size and look at the first twenty. Each step takes the pile from the step before and makes it smaller or tidier.

KQL works like that. You start with a table like ContainerLogV2, then pipe it through operators: where TimeGenerated > ago(30m), where PodNamespace == "shop", where LogMessage has "upstream", project the columns you need, take 20. Time first, cheapest filters next. The tricky parts are the string rules: == is case-sensitive, has matches whole words using an index, contains scans for any substring, and ResultCode in AppRequests is a string, not a number.

The workspace holds millions of rows. "Show me payments' failed requests in the last hour, slowest first" is a one-line question - if you can say it in KQL. This lesson is the everyday half of KQL: picking rows and columns.

What you need to know already: 24.1 (workspace, tables, running a query with az monitor log-analytics query), Ch 6 (pipes and quoting in bash), Ch 7 (grep and regular expressions), Ch 8-9 (HTTP status codes: 5xx = server error).

The shape of a query

KQL reads left to right like a bash pipeline (Ch 6): a table, then operators separated by |. Each operator takes a table in and hands a (smaller, or reshaped) table on - where bash pipes pass lines of text, KQL pipes pass rows with named columns.

The example uses AppRequests, one row per HTTP request an app served. Its columns: TimeGenerated (when), AppRoleName (which service), Name (method and path, e.g. POST /api/pay), ResultCode (HTTP status), Success (true/false), DurationMs (how long it took, in milliseconds).

AppRequests
| where TimeGenerated > ago(1h)
| where AppRoleName == "payments-api" and Success == false
| project TimeGenerated, Name, ResultCode, DurationMs
| order by TimeGenerated desc
| take 20

Read it line by line: from AppRequests, keep the last hour, keep payments-api's failed requests, keep only four columns, newest first, show 20.

The operators you will use daily

Each line: the operator, what it does, an example.

where    filter rows                     where ResultCode startswith "5"
project  choose / rename / compute cols  project TimeGenerated, code = ResultCode
extend   add computed columns            extend seconds = DurationMs / 1000.0
take     any N rows (no order!)          take 10          (limit is a synonym)
count    one row, one number             count
distinct unique combinations            distinct AppRoleName, ResultCode
sort by  order rows (desc by default)    sort by DurationMs desc    (order by = same)
top      sort + take in one              top 5 by DurationMs
getschema  columns and types             KubeEvents | getschema

take returns arbitrary rows - for "the latest N" you need top N by TimeGenerated or sort by TimeGenerated desc | take N.

Time first

Filter on TimeGenerated first. The workspace stores data in chunks by time (called shards), and a time filter lets the engine skip whole chunks; a query without it scans everything kept (the entire retention, 24.1). Portal and alerts add a time range, but write it yourself anyway.

| where TimeGenerated > ago(30m)
| where TimeGenerated between (datetime(2026-09-24 09:00) .. datetime(2026-09-24 09:30))
| where TimeGenerated > now(-2h)

A literal is a value written straight into the query. Two kinds for time:

a datetime - a point in time: datetime(2026-09-24T09:15:00Z) (the Z means UTC)
a timespan - a length of time: 30s 5m 2h 1d 250ms

now() is the current time, ago(5m) is now() - 5m, and between (a .. b) keeps values from a to b inclusive. Datetime minus datetime is a timespan.

Strings: has, contains, and the case rules

==  !=               exact, case-SENSITIVE
=~  !~               exact, case-insensitive
has  !has            a whole TERM, case-insensitive, uses the index   <- default choice
contains  !contains  any substring, case-insensitive, scans           <- when you must
startswith endswith  case-insensitive
in ("a","b")         exact list;  in~ (...) case-insensitive
has_any ("a","b")    any of several terms
matches regex @"..." regular expression

Case-sensitive means "Cart" and "cart" are different.

has splits text into terms (words) at every character that is not a letter or digit, and looks the term up in an index - a lookup list the workspace builds of which rows contain which term, like the index at the back of a book. LogMessage has "upstream" is fast; contains "upstream" scans every row. They differ in results too:

"upstream connect error"     has "stream"   -> false   contains "stream" -> true
"POST /api/payments/refund"  has "payments" -> true    (terms: POST, api, payments, refund)

Use has unless you really need a substring inside a word.

Strings go in double or single quotes. @"C:\path" is a verbatim string: the backslash is just a character, not an escape - useful for regular expressions (Ch 7): @"duration_ms=(\d+)".

Comparing numbers and empty values

isnotempty(x) keeps rows where column x has a value (it is neither empty nor missing - a missing value is called null).

| where DurationMs between (1000 .. 5000)      // inclusive
| where ResultCode in ("502", "503", "504")    // ResultCode is a string in AppRequests!
| where isnotempty(PodIp)

ResultCode in AppRequests is a string: ResultCode >= 500 fails with "Cannot compare values of types string and long". Use startswith "5" or toint(ResultCode) >= 500. Check types with getschema when a filter behaves oddly.

let

let app = "payments-api";
let window = 30m;
AppRequests
| where TimeGenerated > ago(window) and AppRoleName == app
| count

let gives a value a name (like a bash variable) for the rest of the query - later it can name a whole table too (24.9). Each let statement ends with ;; the last statement must be the query itself.

Reading an error

ERROR: (BadArgumentError) The request had some invalid properties
Inner error: {
    "code": "SemanticError",
    "innererror": {
        "code": "SEM0100",
        "message": "'where' operator: Failed to resolve scalar expression named 'Foo'"
    }
}

A scalar expression is anything that gives one value per row - a column name, 1 + 2, ago(1h). So the message says "there is no column called Foo".

SemanticError / SEM0100: a column that does not exist (typo, wrong table, wrong case - column names are case-sensitive). SyntaxError / SYN0002: the parser gave up at the position it names.

Worked: narrowing down, one pipe at a time

ContainerLogV2
| where TimeGenerated > ago(30m)                 // 1. time
| where PodNamespace == "shop"                   // 2. the cheap exact filter
| where LogLevel == "error"                      // 3. another exact filter
| where LogMessage has "upstream"                // 4. the indexed term
| project TimeGenerated, PodName, LogMessage     // 5. only the columns you read
| take 20

Put the cheapest, most selective filters first, and project early when rows are wide - the engine then moves less data through the rest of the pipe.

Common mistakes, shown

where AppRoleName == "Payments-API"      -> 0 rows: == is case-sensitive; use =~
where ResultCode == 500                  -> error: ResultCode is a string
where LogMessage has "status=5"          -> 0 rows: has matches whole terms ("status", "503")
where TimeGenerated > "2026-09-24"       -> compare with datetime(2026-09-24), not a string
| take 10 | where ...                    -> filters only 10 arbitrary rows

The last one is the sneakiest: operators run in order, so take before where samples first and filters the sample.

What you can now do

Write a query that filters by time, exact value, term and range, and keeps only the columns you need.
Pick between ==, =~, has and contains, and know why has is the default.
Read a SemanticError and find the misspelled or wrongly-typed column.

Why it helps

Every incident query starts with these operators, and the difference between a query that answers in two seconds and one that times out is usually the order and the choice of has versus contains. Filtering on TimeGenerated first lets the engine skip whole shards; forgetting it scans the entire retention.

The silent failures matter even more, because a query that returns zero rows looks like good news. AppRoleName == "Payments-API" misses because of case, has "status=5" misses because has matches whole terms, and take 10 | where filters a random sample. Knowing these means you trust your results, and when an error does appear, SEM0100 for an unknown column or SYN0002 for a syntax problem, you know exactly where to look.

FAQ

What is the difference between has and contains?

has looks for a whole term: KQL splits text into terms at non-alphanumeric characters and indexes them, so has "payments" matches "POST /api/payments/refund" quickly using the index. contains looks for any substring and scans every row, so contains "stream" matches "upstream" but has "stream" doesn't. Both are case-insensitive. Use has by default and contains only when you really need part of a word.

Why does take give different rows each time?

Because take returns arbitrary rows, in no defined order, whatever the engine finds first. It's a quick way to peek at a table's shape. For "the latest N", use top N by TimeGenerated desc, or sort by TimeGenerated desc | take N. And put take at the end: take 10 | where ... filters only those ten random rows, which almost never is what you meant.

Why does ResultCode >= 500 fail?

Because ResultCode in AppRequests is a string, and comparing a string with a number gives "Cannot compare values of types string and long". Use ResultCode startswith "5" or toint(ResultCode) >= 500. When a filter behaves strangely, AppRequests | getschema shows every column's type. Types differ between tables, so don't assume a column called status or code is numeric.

Are column names case-sensitive?

Yes. Column names, table names and function names are case-sensitive in KQL, so timegenerated or Podname gives SemanticError with SEM0100: Failed to resolve scalar expression. String comparison with == is also case-sensitive; use =~ for case-insensitive equality, and in~ for lists. has, contains, startswith and endswith are case-insensitive by default, with _cs variants when you need case sensitivity.

Why filter on TimeGenerated first?

Data is stored in shards by ingestion time, and a time filter lets the engine skip every shard outside the window. A query without one scans the whole retention period, which is slow, may time out and is expensive in terms of workspace resources. The portal and alerts add a time range, but writing where TimeGenerated > ago(30m) as the first line makes the query correct and fast wherever it runs, including the CLI.

In an interview Mid

Write a KQL query to find the error logs from one namespace in the last 30 minutes.

ContainerLogV2
| where TimeGenerated > ago(30m)
| where PodNamespace == "shop"
| where LogLevel == "error"
| project TimeGenerated, PodName, LogMessage
| order by TimeGenerated desc

A table, then operators separated by |, like a bash pipeline of rows.

What makes it right:

Time first - the workspace stores data in time chunks; a time filter skips most of it. Without one the query scans the whole retention.
Cheap, selective filters next (== on a column), project early to keep rows narrow.
String operators: == is case-sensitive, =~ is not. has matches a whole term using the index - the default choice; contains scans for any substring. has "status=5" finds nothing, because the terms are status and 503.
Types: ResultCode in AppRequests is a string - startswith "5" or toint(), not >= 500.
take returns arbitrary rows; "latest N" is top N by TimeGenerated.

Also asked: A KQL query returns zero rows but you are sure the data exists. How do you debug it? · What is the difference between has and contains in KQL? · How do you write KQL queries that perform well on a large workspace?

Practise this lesson in the terminal Free, in your browser - a real Ubuntu terminal to try it in, with missions that check your work.