Reading width
Wide uses the full column for everything, text, diagrams, code, and exercises. Narrow keeps the standard reading width.
Text size
Scales the body text. Headings and code blocks keep their size.
In this section
KQL Foundations: parse, extract, Dynamic Columns, bag_keys and mv-expand
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.
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.
Text That Hides Fields
A sentence per eventSyslog 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.
Parsing a Known Layout
parse names the values between fixed wordsWhen 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.
Pulling Out a Pattern
extract with a regular expressionparse 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.
Reading Nested Data
A dot path into a dynamic columnThe 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.
A Property That Is Not There
Missing fields return empty, silentlyA 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.
One Row per Element
mv-expand turns an array into rowsAuthenticationDetails 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.
Inside an Array
Count lengths before expandingarray_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:
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:
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 6Module 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.
Practice
Reconcile the expansion prepared evidenceThe 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.