Why KQL Matters — The Query Language Your SOC Depends On

What this course is

This is a complete KQL training course for security practitioners — the query language behind every investigation, detection rule, threat hunt, and security report in the Microsoft stack. Fourteen modules take you from basic operators through production detection rules, time-series anomaly detection, graph-based attack path reconstruction, and advanced threat hunting.

You use Microsoft Sentinel. You use Defender XDR. You triage alerts, investigate incidents, and produce reports. You do all of this without writing KQL queries — relying on the portal UI, template rules, and pre-built workbooks. It works. Until it doesn't.

The alert fires for "suspicious sign-in activity." You need to know whether this user has ever authenticated from this IP before. The portal doesn't show 90 days of per-user history. The workbook shows aggregates, not detail. The only way to answer the question is five lines of KQL:

SigninLogs
| where UserPrincipalName == "j.morrison@northgateeng.com"
| where IPAddress == "185.220.101.42"
| where TimeGenerated > ago(90d)
| project TimeGenerated, IPAddress, ResultType, AppDisplayName, DeviceDetail

Twelve seconds to run. Zero rows. j.morrison has never authenticated from this IP. The sign-in is definitively anomalous. Without the query, you'd spend 20 minutes clicking through portal pages and still not have a definitive answer.

This is why KQL matters. Not because the language is interesting — but because your daily work requires answers that the portal UI cannot provide. This course makes KQL a fluent working language, not a search bar you copy-paste into.

Every concept is taught with security log examples. Every exercise uses real investigation and detection scenarios. You learn operators by using them to find attacker activity, detect anomalies, and build production detection rules — not by reading abstract syntax documentation.

What this course teaches

Fourteen modules across four phases. K0 and K1 are free — no account required.

Phase 1 — Anatomy of KQL (K0, K1). You are here now. K0 shows you three security questions you can't answer without KQL, maps the Microsoft security data model, identifies the eight tables you'll query every day, walks your first security query, and sets up the lab environment. K1 goes under the hood — the tabular data model, the query pipeline, every data type, type conversion, and null handling (the silent query killer). Understanding how KQL processes data makes everything in Phases 2–4 click faster.

Phase 2 — Core Techniques (K2–K6). Five modules building operator fluency. Filtering and shaping — every where pattern, string comparison, datetime filtering, extend, and the project family (K2). Aggregation — every summarize function, time-based grouping with bin, set and list aggregations, pivot patterns (K3). Joins and correlation — all eight join flavors including the innerunique trap, five performance rules, union with schema normalization, lookup enrichment, and seven cross-table investigation patterns for AiTM, phishing chains, lateral movement, and token replay (K4). String parsing — parse, extract, regex, parse_json, URL and email decomposition for phishing analysis, base64 decoding, and building reusable log parsers (K5). Advanced filtering — mv-expand and mv-apply for dynamic arrays, advanced regex for obfuscation detection, let statements, externaldata, IP range functions, CIDR matching, and watchlist-driven detection patterns (K6).

Phase 3 — Advanced Patterns (K7–K9). Three modules applying KQL to sophisticated detection and investigation challenges. Time-series analysis and anomaly detection — make-series, series_decompose, series_decompose_anomalies, baseline comparison with peer groups, rate-of-change detection for C2 beaconing, and building production anomaly rules (K7). Graph and relationship analysis — entity relationship modeling, attack path reconstruction, multi-hop investigation queries, identity mapping across tables, network graph patterns, and process tree reconstruction (K8). Performance optimization — query engine internals, ten common anti-patterns with before/after, partition pruning, materialized views, shuffle and broadcast join hints, and production query health monitoring (K9).

Phase 4 — Mastery (K10–K13). Four modules applying every skill to production output. Detection rule engineering — converting investigation queries to analytics rules, scheduled vs. NRT design, entity mapping, threshold tuning, multi-condition correlated detection (K10). Threat hunting — hypothesis-driven methodology, MITRE ATT&CK-aligned hunting across seven techniques, UEBA composite risk scoring, retroactive IOC sweeps, and a four-exercise mini capstone (K11, the flagship module). Workbooks, dashboards, and reporting — SOC operations dashboard, executive board deck design, detection health monitoring, automated report generation (K12). Capstone — three complete investigation scenarios requiring every skill: credential spray to BEC, ransomware kill chain reconstruction, and cloud data exfiltration (K13).

Phases 1 and 2 are sequential — they build the operator foundation. Phase 3 advanced patterns and Phase 4 mastery modules can be prioritized based on your needs: jump to K10 if detection rules are urgent, K11 if hunting is the priority, K7 if anomaly detection is what you need.

Who this course is for

Anyone who queries security data in a Microsoft environment. The course is built for self-directed learners at any skill level beyond absolute beginner.

SOC analyst who writes KQL daily and wants to go deeper. You can filter and aggregate. You want to understand why queries behave the way they do, write more efficient queries, and use advanced operators — time-series analysis, graph queries, multi-hop joins — that you haven't explored. This course takes you from "I can query" to "I understand the query engine."

Detection engineer who builds analytics rules. You write Sentinel analytics rules and want more sophisticated detection logic — time-series baselines, correlated multi-condition rules, performance-optimized queries that don't time out on large datasets. K10 (detection rule engineering) is the core module, but Phases 2 and 3 give you the operator depth that makes better rules.

