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

What does a KQL query do? Can it change data?

Read-only. Returns results, never changes data.

What does the pipe ( | ) do?

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

What is a tabular expression statement?

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

What are the two common Sentinel tables used in the module?

SecurityEvent = Windows security events. SecurityAlert = alerts.

Is KQL case-sensitive?

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

What does the search operator do?

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

What does search in (Table, App*) do?

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

What does the where operator do?

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

What does ago() do?

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

How do you combine multiple filters in KQL?

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

What does the in operator do?

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

What is EventID 4624?

Successful logon.

What is EventID 4625?

Failed logon.

What is EventID 4634?

Logoff.

What is EventID 4688?

New process created.

What is EventID 4672?

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

What does == (double equals) do?

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

What does =~ (equals tilde) do?

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

What is the difference between != and !~ ?

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

What does the let statement do?

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

How do you use let to store a dataset?

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

do you use a let variable inside a where time filter?

let names the value; where does the filtering. Example: let timeOffset = 1h; then where TimeGenerated > ago(timeOffset*2) and TimeGenerated < ago(timeOffset) = between 2h ago and 1h ago.

What does the extend operator do?

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

What does order by / sort by do?

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

What does the project operator do?

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

What does project-away do?

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

What does project-keep do?

Keeps only the listed columns. Drops the rest.

What does project-rename do?

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

What does project-reorder do?

Changes the column order.

What does the summarize operator do?

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

What does count() return?

Number of rows per group.

What does countif() return?

Number of rows per group that meet a condition.

What does dcount() return?

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

What do avg(), sum(), max() and min() return?

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

How do you detect a password spray pattern? (module example)

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

What does arg_max(TimeGenerated, *) return?

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

What does arg_min(TimeGenerated, *) return?

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

What does make_list() do?

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

What does make_set() do?

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

What does the render operator do?

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

What chart types can render create?

areachart, barchart, columnchart, piechart, scatterchart, timechart

What does bin() do?

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

What does the union operator do?

Stacks ROWS from 2+ tables into one result. Example: SecurityEvent | union SigninLogs

What is the difference between union kind=outer and kind=inner?

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

What does union withsource= do?

Adds a column showing which table each row came from.

What does SecurityEvent | union SigninLogs | summarize count() return?

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

What does SecurityEvent | union (SigninLogs | summarize count()) return?

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

What does union App* | summarize count() by Type return?

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

What does the join operator do?

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

What is the difference between union and join?

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

What is the default join kind?

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

What does join kind=inner return?

Only rows that match in BOTH tables.

What does join kind=leftouter return?

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

What does join kind=fullouter return?

All rows from BOTH tables, matched where possible.

What does join kind=leftanti return?

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

What does join kind=leftsemi return?

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

When do you use $left and $right in a join?

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

What is the best practice for join performance?

Filter first. Put the SMALLER table on the left.

What does extract() do?

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

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

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

What does tolower() do?

Converts text to lowercase so case doesn't matter.

What does the parse operator do?

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

What is the difference between extract and parse?

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

How do you read a field inside a dynamic column?

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

What does parse_json() do?

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

What does the array index [0] mean? (e.g. AuthDetails[0])

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

What is bracket notation ["field"] used for?

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

What does the externaldata operator do?

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

What is a KQL function (saved query)?

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

What is the difference between has and contains?

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

Where do you write KQL queries in Sentinel and Defender?

Microsoft Sentinel > Logs. Defender portal > Advanced hunting.

What does the _CL table suffix mean?

Custom log table. Example: SecurityEvent_CL

What do the custom column suffixes _s, _d, _b, _t mean?

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