Welcome back to the blog! In the ever-evolving landscape of cybersecurity, staying ahead of threats requires powerful tools and techniques. Microsoft Defender for Endpoint (MDE), now part of the broader Microsoft Defender XDR suite, offers a robust Endpoint Detection and Response (EDR) capability. One of its most potent features is Advanced Hunting, which allows security analysts and threat hunters to proactively search through vast amounts of raw security data using the Kusto Query Language (KQL).

This post will dive into the world of Defender EDR Advanced Hunting. We'll explore the fundamentals of KQL, highlight essential data tables, provide practical query examples, and share valuable tips and best practices to help you master this critical skill. Whether you're new to KQL or looking to refine your hunting techniques, this guide aims to equip you with the knowledge to effectively leverage Advanced Hunting for enhanced threat detection and response.

Understanding the Foundation: Kusto Query Language (KQL)

Advanced Hunting is built upon the Kusto Query Language (KQL), a powerful, read-only language designed to query large datasets efficiently. If you're familiar with SQL, you'll find some similarities, but KQL has its own distinct syntax and operators optimized for log and telemetry data analysis.

A typical KQL query starts with a table name, followed by a series of operators chained together using the pipe symbol (|). Each operator takes the tabular data from the previous step, processes it, and passes the result to the next operator.

Here's a basic structure:

<TableName>
| <Operator1> <Arguments>
| <Operator2> <Arguments>
...

Key KQL Concepts and Common Operators:

  • Filtering (where): Used to filter rows based on specific conditions. Applying filters early, especially time filters, is crucial for performance.
    • Example: | where Timestamp > ago(1d) and EventType == "ProcessCreated"
  • Projection (project): Selects specific columns to include in the output. Also used for renaming columns or creating calculated columns.
    • Example: | project ProcessName, CommandLine, EventTime = Timestamp
  • Aggregation (summarize): Groups rows with the same values in specified columns and applies aggregation functions (like count(), dcount(), avg(), makeset()).
    • Example: | summarize count() by DeviceName, UserName
  • Joining (join): Merges rows from two tables based on matching values in specified columns. Understanding different join kinds (inner, leftouter, etc.) and optimizing joins is vital.
    • Example: | join kind=inner (DeviceFileEvents) on SHA256
  • Limiting Results (top, limit, take): Restricts the number of rows returned. Useful for initial exploration or managing large result sets.
    • Example: | top 100 by Timestamp desc
  • Extending (extend): Creates new calculated columns.
    • Example: | extend FullProcessPath = strcat(FolderPath, "\\", FileName)
  • String Operators (has, contains, startswith, endswith): Used for matching strings. Case-sensitive versions (has_cs, contains_cs, etc.) are generally more performant. Prefer has over contains when possible.

Microsoft provides excellent documentation and even in-portal training resources to learn KQL. Don't be intimidated; start with simple queries and gradually build complexity.

Defender EDR collects a wealth of telemetry data, organized into various tables within the Advanced Hunting schema. Understanding these tables is fundamental to crafting effective queries. The schema view in the portal provides a list of tables and their columns, which is essential for exploring the available data.

Defender Advanced Hunting Schema Interface


Image: The Advanced Hunting interface showing the Schema tab with various data tables.

