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

KQL query (what it does)

Read-only. Returns results, never changes data.

Pipe ( | )

Passes results to the next operator. Left side runs first, then flows right.

Tabular expression statement

Table in, table out. The most common KQL statement.

Common Sentinel tables (module)

SecurityEvent = Windows security events. SecurityAlert = alerts.

KQL case sensitivity

Case-sensitive. Operators must be lowercase (let, where, search). Table and column names must match exactly.

search operator

Finds a term across ALL tables and columns. Fast to write, slow to run. Example: search "new"

search in (Table, App*)

Searches only the tables listed. App* = every table starting with App. Example: search in (SecurityEvent, App*) "new"

where operator

Filters rows to only the ones that match a condition. Example: | where EventID == 4624

ago()

Time back from now. Example: where TimeGenerated > ago(1h). Units: d, h, m, s (1h, not 1hr).

Combining filters

Use and / or on one line, OR stack several where lines (easier to read).

in operator

Matches any value in a list. Example: where EventID in (4624, 4625)

EventID 4624

Successful logon.

EventID 4625

Failed logon.

EventID 4634

Logoff.

EventID 4688

New process created.

EventID 4672

Privileged (admin-level) logon: special privileges assigned.

== (double equals)

Equals, case-SENSITIVE. "User" == "user" → false

=~ (equals tilde)

Equals, case-INSENSITIVE. "User" =~ "user" → true. Memory: ~ = "about the same"

!= vs !~

!= not equal (case-sensitive). !~ not equal (case-insensitive).

let statement

Creates a reusable variable. Ends with ; Example: let timeOffset = 1h;

let with a dataset

Stores a whole query result to reuse by name. Example: let LowActivity = SecurityEvent | summarize cnt=count() by Account | where cnt < 1000;

Time window with let

Between 2h ago and 1h ago: where TimeGenerated > ago(timeOffset*2) and TimeGenerated < ago(timeOffset)

extend operator

Adds a NEW calculated column. Keeps all existing columns. Example: | extend StartDir = substring(…)

order by / sort by

Sorts rows. asc = low to high, desc = high to low. Default = desc.

project

Shows only the columns you pick. Can rename or add computed columns. Example: | project Computer, Account

project-away

Removes the listed columns. Keeps the rest. Memory: AWAY = throw away.

project-keep

Keeps only the listed columns. Drops the rest.

project-rename

Renames a column. Example: | project-rename NewName = OldName

project-reorder

Changes the column order.

summarize operator

Groups rows and calculates totals. Example: | summarize count() by Account

count()

Number of rows per group.

countif()

Number of rows per group that meet a condition.

dcount()

Number of UNIQUE values (an estimate). Memory: d = distinct.

avg() / sum() / max() / min()

Average / total / highest / lowest per group. Each has an -if version (avgif, sumif…).

Password spray pattern (module)

Invalid password sign-ins → dcount(AppDisplayName) by UserPrincipalName, IPAddress. One IP failing across many apps = spray.

arg_max(TimeGenerated, *)

Returns the MOST RECENT full row per group. Memory: max = latest.

arg_min(TimeGenerated, *)

Returns the EARLIEST full row per group. Memory: min = first.

make_list()

Puts all values in one array per group. Keeps duplicates. Memory: List = Lots.

make_set()

Puts unique values in one array per group. No duplicates. Memory: Set = Single copy.

render operator

Turns results into a chart. Goes LAST, after summarize. Example: | render barchart

render chart types

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 2+ tables into one result. Example: SecurityEvent | union SigninLogs

union kind=outer vs kind=inner

outer (default) = keep all columns. inner = keep only columns found in every table.

union withsource=

Adds a column showing which table each row came from.

union then summarize count()

SecurityEvent | union SigninLogs | summarize count() → ONE row: total of both tables.

union with summarize in ( )

SecurityEvent | union (SigninLogs | summarize count()) → all SecurityEvent rows + ONE row with the SigninLogs count.

union App* | summarize count() by Type

Row count per table for every table starting with App. Type = table name.

join operator

Adds COLUMNS from a second table where a key matches. Example: T1 | join kind=inner (T2) on Account

union vs join

union = stacks ROWS (on top). join = adds COLUMNS (side by side on a key).

Default join kind

innerunique: removes duplicate keys on the LEFT, then inner join. Write kind=inner to keep every match.

join kind=inner

Only rows that match in BOTH tables.

join kind=leftouter

All LEFT rows + matching right rows. No match = empty right columns. (rightouter = the reverse)

join kind=fullouter

All rows from BOTH tables, matched where possible.

join kind=leftanti

LEFT rows with NO match on the right. Memory: anti = against / missing.

join kind=leftsemi

LEFT rows that DO match. Only left columns returned. Memory: semi = half (left side only).

$left / $right

Use when key names differ. Example: on $left.UserName == $right.Account

join best practice

Filter first. Put the SMALLER table on the left.

extract()

Pulls text out with regex. Format: extract(regex, group, text). Group 1 = inside first ( ), group 0 = whole match.

extract example

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

tolower()

Converts text to lowercase so case doesn't matter.

parse operator

Splits text with a fixed pattern into several columns. * = skip text. Example: parse EventText with * "resourceName=" resourceName "," *

extract vs parse

extract = regex, one value. parse = fixed pattern, many columns.

Dot notation (dynamic column)

Reads a field inside a dynamic column. Example: DeviceDetail.operatingSystem

parse_json()

Turns a JSON text string into an object you can read fields from.

Array index [0]

Picks the FIRST item in a JSON array. Example: AuthDetails[0].authenticationMethod = method of the first entry.

Bracket notation ["field"]

Another way to read a JSON field. AuthDetails[0]["authenticationMethod"] = AuthDetails[0].authenticationMethod

externaldata operator

Pulls data from outside storage (e.g. Azure Blob with a SAS token) into the query.

KQL function (saved query)

Save a query with a name (e.g. PrivLogins) and call it like a table. Used to build parsers.

has vs contains

has = whole word, faster. contains = any part of text, slower. Prefer has.

Where to write KQL

Microsoft Sentinel > Logs. Defender portal > Advanced hunting.

_CL table suffix

Custom log table. Example: SecurityEvent_CL

Custom column suffixes

_s = string, _d = number, _b = boolean, _t = datetime. Example: EventID_s