What This Course Builds

Fourteen modules in four phases

KQL has over 200 operators and functions. Trying to learn them all at once is how analysts abandon the language after two weeks. This course teaches KQL in the order that security investigations demand, not alphabetically, not by complexity, but by the sequence of questions you ask during a real investigation. Each phase unlocks a security capability that the previous phase could not achieve.

14 modules across 4 phases. Each phase unlocks a security capability that the previous phase could not achieve. K0–K1 are free.

Phase 1 — Foundation (K0–K3)

You are here. Phase 1 teaches the mechanics of KQL itself: how data flows through the pipeline, how filtering works, and how to count and aggregate results into meaningful summaries.

Module 0 (this module) orients you to the security data model, the eight tables you will query daily, and the four-operator query pattern. Module 1 teaches how KQL processes data internally — the tabular expression model, how each pipe operator transforms the dataset, and why the order of operations matters for both correctness and performance. You will understand not just what your queries do, but why they produce the results they produce. This matters when a query returns unexpected results — the analyst who understands the data-flow model can diagnose the problem. The analyst who memorized the syntax cannot.

Module 2 teaches filtering and shaping (the where, extend, and project operators in depth, including multiple filter conditions, calculated columns, conditional logic with iff() and case(), and column renaming. After K2, you can extract exactly the events you need from any table and reshape the output into the format your investigation requires. Module 3 introduces aggregation) summarize, count, dcount, avg, min, max, and grouping with by. Aggregation is the operator that transforms raw events into findings. Instead of seeing 45,000 individual sign-ins, you see "12 unique users targeted from this IP in the last hour." Without aggregation, the analyst reads individual events and tries to spot patterns manually. With aggregation, the query surfaces the pattern directly.

After Phase 1, you can open any Sentinel table, filter it to the events that matter, and summarize the results into investigation findings. You stop relying on portal views and pre-built workbooks for basic triage.

Phase 2 — Correlation (K4–K6)

Phase 2 is the capability leap. A Phase 1 analyst can investigate within a single table. A Phase 2 analyst can investigate across tables, following an attacker from authentication through lateral movement to data exfiltration.

Module 4 teaches join. The operator that connects two tables by a shared field. You will join SigninLogs with DeviceProcessEvents to see what an attacker executed on an endpoint after authenticating. You will join OfficeActivity with SigninLogs to correlate suspicious mailbox rules with the sign-in that created them. Join types matter: inner returns only matches, leftanti returns rows with no match (the "what's missing" query that finds accounts without MFA), and leftouter preserves all left-side rows whether they match or not.

Module 5 teaches string parsing: parse, extract, parse_json, and split. Many of the most valuable investigation fields in Sentinel are packed inside JSON strings or delimited text. DeviceDetail in SigninLogs contains the operating system and browser as a JSON object. TargetResources in AuditLogs contains the modified properties as a nested array. Without parsing, these fields are opaque strings. With parsing, they become filterable, aggregatable investigation data.

Module 6 covers advanced filtering: in, !in, has_any, has_all, regular expressions with matches regex, dynamic list filtering, and let statements for reusable variables. You will build filters that reference external threat intelligence lists, exclude known-good entities, and parameterize queries for reuse across investigations.

After Phase 2, you can correlate events across any combination of tables, parse nested fields that contain investigation-critical data, and build reusable query templates. You are no longer a single-table analyst.

Phase 3 — Analysis (K7–K9)

Phase 3 moves from "what happened" to "is this normal." Time-series analysis, graph relationships, and query performance are the tools that separate reactive investigation from proactive detection.

Module 7 teaches time-series operators: make_series, series_decompose, series_stats, and bin. You will build baseline models that show normal sign-in volume by hour and day, then detect anomalies when volume deviates beyond statistical thresholds. A sudden spike in failed authentications at 3 AM is invisible in raw event data. In a time series, it is a signal. Module 7 is where KQL stops being an investigation tool and starts becoming a detection tool.

Module 8 teaches graph analysis: make_graph, graph-match, and relationship traversal across entities. You will map lateral movement paths, identify hub accounts that connect to an unusual number of devices, and visualize trust chains through application consent grants. Graph analysis answers questions that joins cannot: "What is the shortest path between this compromised account and the finance server?"

Module 9 teaches performance optimization (query execution plans, materialized views, let caching, partition pruning, and the cost difference between indexed and non-indexed operations. You will learn why some queries finish in seconds against 90 days of data while others time out against a single day. Performance is not an afterthought) it determines whether your detection rules can run on a schedule without exceeding the 10-minute timeout.

After Phase 3, you can build time-series baselines, detect anomalies statistically, map entity relationships, and write queries that perform at production scale.

Phase 4 — Mastery (K10–K13)

