In this section

KQL Foundations: parse, extract, Dynamic Columns, bag_keys and mv-expand

Module 0

Introduction

Not all security data arrives in clean columns. Linux and network devices send free text, a sentence per event with the useful values buried inside it. Cloud services send nested data, a column holding a bag of named values or an array of elements, each of which may hold more. KQL has operators for both, and both kinds of unpacking can lose rows or values without an error.

This lesson unpacks the two kinds. It parses SSH log lines from the lab's Linux servers into fields, pulls an address out of a message with a pattern, reads the city out of a sign-in's location, asks for a property that is never there, and turns an array of authentication steps into rows, counting at each step what was lost.

Free textSyslogMessage:a sentencethenparse, extractnamed columnsfrom the textandDynamicLocationDetails:a property bagthenDot path, mv-expanda value, or onerow per element

Two kinds of data that do not arrive in clean columns, and the operators that unpack each.

This is the ground Module 6 builds on. Every query on the page runs in your browser against the same month.

01

Text That Hides Fields

A sentence per event

Syslog is the server family's table for Linux: one row per message a Linux host sent, with the program that sent it and the message as text. The SSH daemon, sshd, writes a sentence for every logon. Here are two of them:

Syslog
| where ProcessName == 'sshd' and SyslogMessage startswith 'Accepted'
| project SyslogMessage
| take 2

The project keeps only the message, and take keeps two rows of it:

SyslogMessage
----------------------------------------------------------------
Accepted password for m.webb from 10.0.1.111 port 49137 ssh2
Accepted password for t.ashworth from 10.0.1.120 port 49359 ssh2

Each message says how the user authenticated, who they were, where they connected from and on which port. None of those is a column.

The table has a column for the message, a column for the program and a column for the host, and the rest of the event is a sentence. sshd wrote 2,411 messages on 8 hosts in the month, of several kinds: logons accepted, passwords refused, sessions opened and closed.

A filter on text like this is possible with the operators of the last lessons: has 'publickey' finds key-based logons. A count by user, or a list of source addresses, is not, because there is no column to group by. The question needs the values out of the sentence and into columns of their own, which is what parse and extract do.

Free text is the oldest form of log data and still one of the most common. Firewalls, proxies, VPN gateways, Linux daemons and many security products send it, and Sentinel often stores it with little structure added. An analyst who can only query columns can only query part of the workspace. Learning to unpack text is learning to read the rest of it.

The messages also vary in a way that matters. The two above share a layout, but a refused password, a closed session and an invalid user each have their own sentence, with the values in different places. Any operator that unpacks the text has to be pointed at one layout at a time, which is why the queries below filter to one kind of message before unpacking it.

The first step with any free-text column is the one this section took: read a few messages before writing anything. The layout of the sentence, which words are fixed and which vary, decides which operator can unpack it and how reliable the result will be.

02

Parsing a Known Layout

parse names the values between fixed words

When a message has a fixed layout, parse can take it apart. Its pattern lists the fixed words in quotes and names a column for each value between them; a star skips anything after the last named value. The accepted-logon messages have exactly such a layout:

Syslog
| where ProcessName == 'sshd' and SyslogMessage startswith 'Accepted'
| parse SyslogMessage with 'Accepted ' Method ' for ' User ' from ' SourceIP
    ' port ' Port:int *
| summarize Logons = count(), Users = dcount(User), Sources = dcount(SourceIP)
    by Method

The pattern creates four columns, Method, User, SourceIP and Port, and the summarize groups by the first:

Method     Logons  Users  Sources
---------  ------  -----  -------
password   280     3      78
publickey  190     3      68

470 accepted SSH logons: 280 by password and 190 by public key, each from 3 users. The method is now a column, so a question such as who still uses passwords to reach Linux servers is one summarize away. Port was typed as an int in the pattern, so it arrived as a number rather than text.

The where above kept only messages beginning with Accepted before parsing. That mattered, and the next query shows why by parsing every sshd message with the same pattern:

Syslog
| where ProcessName == 'sshd'
| parse SyslogMessage with 'Accepted ' Method ' for ' User ' from ' SourceIP
    ' port ' Port:int *
