ExamSprintLab

KQL cheat sheet for certification exams

Updated October 9, 2026

Kusto Query Language (KQL) is the read-only query language of Azure Monitor Logs, Microsoft Sentinel, Defender XDR advanced hunting, Microsoft Fabric Eventhouses and Azure Data Explorer. A query starts with a table and passes its rows through operators separated by a pipe (|), and each operator works on the output of the one before it. The decision rule for exam questions: filter first, aggregate second, shape the output last, and prefer the operator that matches whole terms (has) over the one that scans substrings (contains).

The shape of every query

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType != "0"
| summarize Failures = count() by UserPrincipalName
| top 5 by Failures desc

Read it as a sentence: take the sign-in table, keep the last day, keep failures, count per user, show the five worst. Reading a query top to bottom, one operator at a time, solves most completion questions. ResultType is a string column in this table that holds an error code, and "0" means success, so != "0" keeps the failed sign-ins.

Operators by job

JobOperatorNote
Keep rowswhereSeveral where lines are the same as and
Pick columnsprojectproject-away drops columns, project-rename renames
Add a columnextendComputes from existing columns; keeps all others
Aggregatesummarize ... by ...count(), countif(), dcount(), sum(), avg(), make_set(), arg_max()
Sortorder by (alias sort by)Descending is the default
First N by a valuetop 10 by X descSorts and limits in one step
Any N rowstake (alias limit)No ordering guaranteed; use it to peek at a table
Unique valuesdistinct
Name a valuelet x = ...;Also names a whole sub-query
Stack tablesunionRows from several tables
Combine on a keyjoinSee the next section
Split a stringparsePulls values out of free text
Expand an arraymv-expandOne row per element
Chartrender timechartDisplay hint only

summarize is the operator that exam questions test hardest. It removes every column you did not name in the aggregation or in the by clause. If a later project asks for a column that summarize dropped, the query fails.

Strings: the has / contains / == family

The documentation draws the line clearly. == is case-sensitive and =~ is case-insensitive. has matches a whole term, contains matches any substring, and Kusto builds a term index for terms of three characters or more, so has can use that index while contains has to scan the column. Both are case-insensitive by default, and the _cs variants (has_cs, contains_cs) are case-sensitive.

OperatorMatchesCase
== / !=Exact valueSensitive
=~ / !~Exact valueInsensitive
has / !hasWhole termInsensitive
contains / !containsAny substringInsensitive
startswith, endswithStart or end of the stringInsensitive
in (a, b) / in~Equals any listed valueSensitive / insensitive
has_any (a, b)Any listed termInsensitive
matches regexRegular expressionSensitive

The subtle case: "KustoExplorerQueryRun" has "Explorer" is false, because Explorer is not a whole term, while contains "Explorer" is true. For the same task the documentation also advises the case-sensitive operator for speed (== rather than =~). If a question asks for the most efficient filter and the value is a complete word, has is the answer.

Time

ago(1h), ago(7d) give a moment relative to now. between (datetime(2026-10-01) .. datetime(2026-10-08)) gives a range. bin(TimeGenerated, 1h) rounds a timestamp to a bucket, and summarize count() by bin(TimeGenerated, 1h) produces an hourly series. The column name depends on the source: Azure Monitor and Sentinel tables use TimeGenerated, and Defender XDR hunting tables such as DeviceProcessEvents use Timestamp.

Join kinds

The default join flavor, when you write no kind=, is innerunique: an inner join that first removes duplicate rows on the left side. People expect a plain inner join and get fewer rows. Write kind=inner when duplicates on the left matter.

KindRows returned
innerunique (default)Matches, with the left side de-duplicated on the key
innerOnly matching rows from both tables
leftouterAll left rows, matched right columns or null
leftsemiLeft rows that have a match, left columns only
leftantiLeft rows with no match, left columns only

Worked example: find accounts that signed in but never appeared in a list of approved admins, kept as a Sentinel watchlist named ApprovedAdmins whose search key is the UPN.

