Module 6: Create queries for Microsoft Sentinel using Kusto Query Language (KQL) | SC-200

What is a KQL query?

A read-only request to process data and return results. It never modifies data.

How does data flow in a KQL query?

Start with a table, then pass the results through operators using the pipe ( | ). The LEFT side runs first; its result flows to the next operator on the right.

What does the pipe ( | ) do?

Passes the table result from one step to the next operator.

Tabular expression statement

The most common KQL statement. Its input and output are both tables.

Two common Sentinel tables used in the module

SecurityEvent (Windows security events) and SecurityAlert (alerts).

Is KQL case-sensitive for table names, column names and operators?

Yes. where, let, search must be lowercase. 'Let' or 'Search' with a capital letter fails. SecurityEvent must be spelled exactly.

search operator

Quick way to hunt for a term across all tables and columns. Example: search "new". Easy, but slower than where on a specific table.

Search only specific tables

search in (SecurityEvent, App*) "new" → searches SecurityEvent plus every table whose name starts with App.

where operator

Filters rows down to only those that match a condition. Example: SecurityEvent | where TimeGenerated > ago(1h)

Filter to the last 1 hour

where TimeGenerated > ago(1h) (use 1h, not 1hr. Units: d = days, h = hours, m = minutes, s = seconds)

Two ways to combine filters

1) One line with and / or: where TimeGenerated > ago(1h) and EventID == 4624 2) Stack several where lines (easier to read).

Event ID 4624

Successful logon.

Event ID 4625

Failed logon.

Event ID 4634

Logoff (used with 4624 in the logon/logoff join example).

Event ID 4688

A new process was created.

Event ID 4672

Special privileges assigned to a new logon (privileged / admin-level logon).

Filter on several values at once

Use in: where EventID in (4624, 4625)

== vs =~

== is case-SENSITIVE equals. =~ is case-INSENSITIVE equals. Example: AccountType =~ "user" matches User, USER, user.

!= vs !~

!= is case-sensitive not-equal. !~ is case-insensitive not-equal.

let statement

Declares a variable you can reuse. Ends with a semicolon. Example: let timeOffset = 1h; let discardEventID = 4688;

let can store a whole dataset

let LowActivityAccounts = SecurityEvent | summarize cnt = count() by Account | where cnt < 1000; then: LowActivityAccounts | where Account contains "sql"

Filter a window between 2 hours ago and 1 hour ago (timeOffset = 1h)

where TimeGenerated > ago(timeOffset*2) and TimeGenerated < ago(timeOffset)

extend operator

Adds a NEW calculated column. All original columns stay. Example: extend StartDir = substring(ProcessName, 0, string_size(ProcessName) - string_size(Process))

order by / sort by

Sorts rows. Add asc or desc per column. Default is desc. Example: order by StartDir desc, Process asc

project

Choose which columns to show. Can also rename and add computed columns. Example: SecurityEvent | project Computer, Account

project-away

REMOVES the columns you list. Keeps everything else.

project-keep

KEEPS only the columns you list (removes the rest).

project-rename

Renames columns. Example: project-rename NewName = OldName

project-reorder

Changes the order of columns in the output.

summarize operator

Groups rows and calculates aggregates (count, dcount, avg, sum, max, min). Example: summarize count() by Account

count() vs countif()

count() = number of rows per group. countif(condition) = only rows that meet the condition.

dcount()

Estimated number of DISTINCT (unique) values. Example: summarize dcount(IpAddress)

avg(), sum(), max(), min()

Average, total, highest and lowest value across each group. Each also has an -if version (avgif, sumif, maxif, minif).

Password spray detection idea (module example)

SigninLogs | where ResultDescription has "Invalid password" | summarize applicationCount = dcount(AppDisplayName) by UserPrincipalName, IPAddress | where applicationCount >= threshold

arg_max(TimeGenerated, *)

Returns the full row with the LATEST TimeGenerated per group (most recent record). Example: summarize arg_max(TimeGenerated, *) by Computer

arg_min(TimeGenerated, *)

Returns the full row with the EARLIEST TimeGenerated per group.

make_list()

