Learning basic KQL for the SC-200

Learning KQL for SC-200: Deep Dive into Core Principles

Jan 08, 2025

Recently, someone preparing for the SC-200 exam came to me and said, “KQL is my weakest skill—how can I improve it?” As someone that has already been there (And myself no master of KQL) I understood their challenge. KQL can seem overwhelming at first, (From a non coding or click-ops background) but the truth is, you don’t need to master every operator or function to get thru the exam—or in real-world scenarios.

The secret to mastering KQL lies in focusing on the foundational 20% of concepts that enable you to solve 80% of practical problems. These core principles, when applied effectively, can help you analyse failed logins, identify suspicious activities, and correlate alerts with sign-ins—all essential tasks for both the SC-200 and using MS security tooling in the future!

This guide is my response to that question. It’s designed to help those preparing for the SC-200 focus on the inital most impactful parts of KQL, providing the skills and confidence to not only pass the exam but also apply KQL effectively in real-world scenarios.

The simplest way I explain KQL is by comparing it to working with multiple Excel spreadsheets. Imagine you have thousands of rows of data spread across different sheets. KQL acts like a powerful filter and processing tool that helps you sift through that data, table by table, row by row and column by column, in a logical, step-by-step way.

Think of each pipe (|) in a KQL query as a command that refines or transforms the data before passing it along to the next step. It's like saying, “Filter this data,” then “Pick these columns,” and finally “Sort the results”—all in one fluid motion. Each step produces a result, which is then fed into the next operation, much like how you’d manipulate data in PowerShell commands or use Excel formulas to break down and analyze your data iteratively.

Here’s an example to illustrate this process:

Most Basic Example:

Imagine you’re investigating failed login attempts. Here’s how KQL processes the data:

Step 1: Start with your dataset. You begin with a table, like the SecurityEvent table, which contains raw data about logon events.

SecurityEvent

This is your starting point—like opening a spreadsheet with thousands of rows of raw logon data.
Step 2: Filter the data. Use the where clause to narrow it down to failed logon attempts, equivalent to applying a filter in Excel.

SecurityEvent | where EventID == 4625

Now, instead of sifting through irrelevant rows, you’re only looking at rows related to failed logons.
Step 3: Select specific columns. Use the project operator to focus on just the columns you need, like TimeGenerated, AccountName, and Computer.

SecurityEvent 

| where EventID == 4625 
| project TimeGenerated, AccountName, Computer

This is like hiding all the other columns in Excel to make the data easier to analyze.
Step 4: Sort the data. Finally, you can use the order by operator to sort the results, such as ordering the failed logons by the time they occurred.

SecurityEvent 
| where EventID == 4625 
| project TimeGenerated, AccountName, Computer 
| order by TimeGenerated desc

At this point, you have a neat, organized view of the data that’s ready for further analysis.

Why this analogy works:

Pipes (|): Each pipe acts like a new formula or filter in Excel that processes and refines the data step by step.
Logical flow: KQL queries are sequential and build on each other, just like a well-organized series of Excel actions.
Dynamic results: You can iteratively refine your query by adding more steps, similar to tweaking Excel formulas to get the exact output you need.
Automation: Unlike Excel, which often requires manual adjustments, KQL allows you to automate these operations on massive datasets with ease.

This step-by-step, modular approach makes KQL intuitive once you grasp the basics, allowing you to quickly derive actionable insights from complex datasets.

Now lets look at a 10 step guide to some other key functions to know!

1. Understanding KQL Syntax Basics

The Foundation:

Query Structure: KQL queries are a series of statements connected by pipes (|). Each statement processes data and passes it to the next step.
Case Sensitivity:
- Keywords and operators are case-insensitive (e.g., where, WHERE).
- Field names and string values are case-sensitive.
Best Practice: Write clean, readable queries by formatting them with line breaks after each pipe (|).

Example Query:

SecurityEvent
| where EventID == 4625
| project TimeGenerated, AccountName, Computer
| order by TimeGenerated desc

This query retrieves failed logon events, selects key columns, and orders the results by time.

2. Core Operators and Their Use Cases

Filtering Data with `where`

Purpose: Extract rows that meet specific conditions.
Syntax:

TableName 
| where Condition

Examples:
- | where EventID == 4625: Filter for failed logon events.
- | where AccountName startswith "admin": Retrieve accounts starting with "admin".

Selecting Columns with `project`

Purpose: Limit the output to specific columns for clarity and performance.
Example:

SecurityEvent 
| project TimeGenerated, AccountName

Adding New Columns with `extend`

Purpose: Create calculated columns for better analysis.
Example:

| extend AccountNameLength = strlen(AccountName)

Aggregating Data with `summarize`

Purpose: Group and calculate summary statistics.
Example:

| summarize TotalEvents = count() by EventID

Sorting Data with `order by`

Purpose: Arrange data in ascending or descending order.
Example:

| order by TimeGenerated desc

3. Advanced Filters and Expressions

Logical Operators