Phase 4 applies everything you have built to the two disciplines that define senior security operations: detection engineering and threat hunting.

Module 10 teaches detection rule engineering: building KQL queries that run as Sentinel analytics rules. You will learn threshold-based detection (password spray patterns, bulk file downloads), baseline anomaly detection (deviations from the time series you built in K7), entity-based detection (impossible travel, new-country sign-ins), and the rule lifecycle from draft through testing to production deployment. The query you write interactively becomes the detection rule that runs every five minutes.

Module 11 teaches threat hunting: hypothesis-driven investigation using KQL. You will translate threat intelligence into executable queries, perform stacking analysis to find rare values in high-volume data, identify first-seen entities, and build hunting notebooks that document your methodology alongside your queries.

Module 12 covers workbooks, dashboards, and operational reporting, turning KQL results into visual outputs for SOC leadership, incident managers, and executive stakeholders. You will build SOC dashboards that show alert volume trends, mean time to detection, and analyst workload distribution. The underlying data source for every visualization is a KQL query.

Module 13 is the capstone. A guided hunting lab where you investigate a simulated multi-stage attack across Northgate Engineering's environment. The lab tests every capability from every phase: filtering, joining, parsing, time-series analysis, anomaly detection, and detection rule creation. You will reconstruct the full attack chain, identify the initial access vector, trace lateral movement, locate the exfiltration, and produce a detection rule that would have caught the attack earlier.

After Phase 4, KQL is no longer a tool you use. It is a language you think in.

The query library you will build

By Module 13, you will have authored and saved approximately 50 production-ready security queries: sign-in investigation queries (failed authentication patterns, impossible travel, MFA fatigue detection), endpoint investigation queries (suspicious process chains, living-off-the-land detection, persistence mechanisms), email investigation queries (inbox rule manipulation, forwarding rule creation, bulk mailbox access), cross-table correlation queries (sign-in to endpoint pivots, email to identity pivots, scope assessment queries), detection pattern queries (threshold, baseline, anomaly, entity-based), and hunting queries (stacking analysis, rare value detection, first-seen entity identification).

Each query is documented with the investigation question it answers, the table and fields it uses, and tuning guidance for adapting it to your environment. By the end of the course, you have a personal investigation toolkit, not a set of bookmarked queries you found online, but queries you wrote, tested, and understand line by line. When a new investigation type arrives (a supply chain compromise, a token theft, a consent phishing attack) you will not need a runbook. You will open the query editor, select the right table, apply the right operators, and build the investigation query from the capabilities you have already mastered. The library is the artifact. The capability is the outcome.

Where this course leads

KQL proficiency is the prerequisite for three advanced Ridgeline courses. Detection Engineering (SEC401) uses KQL to build 71 production analytics rules (without KQL fluency, you would struggle with query syntax while trying to learn detection methodology. Practical Threat Hunting (SEC402) uses KQL for hypothesis-driven investigation) without KQL, you cannot translate threat intelligence into executable searches. Practical Incident Response (FOR401) uses KQL for evidence collection, timeline reconstruction, and scope assessment during active incidents. Without KQL, you rely on portal views that miss critical evidence.

The detection-as-code pipeline reinforces this. Every Sentinel analytics rule is a KQL query wrapped in a schedule and threshold. Every saved hunting query in the Hunting blade is KQL. Every workbook visualization is KQL. Tools that deploy detection rules from version-controlled repositories still use KQL as the query core. The query you write interactively in Module 2 becomes the detection rule you deploy through a CI/CD pipeline in Module 10. Mastering the language is mastering the building block of every automated security workflow in the Microsoft ecosystem.

AI tools like Security Copilot generate draft KQL, but they produce queries that are approximately 70 to 80 percent correct. The remaining 20 to 30 percent requires a human who can read the query, identify the error, and fix it. An analyst who cannot read KQL cannot validate AI-generated output. They deploy incorrect queries, miss threats, and generate false positives that erode SOC trust. AI accelerates analysts who already know KQL. It does not replace the skill.

Mapping the progression to your role

The four phases correspond to real capability thresholds in security operations. After Phase 1, you can answer single-table investigation questions independently. After Phase 2, you can trace an attack across data sources. After Phase 3, you can build the baselines and anomaly detection that power proactive security. After Phase 4, you can author the detection rules and hunting queries that define a mature security program.

Where you spend the most time depends on what your role demands. SOC analysts will get the most immediate value from Phases 1 and 2, where filtering and cross-table joins directly accelerate daily triage. Detection engineers will find Phase 3 and the first half of Phase 4 transformative, because time-series baselines and rule engineering are the core of the discipline. Threat hunters will use all four phases, because hypothesis testing requires the full operator range from filtering through graph traversal.