| summarize Messages = count(), Parsed = countif(isnotempty(Method)),
    NotParsed = countif(isempty(Method))

It counts the messages the pattern could read and the ones it could not:

Messages  Parsed  NotParsed
--------  ------  ---------
2411      470     1941

Of 2,411 messages, 470 matched the pattern and 1,941 did not. parse does not drop the rows it cannot read; it keeps them with empty values in every column it was asked to create.

A summarize by Method on that result would show a large group with an empty method, and a count of logons that forgot to exclude it would be wrong by 1,941. parse-where does the same job and drops the rows that do not match, which is the right choice when only matching rows matter.

Keeping unmatched rows is sometimes exactly what is wanted. A query that parses one layout and then counts the rows it could not read is a cheap way to discover the layouts it did not know about: read a few of the unparsed messages and each new sentence shape becomes visible.

That is how a parser for a new log source is usually built, one layout at a time, until the unparsed count is close to zero.

03

Pulling Out a Pattern

extract with a regular expression

parse needs fixed words around a value. When a value can appear anywhere, or the words around it vary, extract() pulls out whatever matches a regular expression. Refused SSH passwords are worth counting by where they came from, and the address sits after the word from in each message:

Syslog
| where ProcessName == 'sshd' and SyslogMessage has 'Failed password'
| extend SourceIP = extract(@'from (\d+\.\d+\.\d+\.\d+)', 1, SyslogMessage)
| summarize Attempts = count(), Sources = dcount(SourceIP), Hosts = dcount(Computer),
    NoAddress = countif(isempty(SourceIP))

The regex captures four groups of digits separated by dots after the word from, and the summarize counts what it found:

Attempts  Sources  Hosts  NoAddress
--------  -------  -----  ---------
1001      4        8      0

1,001 refused passwords against 8 hosts from 4 source addresses, and every message yielded an address, so the extract missed nothing here. That last count is the one to keep.

An extract that fails returns an empty string, and a summarize by the extracted value would put every failure into one empty group that is easy to overlook; counting the empty values is the check that the pattern matched what it was meant to.

Regular expressions are powerful and easy to get slightly wrong. This one accepts any four groups of digits, including impossible addresses such as 999.1.1.1, and it would miss an IPv6 address entirely. For counting sources in a known log format that is acceptable. For extracting indicators from arbitrary text, Module 6 uses tighter patterns and the purpose-built functions KQL provides for addresses, which also validate what they find.

The counts also say something about the month. A few sources making a thousand refused attempts against several hosts is the familiar background of any server reachable from the internet, and the lab includes it as ordinary noise. Module 6 returns to it with a sharper question: which of those attempts used an account that exists, and did any of them succeed.

The choice between parse and extract is about how much you know of the text. parse is faster to read and stricter, which is good when the layout is fixed. extract is more flexible and less strict, which is good when it is not, and needs the empty-value check more.

04

Reading Nested Data

A dot path into a dynamic column

The sign-in tables hold much of their detail in dynamic columns. LocationDetails is a property bag, a set of named values, with the country, state and city of each sign-in inside it. A field in a bag is read with a dot path, and tostring() turns the result into an ordinary string:

SigninLogs
| extend City = tostring(LocationDetails.city)
| summarize SignIns = count() by City
| top 5 by SignIns

The city comes out of the bag as a string column, and the summarize counts sign-ins by it:

City        SignIns
----------  -------
Manchester  7486
London      293
Frankfurt   116
Stockholm   99
Dublin      99

7,486 of 8,203 sign-ins came from Manchester, far ahead of any other city. The dot path reached inside the column without any parsing, because the data was already structured; it only had to be addressed.

The tostring() matters more than it looks. A value read out of a dynamic column is itself dynamic, and dynamic values behave differently in comparisons, joins and summarize groups from the strings they print as. Some operators refuse them; others treat them in ways that differ from a string.

Converting each value to its real type as soon as it is extracted, tostring() for text, toint() or tolong() for numbers, todatetime() for times, avoids a class of problems that show up only later in a query.

Dot paths go as deep as the data does. A bag inside a bag is read with two dots, and an element of an array inside a bag with a bracket and an index, such as AuthenticationDetails[0].authenticationMethod for the first step of a sign-in.

