Module 6: Create Queries for Microsoft Sentinel Using Kusto Query Language (KQL)

What this module is about

Kusto Query Language (KQL) is the query language used across Microsoft Sentinel, Microsoft Defender XDR Advanced Hunting, and Azure Monitor. Every investigation you conduct, every detection rule you build, every threat hunt you execute, and every workbook you create runs on KQL. It is not a supporting skill for security operations — it is the primary skill.

This module teaches you to write KQL from scratch. Not to copy queries from documentation and run them, but to understand the language deeply enough that when you face a scenario you have never seen before — an unfamiliar alert, an unusual data source, a complex investigation question — you can construct the query you need, run it, interpret the results, debug it when it fails, and optimize it when it runs slowly.

By the end of this module, you will not just know KQL syntax. You will think in KQL. When your CISO asks “how many users clicked the phishing link,” your mind will immediately construct the query before you touch the keyboard. That is the level of fluency this module builds.

What you will be able to do after completing this module

• Write KQL queries from a blank editor for any investigation scenario
• Use the core operators (where, project, extend, summarize, let, join, union) with confidence and understand when each is the right choice
• Build queries incrementally — one pipe at a time — and read intermediate results to guide your next step
• Filter, aggregate, and join data across the security tables in Sentinel and Defender XDR
• Work with string data: extract fields from unstructured text, parse JSON columns, manipulate timestamps
• Debug failing queries systematically using elimination
• Write queries that perform well on tables with millions of rows
• Apply KQL patterns that you will reuse in every investigation module (11-15)

How this module is structured

This module has 8 content subsections, a Module Summary, and a Check My Knowledge assessment. Each subsection builds on the previous one — the operators you learn in 6.1 are used in 6.2, the summarization skills from 6.2 feed into the multi-table joins in 6.3, and so on.

6.1 — Construct KQL Statements for Microsoft Sentinel. The foundation. You will learn how KQL thinks (the pipe model), how to filter data with where, shape output with project and extend, reuse logic with let, and sort results for investigation. By the end of this subsection, you will build a complete investigation query from scratch that tells the story of a brute-force attack.

6.2 — Analyze Query Results Using KQL. Aggregation and visualization. You will learn to summarize data with summarize and its aggregation functions (count, dcount, sum, avg, make_set, make_list), group results by dimensions (user, IP, time), render visualizations (bar charts, time series), and use the bin() function for time-based grouping. By the end, you can answer questions like “how many failed sign-ins per country per hour” in a single query.

6.3 — Build Multi-Table Statements Using KQL. Joining data across tables. You will learn union (combining rows from multiple tables) and join (matching rows between tables on a shared key). This is where KQL becomes powerful for security — correlating sign-in data with email data with endpoint data to trace an attack across your environment. By the end, you can write the cross-table correlations used in the Module 11 AiTM investigation.

6.4 — Work with String Data in KQL. Text extraction and manipulation. You will learn extract (regex-based extraction), parse (pattern-based extraction), split, substring, and string functions for manipulating the unstructured and semi-structured text fields that appear throughout security log data. By the end, you can extract usernames from email addresses, parse IP addresses from free-text messages, and pull structured data from JSON fields.

6.5 — Security-Specific KQL Patterns. The queries every SOC analyst needs. This subsection is our addition — not in Microsoft Learn. You will learn the investigation patterns that recur across every security scenario: the failed-then-succeeded pattern (brute force detection), the impossible travel pattern, the first-time-seen pattern, the anomalous volume pattern, the entity timeline pattern, and the IOC sweep pattern. Each pattern is taught with a real investigation context and a reusable query template.

6.6 — Building an Investigation Query Library. Moving from ad-hoc queries to organized, reusable tools. You will learn to structure your queries for reuse, parameterize them with let for different investigations, organize them into investigation playbook sets, and build a personal query library that accelerates every future investigation.

6.7 — KQL Performance Optimization and Query Debugging. Making queries fast and fixing them when they break. You will learn why queries are slow (full table scans, cartesian joins, inefficient string operators), how to read query statistics, and systematic debugging techniques. This subsection directly improves your effectiveness during time-critical investigations where a 30-second query versus a 3-second query makes a material difference.

6.8 — Real-World Query Building Exercises. Scaffolded practice from simple to complex. Five investigation scenarios of increasing difficulty, each requiring you to construct queries from a blank editor with only the scenario description as input. No hints, no starter queries — just the question and the data. This is where you prove to yourself that the module worked.

6.9 — Module Summary. Key takeaways, skill checklist, SC-200 exam objectives covered, and bridge to Module 7.

6.10 — Check My Knowledge. 20 scenario-based questions testing whether you can apply KQL to real investigation situations.

Sections in this module