Here are some of the most frequently used tables for EDR-related hunting:

  • DeviceEvents: A versatile table capturing various security-related events, including antivirus detections, exploit protection events, Attack Surface Reduction (ASR) rule triggers, and more. Check the ActionType column to filter specific event types.
  • DeviceProcessEvents: Logs process creation events. This is invaluable for tracking program execution, parent-child process relationships, command-line arguments, and process hashes (SHA1, SHA256, MD5).
  • DeviceNetworkEvents: Records network connections initiated or terminated by processes on the device. Essential for tracking C2 communication, data exfiltration attempts, or connections to suspicious IPs/domains (RemoteIP, RemoteUrl, RemotePort).
  • DeviceFileEvents: Tracks file creation, modification, and deletion events. Useful for monitoring malware dropping, file tampering, or suspicious file operations (FileName, FolderPath, SHA1, SHA256).
  • DeviceRegistryEvents: Logs registry key and value creation, modification, and deletion. Critical for detecting persistence mechanisms (e.g., Run keys), configuration changes, or malware artifacts stored in the registry.
  • DeviceImageLoadEvents: Records DLL loading events. Can help identify DLL hijacking, suspicious module loads, or processes loading unexpected libraries.
  • DeviceLogonEvents: Captures user logon activities (interactive, remote, network logons). Important for tracking lateral movement, unauthorized access attempts, or unusual logon patterns (LogonType, AccountName, AccountDomain).
  • EmailEvents, EmailAttachmentInfo, EmailUrlInfo: While often associated with Defender for Office 365, these tables are crucial when investigating threats that originate from email, allowing correlation between email delivery and endpoint activity.
  • UrlClickEvents: Tracks user clicks on URLs within emails or other Office applications, provided Safe Links is enabled.
  • IdentityLogonEvents, IdentityQueryEvents: Provide insights into authentication events and directory service queries, often linked to Defender for Identity.

Remember to explore the schema reference within the Microsoft Defender portal for detailed descriptions of each table and its columns.

Practical Hunting Queries: Examples in Action

Theory is great, but let's see KQL in action with some practical examples relevant to EDR threat hunting. Remember to adjust timeframes (ago(Xd)) and specific indicators based on your investigation.

Example 1: Suspicious PowerShell Execution (Downloads)

PowerShell is a powerful tool often abused by attackers. This query looks for PowerShell processes executing commands commonly associated with downloading payloads.

// Finds PowerShell execution events potentially involving downloads
DeviceProcessEvents
| where Timestamp > ago(1d)
| where FileName in~ ("powershell.exe", "powershell_ise.exe")
// Look for commands indicating download activity
| where ProcessCommandLine has_any("WebClient", "DownloadFile", "DownloadString", "WebRequest", "Invoke-Expression", "IEX", "http", "https")
// Filter out common legitimate update processes if needed (example)
// | where InitiatingProcessFileName !in~ ("updateservice.exe", "msiexec.exe") 
| project Timestamp, DeviceName, AccountName, InitiatingProcessFileName, InitiatingProcessCommandLine, FileName, ProcessCommandLine
| top 100 by Timestamp desc

Explanation: This query filters DeviceProcessEvents for PowerShell executions within the last day. It then searches the command line for keywords related to web requests and downloads. You might need to add further filters to reduce noise from legitimate scripts.

Example 2: Process Execution from Unusual Locations

Malware often runs from temporary directories or user profile locations instead of standard system paths.

