OnCallReady

Lesson 24.11 · Azure III: Monitor, KQL & Cost · 14 min read

KQL 4: getting fields out of text and JSON

In plain words

Imagine a pile of postcards where people wrote their name, age and town in one sentence: "I am Ana, 9, from Cluj." To make a table, you need to pick out the pieces. If everyone wrote the exact same sentence shape, you can use a stencil with holes in the right places. If the sentences vary, you look for patterns like "a number after a comma" instead. And if someone filled in a form with boxes already, you just read the boxes.

That's getting fields out of logs in KQL. parse is the stencil for a fixed shape: parse LogMessage with * "status=" status:int " duration_ms=" ms:long. extract finds one field with a regex, like @"duration_ms=(\d+)". And JSON is the pre-filled form: in ContainerLogV2, a JSON log line is already dynamic, so LogMessage.level just works.

Application logs arrive as one long line of text. You cannot take a p95 of "...duration_ms=118..." - you need 118 as a number in its own column first. This lesson is how to cut fields out of text and out of JSON, the KQL version of what awk and jq did for you in Ch 7.

What you need to know already: 24.3 (where, extend, verbatim strings), 24.5 (summarize, percentiles), Ch 7 (regular expressions, awk fields, jq paths), 22.3 (JSON).

A payments log line:

2026-09-24T09:21:44.120Z INFO  [payments-api] POST /api/payments/authorize status=200 duration_ms=118 trace=5c1e0a97d3f24b1c

Time, level, service, method and path, then key=value fields. trace= is a request ID: a random ID the app puts on every log line that belongs to the same request, so you can find all of them later.

parse: when the shape is fixed

ContainerLogV2
| where PodNamespace == "payments"
| parse LogMessage with * "status=" status:int " duration_ms=" ms:long " trace=" trace
| where isnotempty(trace)
| summarize avg(ms), max(ms) by status

parse <column> with <pattern> walks the text left to right. The pattern is made of literals (the exact text in quotes that must be there, like "status=") and captures (name:type - "whatever comes next, up to the following literal, goes into a new column called name, converted to type"). * means "skip anything". int/long are whole numbers, real has decimals; no type means string. Lines that do not match get empty/null captures - filter them out. Cheap and readable when every line has the same shape.

extract: one field, by regex

ContainerLogV2
| extend ms = toint(extract(@"duration_ms=(\d+)", 1, tostring(LogMessage)))
| where isnotnull(ms)
| summarize percentiles(ms, 50, 95, 99) by PodNamespace

extract(regex, group, text) runs a regular expression (Ch 7) over the text and returns the part inside the Nth pair of parentheses (the capture group; here group 1 is (\d+), the digits) as a string, or empty if nothing matched; convert with toint(), todouble(). Use verbatim strings (@"...") so the backslashes survive. Regex scans every row - filter with has first.

LogMessage is dynamic

dynamic is KQL's type for a JSON value - an object, an array, or a plain value. In ContainerLogV2, LogMessage is dynamic: if the container logged JSON, it is already parsed and you can walk into it with a dot, like a jq path (Ch 7):

ContainerLogV2 | where LogMessage.level == "error" | project LogMessage.msg

For plain text it is just a string, and string functions need tostring(LogMessage). Structured JSON logging from the app is worth more than any clever regex.

JSON in columns

Many columns hold JSON as strings - KubeNodeInventory.Labels, AzureActivity.Properties, ContainerLastStatus:

KubeNodeInventory
| summarize arg_max(TimeGenerated, Labels) by Computer
| extend pool = tostring(parse_json(Labels)[0].agentpool)

AzureActivity
| extend role = tostring(parse_json(Properties).roleDefinitionName)

parse_json() turns the string into a dynamic value; [0], .key walk into it; wrap the result in tostring() before grouping or comparing.

mv-expand (multi-value expand) turns an array into one row per element. print makes a one-row table from values you type, handy for trying things:

print tags = dynamic(["a", "b", "c"]) | mv-expand tags

Which to use

has / contains       find the lines
parse                fixed layout, several fields at once
extract              one field, flexible layout
parse_json + .field  JSON strings