The path is written exactly as the structure is shaped, so reading one raw value of the column first, as the first section did with text, shows what path to write.

Sometimes nested data arrives as a string holding JSON rather than as a dynamic column, especially from custom sources. parse_json() turns such a string into a dynamic value, after which the dot path works the same way. Checking the column's type in getschema first, as the first lesson did, says which case you are in.

05

A Property That Is Not There

Missing fields return empty, silently

A property bag holds whatever properties its source put into it, and different rows can hold different ones. Asking for a property a row does not have is not an error; it returns an empty value. Ask the location bag for two properties, one that exists and one that does not:

SigninLogs
| summarize SignIns = count(),
    WithState = countif(isnotempty(tostring(LocationDetails.state))),
    WithStreet = countif(isnotempty(tostring(LocationDetails.street)))

The query counts the sign-ins whose bag holds a state, and those whose bag holds a street:

SignIns  WithState  WithStreet
-------  ---------  ----------
8203     8203       0

Every sign-in has a state, and none has a street. LocationDetails in the lab holds exactly these keys: ["countryOrRegion","state","city"]. A query that filtered on the street would return nothing, and nothing in the result would say that the property never existed, as opposed to existing and never matching. That is the same silent failure as the case-sensitive filter in the second lesson, one level down.

bag_keys() lists the properties a bag holds, which makes it the getschema of a dynamic column. Summarized across a table, as it was to get the list above, it shows every shape the column takes.

In real data the shapes vary: a vendor adds a property in a new version, a service omits one for some kinds of event, a field moves from one bag to another. Module 6's lesson on property bags and changing schemas is about writing queries that survive those changes, and the habit starts here: list the keys before relying on one.

Property names inside a bag are also case-sensitive, like column names. LocationDetails.City and LocationDetails.city are different paths, and only one of them exists here, so a path with the wrong case is just another property that is not there, returning empty values without complaint.

The same applies to device details, authentication details and every other bag in the sign-in tables. The second lesson found that most sign-ins in the lab have no operating system in DeviceDetail; a property that is present and empty and a property that is absent look the same to a dot path, and both need checking.

06

One Row per Element

mv-expand turns an array into rows

AuthenticationDetails is not a bag but an array: one element per step a sign-in went through, each element a bag of its own with the method, the time and whether the step succeeded. Counting methods needs one row per step, and mv-expand produces exactly that, copying the rest of the row onto each element:

SigninLogs
| mv-expand Step = AuthenticationDetails
| extend Method = tostring(Step.authenticationMethod)
| summarize Steps = count() by Method
| order by Steps desc

Each element becomes a row with the step in a new column, and the method is read out of each step:

Method                    Steps
------------------------  -----
PhoneAppNotification      7040
No authentication method  797
FIDO2                     339
Password                  15
Mobile app notification   15
SatisfiedByClaimInToken   3

7,040 steps used PhoneAppNotification, by far the commonest method, and the rest are spread across a few others. The total is 8,209 steps from 8,203 sign-ins, and that difference is worth explaining before trusting any count taken after the expansion.

After mv-expand a row is a step, not a sign-in, exactly as a row after summarize is a group, and a count of rows answers a different question from the one it answered before.

Two things change the row count. A sign-in whose array has two elements becomes two rows, so steps outnumber sign-ins. A sign-in whose array is empty becomes no row at all: Microsoft documents that mv-expand produces no record for an empty array. Both happen in the same query and partly cancel, so the net difference of 6 hides two movements in opposite directions.

mv-expand also copies every other column of the row onto each element it produces, so a two-step sign-in becomes two rows with the same user, time, address and result. Any later count of users, addresses or failures counts that sign-in twice.

Summarizing back to one row per sign-in, with dcount() on its identifier or a summarize by it, is the usual way to return to the original meaning once the elements have been examined.

The way to see them is to count array lengths before expanding, which the next section does. It is the same reconciliation every lesson in this module has used, applied to the one operator that changes the number of rows without filtering or grouping.

07

Inside an Array

Count lengths before expanding

array_length() returns the number of elements in an array, so grouping by it shows how many sign-ins have each number of steps:

SigninLogs
| extend Steps = array_length(AuthenticationDetails)
| summarize SignIns = count() by Steps
| order by Steps asc

Each length becomes a group, from sign-ins with no steps to those with two:

Steps  SignIns
-----  -------
0      9
1      8179
2      15

8,179 sign-ins have one step, 15 have two and 9 have none. Now the expansion reconciles exactly: 8,179 rows from the one-step sign-ins, 30 from the two-step ones and none from the empty arrays make 8,209.

The 9 empty-array sign-ins are the ones a count after mv-expand loses, and whatever made their arrays empty is now a question worth asking rather than a gap nobody noticed. Choosing the right tool for a value buried in text or nested data comes down to a short climb:

Getting a value out of text or nested data
1Fixed words around the value?
a log line with a known layoutparse, parse-where
2Only a pattern?
an address or number anywhere in the textextract() with a regex
3A field in a dynamic column?
a property bagdot path, then tostring()
4JSON held in a string?
nested data typed as textparse_json(), then a dot path
5An array of elements?
one row per elementmv-expand, or array functions
Use the most specific tool the data allows, and count what it failed to read.

The bottom rung is where rows change number, and the top rungs are where values go missing. Each of the three mistakes below happened on this page, and each was caught by a count:

Three unpacking mistakes
parse drops the rows it cannot read.
It keeps them with empty columns: 1,941 of 2,411 here.parse-where
A missing property raises an error.
LocationDetails.street returns empty on every row, silently.check bag_keys()
mv-expand keeps every row.
The 9 sign-ins with an empty array produce no row at all.array_length() first
Every unpacking step can lose rows or values without saying so; count before and after.

The two-step sign-ins are worth a look of their own, because a sign-in that needed a password and then an app notification is a different event from one that needed only a notification. Grouping the methods inside each array, rather than expanding them, would show which combinations occur, and the rare combinations are the ones to read first.

Arrays can also be worked on without expanding them. array_length(), set_has_element() and the other array functions answer questions about an array in its row, which keeps the row a sign-in. Expanding is right when each element needs to be counted, filtered or joined on its own; when the question is about the whole array, working inside it avoids changing what a row means.

What Module 6 builds

Module 6

6.1 to 6.4

pulling fields out of free text, text that varies, matching patterns, indicators buried in text

6.5 to 6.6

nested data, property bags and changing schemas

6.7 to 6.8

one row per element, working inside an array

Module 6 builds on this lesson one form of data at a time: free text, text that varies, patterns, indicators in text, nested data, property bags, one row per element and working inside an array.

The habits to carry into it: read a few raw values first, parse fixed layouts and extract patterns, count what an unpacking step failed to read, convert dynamic values to real types, list a bag's keys before relying on one, and count array lengths before expanding.

Do

Practice

Reconcile the expansion
prepared evidence

The prepared evidence below is the sign-in table before and after mv-expand, with the number of sign-ins at each array length. Find the sign-ins that produced no row, the sign-ins that produced two, and the arithmetic that ties the counts together. Try each question before opening its answer, and say which row of the table decided it.

The two rows at the top are the before and after of the expansion. The three below explain the difference, and the questions ask you to say which of them adds rows, which removes them and how the three combine. Getting that arithmetic right on five numbers is the same skill as getting it right on a real table of millions, where nobody lays the numbers out for you.

The table is the reconciliation itself, laid out as five numbers. Nothing in it needs a query, but every question has an answer that comes from reading two or three of the numbers together rather than any one alone, which is exactly how an analyst checks an expansion in practice.

Then run the queries on this page and change them. Add LocationDetails.state to the city query and group by both, then replace mv-expand with a where on array_length(AuthenticationDetails) == 2 and see the two-step sign-ins as whole rows.

Free text and nested data both need unpacking before they can be grouped or joined. Parse fixed layouts, extract patterns, read bags with a dot path and convert the type, list a bag's keys before trusting one, and count array lengths before expanding. Every unpacking step can lose rows or values silently, and a count before and after is what catches it.

The next lesson names things: values and results with let, a function of your own, a list kept outside the query, and address ranges.