Builds an array of ALL values per group (duplicates kept). Example: summarize make_list(Account) by Computer

make_set()

Builds an array of UNIQUE values per group (no duplicates).

make_list vs make_set (memory trick)

List = Lots (keeps duplicates). Set = Single copy of each (unique only).

render operator

Turns results into a chart. Goes at the END, usually after summarize. Example: summarize count() by Account | render barchart

render chart types in the module

areachart, barchart, columnchart, piechart, scatterchart, timechart

bin()

Groups time into buckets. Example: summarize count() by bin(TimeGenerated, 1m) | render timechart

union operator

STACKS rows from two or more tables into one result. Example: SecurityEvent | union SigninLogs

union kind=outer vs kind=inner

outer (default) keeps all columns from all tables. inner keeps only columns that exist in every table.

union withsource=SourceTable

Adds a column showing which table each row came from.

SecurityEvent | union SigninLogs | summarize count()

Returns ONE row: the total row count of both tables combined.

SecurityEvent | union (SigninLogs | summarize count() | project count_)

Returns ALL SecurityEvent rows plus ONE extra row holding the SigninLogs count. The summarize only applies inside the parentheses.

union App* | summarize count() by Type

Counts rows for every table whose name starts with App. The Type column holds the table name.

join operator

Combines COLUMNS from two tables where a key matches (for example Account). Syntax: T1 | join kind=inner (T2) on Account

union vs join (memory trick)

union = add ROWS (stack on top). join = add COLUMNS (side by side on a matching key).

Default join kind

innerunique: removes duplicate keys from the LEFT table, then does an inner join. Write kind=inner if you want every match.

join kind=inner

Only rows that match in BOTH tables.

join kind=leftouter / rightouter / fullouter

leftouter = all left rows + matches from right (blank if no match). rightouter = the reverse. fullouter = all rows from both.

join kind=leftanti / rightanti

leftanti = left rows with NO match in the right table. Great for finding what's missing. rightanti = the reverse.

join kind=leftsemi / rightsemi

leftsemi = left rows that DO have a match; only left columns returned. rightsemi = the reverse.

Join when key column names differ

on $left.ColumnA == $right.ColumnB

Join best practice

Filter both tables first and put the smaller table on the LEFT.

extract() function

Pulls part of a string using regex. Format: extract(regex, captureGroup, text). capturegroup 0 = entire match, 1 = for the value matched by regex.

extract("x=([0-9.]+)", 1, "hello x=45.6|wo") returns?

"45.6"

tolower()

Converts text to lowercase. Often used before matching or extracting so case doesn't matter.

parse operator

Extracts values from a string expression into one or more calculated columns. Example: parse EventText with * "resourceName=" resourceName ", totalSlices=" totalSlices:long * (* = skip text, :long / :date sets the type)

extract vs parse

extract = regex, one value per call. parse = fixed text pattern, several columns at once.

Read a field inside a Nested (JSON) column

Dot notation.

Example: SigninLogs | extend OS = DeviceDetail.operatingSystem

parse_json()

Converts a JSON string into a dynamic object so you can read its fields. Example: extend AuthDetails = parse_json(AuthenticationDetails)

AuthDetails[0].authenticationMethod

[0] = first item in the JSON array, then .authenticationMethod reads that field. Bracket form also works: AuthDetails[0]["authenticationMethod"]

externaldata operator

Reads data from outside storage (for example an Azure Blob file with a SAS token) into the query. You define the schema, e.g. externaldata (UserID:string) [@"https://…"]

KQL functions / parsers

Save a query as a function (e.g. PrivLogins), then call it by name like a table. Used to build reusable parsers.

has vs contains

has = matches a whole term, faster (uses the index). contains = matches any substring, slower. Prefer has when possible.

Where do you write KQL in Microsoft Sentinel?

Microsoft Sentinel > Logs (KQL mode). In the Defender portal, use Advanced hunting.

Tables ending in _CL

Custom log tables (as in the lab demo, e.g. SecurityEvent_CL). Older custom columns get a type suffix: _s = string, _d = number, _b = boolean, _t = datetime.