Combine multiple conditions using and, or, and not.
Example:

| where EventID == 4625 and AccountName contains "admin"

String Operations

Use functions like startswith, contains, has, and endswith to filter textual data.
Examples:
- | where Computer has "server"
- | where AccountName endswith "123"

4. Transforming Data

Parsing Fields

Purpose: Extract or manipulate structured fields within logs.
Examples:
- Using parse: | parse Message with "User=" Username ";"
- Using extract: | extend ExtractedField = extract("User=(\w+)", 1, RawData)

Time Functions

Group events into time intervals with bin.
Example:

| summarize count() by bin(TimeGenerated, 1h)

5. Aggregations

Common Functions

count(): Total number of rows.
sum(): Total of a numerical column.
avg(): Average of a numerical column.
min() / max(): Minimum or maximum value.

Grouping Data

Use summarize with by to group and calculate statistics.
Example:

| summarize TotalLogons = count() by AccountName

6. Joining and Enriching Data

Joins

Combine datasets using join.
Syntax:

Table1 | join kind=inner (Table2) on KeyField

Example: Enrich failed logons with account details:

SecurityEvent
| where EventID == 4625
| join kind=inner (UserDetails) on AccountName

Lookup

Simplify enrichment for small datasets.
Example:

| lookup kind=leftouter UserDetails on AccountName

7. Visualization

Adding Visualizations

Use render to display data as charts.
Examples:
- Time chart:

| summarize count() by bin(TimeGenerated, 1h)
| render timechart

- Bar chart:

| summarize count() by AccountName
| render barchart

8. Practical SC-200 Scenarios

Detecting Failed Logons

SecurityEvent
| where EventID == 4625
| summarize FailedAttempts = count() by AccountName, bin(TimeGenerated, 1h)
| order by FailedAttempts desc

Monitoring Suspicious Activity

AzureDiagnostics
| where OperationName contains "Delete"
| summarize DeleteCount = count() by CallerIpAddress, bin(TimeGenerated, 1d)

Correlating Alerts with Sign-Ins

SecurityAlert
| join kind=inner (SignInLogs) on AccountName
| where AlertSeverity == "High"
| project TimeGenerated, AccountName, AlertName, IPAddress

9. Performance Optimization

Best Practices:

Filter Early: Use where as early as possible to reduce the dataset size.
Project Columns: Limit output fields with project.
Indexed Fields: Use indexed fields (like TimeGenerated) for filtering.
Avoid Complex Joins: Simplify joins by pre-filtering datasets.

10. Learning by Practice

MUST WATCH

Learn KQL with Kusto Detective Agency

Recommended Tools:

Kusto Detective Agency

By mastering these core principles, you’ll be well-equipped to pass the SC-200 exam and apply KQL effectively in real-world scenarios. Let me know if you need further clarification or additional practice exercises!

Some more advanced resources -

Learn KQL with the Must Learn KQL series and book
KQLQuery.com - Blog posts about KQL and different use cases
KQLSearch.com - Search Engine for KQL Queries
Log Analytics Demo Lab: aka.ms/LADemo

Hands-On Learning with Microsoft Interactive Labs SC-200

👉 Apply Microsoft Defender for Office 365 preset security policies
🧪 https://lnkd.in/d6BqZJtv

👉 Deploy Microsoft Defender for Endpoint
🧪 https://lnkd.in/dd_Vj9VT

👉 Mitigate Attacks with Microsoft Defender for Endpoint
🧪 https://lnkd.in/d273kEnd

👉 Enable Microsoft Defender for Cloud
🧪 https://lnkd.in/d_nGMapG

👉 Mitigate threats using Microsoft Defender for Cloud
🧪 https://lnkd.in/drJmedgr

👉 Create queries for Microsoft Sentinel using Kusto Query Language (KQL)
🧪 https://lnkd.in/dQnQ_iEZ

👉 Configure your Microsoft Sentinel environment
🧪 https://lnkd.in/dr4akkUX

👉 Connect data to Microsoft Sentinel using data connectors
🧪 https://lnkd.in/dtRmAwFa

👉 Connect Windows devices to Microsoft Sentinel using data connectors
🧪 https://lnkd.in/dpxmvabA

👉 Connect Linux hosts to Microsoft Sentinel using data connectors
🧪 https://lnkd.in/dua8fHNm

👉 Create workbooks
🧪 https://lnkd.in/dnawKhcP

👉 Use Repositories in Microsoft Sentinel
🧪 https://lnkd.in/daQkDy9N

Check out the full set of SC-200 labs on the blog: https://lnkd.in/dqjrWG-k

Thanks for reading Marcus Burnap - Microsoft Security - Sentinel - Defender XDR! This post is public so feel free to share it.

#MicrosoftSecurity

#MicrosoftLearn

#CyberSecurity

#MicrosoftSecurityCopilot

#Microsoft

#MSPartnerUK

#msftadvocate

Marcus Burnap - Microsoft Security - Sentinel - Defender XDR