Getting started with KQL advanced hunting queries in Microsoft Defender

Microsoft Defender advanced hunting is a powerful feature that allows you to proactively hunt for threats across your endpoints and cloud apps using the Kusto Query Language (KQL). KQL is a rich and expressive language that lets you manipulate and analyze data in a specialized schema. In this blog post, I will show you some basic syntax and how to get started with basic queries.

Basic syntax

A KQL query typically consists of the following elements:

• A table name followed by a pipe ( | ) symbol. This specifies the data source to query from. For example, DeviceProcessEvents is a table that contains information about processes running on devices.
• One or more query operators separated by pipes ( | ). These are functions that perform operations on the data, such as filtering, sorting, aggregating, projecting, and joining. For example, where is an operator that filters the data based on a condition, and project is an operator that selects the columns to display in the results.

Here is an example of a simple query that returns the top 10 processes by CPU usage in the last hour:

DeviceProcessEvents
| where Timestamp > ago(1h)
| summarize avg(CPU) by ProcessName
| top 10 by avg_CPU

In addition to the basic syntax about there are other KQL commands that are commonly used. Here is an expanded list of some of the most common KQL commands used:

• where: Filters the data based on a condition. For example, DeviceProcessEvents | where ProcessName == "powershell.exe" returns only the rows where the process name is PowerShell.
• project: Selects the columns to display in the output. For example, DeviceProcessEvents | project Timestamp, DeviceName, ProcessName returns only the timestamp, device name, and process name columns from the table.
• summarize: Groups the data by one or more columns and calculates aggregations over each group. For example, DeviceProcessEvents | summarize count() by ProcessName returns the number of rows for each process name in the table.
• sort: Sorts the data by one or more columns in ascending or descending order. For example, DeviceProcessEvents | sort by Timestamp desc returns the data sorted by the timestamp column in descending order.
• top: Returns the first N rows of the data when sorted by a column. For example, DeviceProcessEvents | top 10 by CPU returns the top 10 rows with the highest CPU values.
• extend: Creates a new column based on an expression. For example, DeviceProcessEvents | extend ProcessPath = strcat(FolderPath, FileName) creates a new column called ProcessPath that combines the folder path and file name columns.

These are just some of the basic commands that you can use in KQL. For more information, you can check out these resources:

• KQL quick reference: A list of useful KQL functions and their definitions with syntax examples.
• Learn the advanced hunting query language: A Microsoft Learn module that teaches you the fundamentals of KQL and how to use it in advanced hunting.
• Tutorial: Learn common Kusto Query Language operators: A tutorial that shows you how to use the essential KQL operators to access and analyze your data.

To help you learn the advanced hunting query language, you can also use the following resources:

• Microsoft Defender for Endpoint Commonly Used Queries and Examples: This is a blog post that provides a collection of useful queries for different scenarios and use cases.
• Hunt across cloud app activities with Microsoft 365 Defender advanced hunting: This is another blog post that shows you how to hunt for threat activity involving cloud applications such as Exchange Online and Teams.
• MS Defender Advanced Hunting Using KQL Queries: This is a third-party blog post that explains how to use the different types of queries available in advanced hunting, such as shared queries, my queries, and community queries.

I hope this blog post has given you a basic introduction to KQL queries in Microsoft Defender advanced hunting. Happy hunting!

Note: This article was coauthored with Microsoft Copilot.