6.4 Work with String Data in KQL

Work with String Data in KQL

Introduction

Security log data is not always neatly structured. Email headers arrive as raw text strings. Syslog messages contain IP addresses buried in free-text sentences. JSON fields nest useful data three levels deep. User principal names contain both the username and the domain in a single field. To investigate effectively, you need to extract specific values from these messy formats and turn them into queryable columns.

This subsection teaches you the string manipulation tools in KQL — the operators that split, extract, parse, and transform text data into structured, filterable columns. These skills are used constantly in Modules 8 (connecting log sources that produce unstructured data), 9 (building analytics rules that extract IOCs from alerts), and 11-13 (investigation queries that parse evidence from complex log entries).

Here is what you will learn:

• split() — breaking a string into parts at a delimiter (extracting domains from emails, paths from URLs)
• extract() — pulling values using regular expressions (extracting IPs from free text, patterns from URLs)
• parse — pattern-based extraction that creates named columns from text (parsing structured messages without regex)
• substring() — extracting a portion of a string by position
• String functions — tolower(), toupper(), strlen(), strcat(), replace_string(), trim()
• Working with JSON/dynamic columns — navigating nested structures with dot notation and tostring()

split() — breaking strings at a delimiter

What it is

split() takes a string and a delimiter character, and returns an array of parts. You access individual parts using array indexing ([0] for the first part, [1] for the second, etc.).

Why it matters

User principal names are formatted as username@domain. URLs are formatted as protocol://domain/path. Email headers contain key-value pairs separated by specific characters. split() extracts the piece you need.

1
2
3
4
5
6

SigninLogs
| where TimeGenerated > ago(7d)
| extend Username = tostring(split(UserPrincipalName, "@")[0])
| extend Domain = tostring(split(UserPrincipalName, "@")[1])
| project TimeGenerated, UserPrincipalName, Username, Domain
| take 5

extract() — regex-based extraction

What it is

extract() uses a regular expression to find and return a matching pattern from a string. It is more powerful than split() because it can match complex patterns, not just delimiter positions.

Why it matters

Syslog messages, alert descriptions, and free-text log fields contain embedded data that you cannot extract with split(). An IP address buried in a sentence like “Connection from 198.51.100.44 denied by firewall rule FW-001” requires pattern matching to pull out.

1
2
3
4
5
6
7

// Extract an IPv4 address from any text field
Syslog
| where TimeGenerated > ago(24h)
| extend ExtractedIP = extract(@"(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})", 1, SyslogMessage)
| where isnotempty(ExtractedIP)
| project TimeGenerated, ExtractedIP, SyslogMessage
| take 10

The regex pattern \d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3} matches any IPv4 address. The 1 in extract() means “return the first capture group” (the content inside parentheses).

parse — pattern-based extraction without regex

What it is

parse extracts values from a string using a text pattern with named placeholders. It is simpler than extract() because you define the pattern using the actual text structure, not regular expressions.

Why it matters

Many log messages follow a consistent structure: “User john.smith from IP 198.51.100.44 failed authentication at 14:32.” If the structure is consistent, parse extracts the values without regex.

1
2
3
4
5
6
7

// Parse structured Syslog messages
Syslog
| where TimeGenerated > ago(24h)
| where SyslogMessage has "failed authentication"
| parse SyslogMessage with * "User " ParsedUser " from IP " ParsedIP " failed" *
| where isnotempty(ParsedUser)
| project TimeGenerated, ParsedUser, ParsedIP, SyslogMessage

The parse pattern uses * for “skip anything” and quoted strings for literal text. The unquoted names (ParsedUser, ParsedIP) become new columns containing the extracted values.

Working with JSON and dynamic columns

What it is

Many Sentinel tables contain columns of type dynamic — JSON objects or arrays. LocationDetails in SigninLogs is a common example: it contains nested data like {"city": "London", "state": "England", "countryOrRegion": "GB"}. To use these values in filters or projections, you must navigate the JSON structure.

How it works

Use dot notation to navigate nested JSON, and tostring() to convert the result to a usable string:

1
2
3
4
5
6
7
8

SigninLogs
| where TimeGenerated > ago(7d)
| extend City = tostring(LocationDetails.city)
| extend State = tostring(LocationDetails.state)
| extend Country = tostring(LocationDetails.countryOrRegion)
| extend OS = tostring(DeviceDetail.operatingSystem)
| extend Browser = tostring(DeviceDetail.browser)
| project TimeGenerated, UserPrincipalName, City, Country, OS, Browser

For deeper nesting, chain dot notation: tostring(AuthenticationDetails[0].authenticationMethod) accesses the first element of an array and extracts the authenticationMethod property.

1
2
3
4
5
6
7

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == "0"
| extend Username = tostring(split(UserPrincipalName, "@")[0])
| extend Country = tostring(LocationDetails.countryOrRegion)
| summarize SigninCount = count() by Username, Country
| order by Username asc, SigninCount desc