let Admins = _GetWatchlist('ApprovedAdmins') | project AccountUPN = tostring(SearchKey);
SigninLogs
| where TimeGenerated > ago(7d)
| join kind=leftanti (Admins) on $left.UserPrincipalName == $right.AccountUPN
| summarize Signins = count() by UserPrincipalName

For performance the documentation recommends putting the smaller table on the left.

Which exams test this

ExamHow KQL appearsWhere it is covered here
SC-200Perform threat hunting (20–25%): pick the right table, identify threats with KQL and build Advanced Hunting and Sentinel hunting queries; the same queries drive custom detections and analytics rules in the operations domainKQL for SC-200 and perform threat hunting
DP-600Prepare data (45–50%): select, filter and aggregate data with KQLPrepare data explained
DP-700Ingest and transform data (30–35%): choose KQL among the transformation options, transform batch data with it and process streaming data with itIngest and transform explained
SC-300Plan and automate identity governance (20–25%): monitor Microsoft Entra ID with KQL queries in Log AnalyticsIdentity governance explained
AI-200Secure, monitor, and troubleshoot Azure solutions (20–25%): write KQL queries to analyse logs and metricsSecure, monitor and troubleshoot explained
AZ-400Implement an instrumentation strategy (5–10%): interrogate logs with basic KQL queriesInstrumentation explained
AZ-104Monitor and maintain Azure resources (10–15%): query and analyse logs in Azure Monitor, which uses KQL; the outline does not name the languageMonitor and maintain explained

Depth differs: SC-200 gives KQL a whole hunting domain, while AZ-400 asks only for basic queries and AZ-104 for log queries without naming the language. SC-200, SC-300, DP-600 and DP-700 rows follow the English outlines that take effect in October 2026, which Microsoft describes as minor changes.

How the questions are built

Most KQL questions are one of three types: complete the missing operator, pick the query that produces a described result, or pick the most efficient filter. The traps repeat:

  • Wrong aggregation level. “Per user” needs by UserPrincipalName; without it you get one row for the whole table.
  • Filter after summarize. A condition on a raw column placed after summarize fails because the column is gone. Raw-column filters go before it.
  • Case and substring. has against a partial word returns nothing. Constraint words: “most efficient”, “whole word”, “case-insensitive”.
  • Wrong table or time column. The scenario names a data source; the table follows from it, and the time column follows from the table.
  • Left versus anti. “Never” and “no matching” point to leftanti; “all, including those without” points to leftouter.

Practice questions

Question 1. A query must return the number of failed sign-ins per user over the last day, but only for users with more than 20 failures, showing the highest first. Which ordering of operators is correct?

  • A. where Failures > 20 | summarize Failures = count() by UserPrincipalName | where TimeGenerated > ago(1d)
  • B. where TimeGenerated > ago(1d) and ResultType != “0” | summarize Failures = count() by UserPrincipalName | where Failures > 20 | order by Failures desc
  • C. where TimeGenerated > ago(1d) | where Failures > 20 | summarize Failures = count() by UserPrincipalName
  • D. summarize Failures = count() by UserPrincipalName | where TimeGenerated > ago(1d) | order by Failures desc
Show answer

Answer: B

Filtering the rows to the last day and to failures comes first, then summarize counts per user, then a where on the aggregated column removes users at or under 20, then the result is sorted. Option A filters on the count before it exists. Option C puts the count filter before summarize, so the column is missing. Option D summarizes first, which drops the TimeGenerated column, so the later time filter fails.

Question 2. You are searching a large table for rows whose Computer column contains the complete word payroll, and the query must be as fast as possible. The match does not have to be case-sensitive. Which filter should you use?

  • A. where Computer matches regex ‘payroll’
  • B. where Computer contains ‘payroll’
  • C. where Computer has ‘payroll’
  • D. where Computer == ‘payroll’
Show answer

Answer: C

has matches whole terms and can use the term index, so it is faster than contains, which scans for substrings. It is also case-insensitive, which meets the requirement. contains works but scans the column. A regular expression match cannot use the term index either and is more work than a plain word needs. An exact equality would only match when the whole value is the word payroll, which the requirement does not say.

Go deeper