Filter before you parse: where LogMessage has "duration_ms" then extract. A regex over every log line of the last day is the query that times out.

Worked: an access log line

10.0.4.17 - - [24/Sep/2026:09:21:44 +0000] "POST /api/payments/refund HTTP/1.1" 502 157 "-" "okhttp/4.12" 30.004
ContainerLogV2
| where ContainerName == "nginx"
| parse LogMessage with ip " - - [" ts "] \"" method " " path " " proto "\" " status:int " " bytes:long " \"" * "\" \"" ua "\" " rt:real
| where status >= 500
| summarize n = count(), p95 = percentile(rt, 95) by path, status

(Chapter 7 did the same with awk; the pattern thinking is identical.) Note the escaped quotes inside the pattern, and that status:int lets you compare numerically afterwards.

When parse returns nothing

Every capture empty means the literal parts did not match: an extra space, a different quote character, a field that is sometimes missing. Test on one row with take 1, and prefer extract for fields that move around.

What you can now do

Why it helps

Application logs rarely arrive with the fields you need as columns. "What's the p95 duration by endpoint?", "which status codes did nginx return?" and "which request IDs failed?" all require pulling values out of text first. Being able to do that on the spot during an incident, instead of asking developers to add metrics, is a large part of being fast on call.

It also gives you a strong argument in design discussions: structured JSON logs make every field queryable without regex, cost less CPU to query, and survive format changes. And knowing to filter with has before parsing prevents the classic query that runs a regex over a whole day of logs and times out, right when everyone is waiting for the answer.

FAQ

When do I use parse and when extract?

parse when every line has the same layout: one pattern pulls several fields at once, with types, and is cheap and readable. extract when you need one field and its position or surroundings vary, since a regex can find it anywhere in the line. For JSON strings use parse_json and dot into the result. If parse returns empty captures on most rows, the layout isn't as fixed as you thought; switch to extract.

Why is everything empty after parse?

Because the literal parts of the pattern didn't match the text: an extra space, a different quote character, a field that's sometimes missing, or a different order. parse doesn't partially match; the captures are empty or null. Test the pattern on one row with take 1 and compare it character by character. Use escaped quotes inside the pattern, and filter out non-matching rows with isnotempty() afterwards.

What does dynamic mean for LogMessage?

In ContainerLogV2, LogMessage has type dynamic. If the container logged a JSON object, it's already parsed, and you can use LogMessage.level or LogMessage.msg directly without parse_json. If it logged plain text, it's a string inside a dynamic value, so string functions like extract need tostring(LogMessage). That's one of the concrete payoffs of structured logging from the application.

How do I read JSON stored in a string column?

parse_json() turns the string into a dynamic value, and then .key and [index] walk into it: tostring(parse_json(Labels)[0].agentpool) for a node's pool, tostring(parse_json(Properties).roleDefinitionName) in AzureActivity. Wrap the result in tostring(), toint() or similar before grouping or comparing, because dynamic values compare and group awkwardly. mv-expand turns an array into one row per element.

Why should I filter before parsing?

Because parsing, and especially regex, is CPU-expensive per row, and log tables are huge. Running extract over every log line of a day can time out. First narrow with time, namespace or container equality and has on a term that appears in the lines you want, like where LogMessage has "duration_ms", then parse the few rows that remain. The result is the same and the query is orders of magnitude cheaper.

In an interview Mid

How would you extract a numeric field from log lines in KQL and aggregate it?

Filter first, then cut the field out, typed, then aggregate:

ContainerLogV2
| where TimeGenerated > ago(1h) and LogMessage has "duration_ms"
| extend ms = toint(extract(@"duration_ms=(\d+)", 1, tostring(LogMessage)))
| where isnotnull(ms)
| summarize percentiles(ms, 50, 95, 99) by PodNamespace

Regex over every line of a day is the query that times out - has first. And structured JSON logging beats any clever regex.

Also asked: Why is structured logging valuable for operations, and how does it change your queries? · When would you use parse instead of extract in KQL? · How do you read a value out of a JSON column in KQL?

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