K0.5 What This Course Builds
The four phases
Figure K0.5 — Four phases, each unlocking a security capability. K0-K1 are free.
Phase 1 — Anatomy of KQL (K0-K3, free start): The foundation. Tables, operators, filtering, your first queries. After Phase 1: you can open any Sentinel table and extract the data you need for an investigation. You stop relying on portal views and workbooks for basic triage.
Phase 2 — Intermediate techniques (K4-K6): Time analysis, string parsing, and the operator that changes everything: join. After Phase 2: you can correlate events across tables, parse nested JSON fields, and build timeline reconstructions. You can investigate cross-product attack chains — following an attacker from sign-in (SigninLogs) through endpoint activity (DeviceProcessEvents) to data access (OfficeActivity).
Phase 3 — Advanced patterns (K7-K9): Deep dives into the 8 daily tables (K7), data visualisation for reporting (K8), and investigation query patterns (K9). After Phase 3: you build investigation dashboards, produce visual reports for leadership, and have a library of reusable investigation patterns.
Phase 4 — Mastery (K10-K13): Detection patterns (thresholds, baselines, anomaly detection — the queries that become Sentinel analytics rules), hunting patterns (rare value analysis, stacking, hypothesis-driven searches), query performance optimisation, and building a personal query library. After Phase 4: you are ready for the Detection Engineering course (build rules from your KQL skills) and the Threat Hunting course (hunt with your KQL skills). KQL is no longer a tool you use — it is a language you think in.
What you will build
By K13, the learner has a personal library of 50+ production security queries covering: sign-in investigation (10 queries), endpoint forensics (10 queries), email investigation (8 queries), cross-table correlation (8 queries), detection patterns (8 queries), hunting patterns (6 queries), and reporting visualisations (5 queries). These queries are reusable, parameterised, and documented in a Git repository.
Where this course leads
KQL proficiency is the prerequisite for three advanced courses: Detection Engineering (build the analytics rules that automate your queries), Practical Threat Hunting (hunt for threats that rules have not yet been written for), and Practical Incident Response (investigate incidents using the queries and tables you have mastered). Without KQL, those courses require the learner to learn the language AND the methodology simultaneously. With KQL mastered, the learner focuses entirely on methodology.
The myth: Security Copilot and other AI tools can write KQL for you. Learning the language manually is a waste of time.
The reality: AI tools generate draft KQL that is 70-80% correct. The remaining 20-30% requires the analyst to READ the query, IDENTIFY errors, and CORRECT them — which requires understanding the language. An analyst who cannot read KQL cannot validate AI output. They deploy incorrect queries, miss threats, and generate false positives. AI is an accelerator for analysts who know KQL (see Detection Engineering DE12). It is not a replacement for the skill. The analyst who knows KQL uses AI to work faster. The analyst who does not know KQL uses AI blindly and trusts output they cannot verify.
Try it yourself
Exercise: Assess your current KQL level
Which of these can you do today? (1) Write a where filter against SigninLogs. (2) Use summarize to count events by a field. (3) Join two tables by a shared field. (4) Use make_series for time-series analysis. (5) Write a parameterised function. If you can do 1-2: start at K1. If 1-3: start at K4. If 1-5: skip to K10. This course meets you where you are.
Check your understanding
A SOC analyst can write basic where filters and summarize statements but cannot join tables or parse nested JSON. Which phase should they start with, and what skill does that phase unlock?
Answer: Phase 2 (K4-K6). This analyst has the Phase 1 foundations (filtering, basic aggregation) but needs the intermediate techniques: time analysis (K4), string and JSON parsing (K5), and joins (K6). After Phase 2, the analyst can correlate events across SigninLogs and OfficeActivity — the cross-table investigation capability that transforms basic triage into real investigation.
Troubleshooting
“I already know KQL basics — can I skip to Phase 2?” Yes. Each phase is self-contained. If you can write a query with where, project, and summarize against SigninLogs: start at K4 (time analysis). The Phase 1 modules are there if you need them, not as gates you must pass through.
The 50+ query library: what you will build
By the end of this course, you will have a personal library of production-ready security queries. Here is a preview of what each phase produces:
Phase 1 queries (K0-K3): Basic investigation queries — failed sign-ins by IP, process execution by device, inbox rule creation, file operations by user. These are your daily triage queries. Approximately 12 queries.
Phase 2 queries (K4-K6): Cross-table investigation queries — correlating a sign-in anomaly with subsequent email activity (join SigninLogs with OfficeActivity), tracking an attacker across identity and endpoint tables (join SigninLogs with DeviceProcessEvents), parsing nested JSON fields in AuditLogs to extract MFA registration details. Approximately 12 queries.
Phase 3 queries (K7-K9): Deep investigation queries — full table schema explorations with worked security scenarios, visualisation queries for SOC dashboards (sign-in failure trends, alert volume over time, top targeted accounts), and reusable investigation patterns (timeline reconstruction, entity pivot, scope assessment). Approximately 14 queries.
Phase 4 queries (K10-K13): Production detection and hunting queries — threshold-based detection (password spray, bulk download, mass encryption), baseline anomaly detection (make_series for volumetric anomalies), rare value hunting (stacking analysis, first-seen entity detection), and performance-optimised queries for analytics rules. Approximately 16 queries.
Each query is documented with: the investigation question it answers, the table and fields it uses, the expected output for NE’s environment, and tuning guidance for adaptation to your environment. The library lives in a Git repository — version-controlled, searchable, and shareable with your team.
The cross-sell: where KQL leads
KQL proficiency unlocks three advanced Ridgeline courses that would otherwise require learning the language AND the methodology simultaneously:
Detection Engineering uses KQL to build 71 production analytics rules. Without KQL: the learner struggles with syntax while trying to understand detection methodology. With KQL: the learner focuses entirely on detection logic, rule architecture, and program governance.
Practical Threat Hunting uses KQL for hypothesis-driven investigation across Sentinel tables. Without KQL: the learner cannot express hunting hypotheses as queries. With KQL: the learner translates threat intelligence into executable searches.
Practical Incident Response uses KQL for evidence collection, timeline reconstruction, and scope assessment during active incidents. Without KQL: the learner relies on portal views that miss critical evidence. With KQL: the learner extracts exactly the evidence the investigation requires.
Mastering KQL is not the destination — it is the foundation that every Microsoft security skill builds on.
How to get the most from this course
Read, then type, then modify. Each subsection presents KQL examples. Do not just read them — type them into the Sentinel Logs blade and run them. Then MODIFY: change the time range, change the filter, change the table. The modification is where learning happens.
Build your query library as you go. From K1 onwards, save every useful query with a comment explaining what it does. By course end, your library is a production-ready investigation toolkit.
Use your own data when possible. The NE examples use fictional data. Your environment has REAL data. Adapt each query to YOUR SigninLogs, YOUR user names, YOUR IP ranges. The adapted query goes into your personal library — immediately useful.
Do not skip Phase 1. A join query that fails usually has a where filter issue (Phase 1), not a join issue (Phase 2). Build the foundation correctly.
Module-by-module skill progression
Each module adds specific operators that unlock a security capability the previous module could not achieve. The progression is deliberate — each module builds on the previous:
K1 teaches how the pipeline processes data: table in, table out. Skill unlocked: understanding WHY queries produce the results they do. K2 teaches filtering: where, extend, project. Skill: extracting exactly the events you need from any table. K3 teaches aggregation: summarize, count, dcount. Skill: answering “how many” questions that reveal patterns invisible in raw events. K4 teaches time operators: ago, between, bin, datetime_diff. Skill: temporal investigation — correlating events by WHEN they occurred. K5 teaches string parsing: parse, extract, parse_json. Skill: cracking open nested fields that contain investigation gold (DeviceDetail, TargetResources). K6 teaches joins: inner, leftanti, leftouter. Skill: cross-table investigation — the skill that transforms a single-table analyst into a multi-product investigator. K7 through K13 build on these foundations for time-series analysis, graph relationships, performance, detection engineering patterns, threat hunting, workbook reporting, and the capstone investigation lab.
You're reading the free modules of Mastering KQL
The full course continues with advanced topics, production detection rules, worked investigation scenarios, and deployable artifacts. Premium subscribers get access to all courses.