// Detects processes launched from potentially suspicious folders
DeviceProcessEvents
| where Timestamp > ago(7d)
// Define suspicious paths (adjust as needed)
| where FolderPath has_any (@"\Users\", @"\AppData\Local\Temp\", @"\Windows\Temp\", @"\PerfLogs\", @"C:\ProgramData\")
// Exclude known legitimate processes if they run from these locations
// | where FileName !in~ ("chrome.exe", "msedge.exe", "OneDrive.exe") 
// Focus on potentially unsigned or less common executables
| where IsSigned == false or SignatureStatus != "Trusted"
| project Timestamp, DeviceName, FileName, FolderPath, ProcessCommandLine, InitiatingProcessFileName, InitiatingProcessCommandLine
| summarize count() by FileName, FolderPath, ProcessCommandLine, DeviceName

Explanation: This query identifies processes running from common temporary or user-specific locations. It further filters for unsigned executables or those with non-trusted signatures, which can be indicative of malicious activity. Summarizing helps identify recurring patterns.

Example 3: Network Connections to Known Bad IPs/Domains

Correlating network activity with threat intelligence feeds is crucial.

// Requires a watchlist 'BadIPs' containing known malicious IP addresses
let BadIPs = externaldata(IP: string) [@"https://path/to/your/bad_ips.csv"] with (format="csv"); // Replace with your TI source
DeviceNetworkEvents
| where Timestamp > ago(1d)
| where RemoteIP in (BadIPs)
| project Timestamp, DeviceName, InitiatingProcessFileName, RemoteIP, RemoteUrl, RemotePort
| take 100

Explanation: This query joins DeviceNetworkEvents with an external data source (like a CSV file or threat intelligence feed URL) containing known malicious IP addresses. It highlights any connections made to these IPs. Note: You need to manage and provide the external threat intelligence source.

Example 4: Detecting Potential Ransomware Activity (File Modifications)

Ransomware rapidly encrypts files, often changing file extensions.

// Looks for rapid file modification/creation with specific extensions
DeviceFileEvents
| where Timestamp > ago(1h) 
| where ActionType in ("FileCreated", "FileRenamed")
// Add known ransomware extensions or patterns
| where FileName has_any (".locked", ".encrypted", ".crypt", ".<random_string>") or PreviousFileName has_any (".locked", ".encrypted", ".crypt")
| summarize FileCount = dcount(FileName), DistinctExtensions = makeset(extract(@'\.([^.]+)$', 1, FileName)) by DeviceName, InitiatingProcessFileName, bin(Timestamp, 5m) // Binning by 5 minutes
| where FileCount > 50 // Threshold for rapid activity (adjust)
| project Timestamp, DeviceName, InitiatingProcessFileName, FileCount, DistinctExtensions

Explanation: This query looks for a high volume (FileCount > 50) of file creation or renaming events within short time intervals (5 minutes), focusing on files with common ransomware-associated extensions. The makeset(extract(...)) part attempts to capture the extensions being used. Thresholds and extensions need careful tuning.

Tips for Effective Advanced Hunting

Mastering Advanced Hunting involves more than just knowing KQL syntax. Here are some tips and best practices gathered from experience and Microsoft's guidance:

  1. Filter Early, Filter Often: Apply time filters (Timestamp > ago(Xd)) and other specific filters (where) as early as possible in your query. This drastically reduces the data processed by subsequent operators, improving performance and reducing resource consumption.
  2. Be Specific: Avoid broad searches. Instead of * or searching across all columns, target specific columns (where FileName == "malware.exe"). Use case-sensitive operators (==, has_cs) when possible for better performance.
  3. Optimize Joins: When using join, place the smaller table on the left. Apply filters to both tables before the join. Understand the different kind options (e.g., inner, leftouter) and use hints (hint.shufflekey) for high-cardinality keys if needed.
  4. Use has over contains: has looks for whole terms and is generally faster than contains, which searches for substrings.
  5. Parse, Don't Extract (Regex Sparingly): Use parse or specific parsing functions (parse_json, parse_csv) instead of generic regex functions (extract, matches regex) whenever possible, as regex can be resource-intensive.
  6. Project Only What You Need: Use project to select only the necessary columns, especially before complex operations like join or summarize.
  7. Understand Quotas: Be aware of CPU resource quotas allocated to your tenant. Monitor query execution time and resource usage (Low, Medium, High). Optimize high-resource queries to avoid throttling.
  8. Leverage IntelliSense and Schema: Use the built-in autosuggest (IntelliSense) and the schema tree in the Advanced Hunting interface to write queries faster and more accurately.
  9. Use Comments: Add comments (// comment) to explain the purpose of your query or specific parts, especially for complex queries or those you plan to save and share.
  10. Stay Updated: Microsoft frequently updates Defender EDR features, data schema, and KQL capabilities. Follow official documentation and security blogs to stay informed.
  11. Audit vs. Block: Understand the difference between Audit and Block modes for features like Attack Surface Reduction (ASR). Audit mode logs events but doesn't prevent them; use it for testing before enforcing rules in Block mode.
  12. RBAC Matters: Especially with features like Live Response, ensure proper Role-Based Access Control (RBAC) is configured. Limit powerful actions and access to sensitive machines (like Domain Controllers) to authorized personnel.

Conclusion

Microsoft Defender EDR's Advanced Hunting provides an incredibly powerful platform for proactive threat hunting and deep investigation. By mastering KQL and understanding the available data, security teams can uncover subtle threats, investigate incidents thoroughly, and significantly improve their organization's security posture. Start experimenting with the queries, explore the schema, apply the optimization tips, and integrate Advanced Hunting into your regular security operations. Happy hunting!

References