Threat hunter who needs advanced query techniques. You hunt in Sentinel and Defender XDR. You need behavioral analysis (peer group comparison, baseline deviation), retroactive IOC sweeps across months of data, attack path reconstruction from log data, and the graph-based queries that trace lateral movement. K8 (graph analysis) and K11 (threat hunting) are the advanced modules.

IT professional transitioning to security operations. You know the M365 portals and you've seen KQL in Sentinel. You want structured training that teaches the language through security scenarios — not generic data analytics examples. Every query in this course uses security log tables (SigninLogs, DeviceProcessEvents, EmailEvents). You learn KQL and investigation methodology at the same time.

Anyone with a genuine interest in KQL and security data analysis. Whatever your background — transitioning from Splunk SPL, coming from a development background, or exploring security for the first time — if you're willing to write queries, this course is for you.

Prerequisites

Two required, one recommended.

Basic KQL familiarity. You should be able to write a query using where, project, summarize, and extend. If SigninLogs | where TimeGenerated > ago(1h) | summarize count() by UserPrincipalName makes sense to you, you're ready. If KQL is entirely new, the M365 Security Operations course (Module 6) covers the foundations, or use Microsoft Learn's free "Write your first query with KQL" primer — two to three hours.

Access to Sentinel or Defender XDR Advanced Hunting. You need a query environment to run the exercises. Options: your production Sentinel workspace, a developer tenant with sample data (free from developer.microsoft.com), or Defender XDR Advanced Hunting (included with any M365 E5 or Defender license). K0.6 walks the setup.

Recommended: experience investigating security alerts. The course teaches KQL through investigation scenarios. If you've investigated a phishing alert, triaged a suspicious sign-in, or reviewed endpoint telemetry — even at a basic level — the scenarios will feel familiar and the learning will stick faster.

Lab setup

Everything runs in the browser. No local tools, no VMs, no third-party software.

M365 Developer Tenant (free). From developer.microsoft.com — 25 user licenses, full E5 environment. Load the sample data packs to populate SigninLogs, DeviceProcessEvents, EmailEvents, and other tables with realistic data. K0.6 walks the complete setup.

Sentinel workspace. Linked to your developer tenant via an Azure free subscription. The free tier includes 5 GB/day of Log Analytics ingestion — enough for all course exercises.

Defender XDR Advanced Hunting. Included with the developer tenant. Different table schemas from Sentinel — the course shows both where they diverge.

What you can skip: you don't need any setup to start K0. The first five subs are content and examples you read. K0.6 is the lab setup sub — configure your environment when you reach it.

How the course is structured

Every module from K2 onward follows the same pattern.

Annotated KQL queries. Every concept is taught through a complete, runnable query with line-by-line annotation. The query uses real security tables (SigninLogs, DeviceProcessEvents, EmailEvents) and addresses a real investigation or detection scenario. Copy, paste, run — then read the explanation.

Security scenario context. Every operator is introduced through a security problem: "you need to find all users who signed in from more than five countries in the last 24 hours" — then the query that solves it.

Progressive complexity. Each module starts with the simple form of the operator and builds to the advanced form. K4 (joins) starts with a two-table inner join and ends with materialized multi-step progressive investigation across seven tables.

Try-it exercises. Practice queries you write yourself. Setup, task, expected result, debugging branch. The exercises use the same sample data in your developer tenant.

Module completion pattern. Each module has content subsections (five to nine), a module summary, and a Check My Knowledge subsection. Phase 4 modules include extended exercises and the capstone includes self-assessment rubrics.

Time per phase

The course is self-paced. No cohorts, no deadlines, no streaks.

Phase 1 (K0, K1): One evening. K0 is orientation and first query (1.5–2 hours). K1 is data model internals (1.5–2 hours).

Phase 2 (K2–K6): Two to three weeks at five to eight hours per week. Five modules building operator fluency. K4 (joins) is the longest and most important — take your time with it.

Phase 3 (K7–K9): One to two weeks. Three advanced modules. K7 (time-series) and K9 (performance) are the most technically dense.

Phase 4 (K10–K13): Two to three weeks. Four application modules. K11 (threat hunting) is the flagship. K13 (capstone) requires a dedicated session — plan three to four hours for the three investigation scenarios.

Full course at five to eight hours per week: eight to twelve weeks. Run every query. KQL fluency comes from writing queries, not reading about them. Execute every code block in your own environment.

Start here

Go to K0.1 — Three Questions You Cannot Answer Without KQL next. It walks three concrete security scenarios — each one a question the portal UI can't answer and KQL can — and shows you the difference between clicking through pages and writing a five-line query that gives you a definitive answer in seconds.

After K0.1, the remaining K0 subsections map the Microsoft security data model (K0.2), identify the eight tables you'll query every day (K0.3), walk your first complete security query from blank editor to investigation result (K0.4), preview the complete skill progression across all fourteen modules (K0.5), set up your lab environment (K0.6), summarize the module (K0.7), and test what you've learned (K0.8).

Work through K0 in order. The data model K0.2 introduces and the eight-table map in K0.3 are referenced in every subsequent module.