Threat Hunting with KQL: Advanced Techniques for Microsoft Sentinel

Introduction

Threat hunting is a proactive security approach that focuses on searching for malicious activities that have evaded existing security controls. Microsoft Sentinel, with its powerful Kusto Query Language (KQL), provides an excellent platform for threat hunting. This article explores advanced KQL techniques that security analysts can use to hunt for threats effectively.

Understanding KQL Fundamentals

Before diving into advanced techniques, let's review some KQL fundamentals:

• KQL is a read-only query language similar to SQL but optimized for log analytics
• It uses pipes (|) to chain operations together
• It supports a wide range of operators for filtering, aggregating, and joining data
• It can process massive datasets efficiently

Advanced KQL Techniques for Threat Hunting

1. Time Window Analysis

Identify unusual patterns within specific time windows:

// Find login attempts outside business hours
SigninLogs
| where TimeGenerated > ago(7d)
| extend hour = datetime_part("hour", TimeGenerated)
| where hour < 6 or hour > 18
| where ResultType == "0" // Successful logins
| summarize count() by UserPrincipalName, bin(TimeGenerated, 1h)
| where count_ > 10

2. Statistical Anomaly Detection

Use statistical methods to identify outliers:

// Detect unusual number of failed logins
SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType != "0"
| summarize FailedLogins=count() by UserPrincipalName, bin(TimeGenerated, 1d)
| summarize avg_failures=avg(FailedLogins), stdev_failures=stdev(FailedLogins), max_failures=max(FailedLogins) by UserPrincipalName
| extend threshold = avg_failures + (2 * stdev_failures)
| where max_failures > threshold and max_failures > 5

3. Entity Correlation

Correlate activities across different entities:

// Correlate successful logins after multiple failures from different locations
let failedLogins = SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType != "0"
| summarize failedAttempts=count() by UserPrincipalName, IPAddress, bin(TimeGenerated, 15m)
| where failedAttempts > 5;

let successfulLogins = SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == "0"
| project TimeGenerated, UserPrincipalName, IPAddress, Location;

failedLogins
| join kind=inner (
    successfulLogins
) on UserPrincipalName
| where TimeGenerated > TimeGenerated1
| where TimeGenerated < TimeGenerated1 + 1h
| where IPAddress != IPAddress1
| project UserPrincipalName, FailedIPAddress=IPAddress, FailedLocation=Location, SuccessIPAddress=IPAddress1, SuccessLocation=Location1, TimeGenerated, TimeGenerated1

4. Behavioral Baselining

Establish baselines and look for deviations:

// Detect unusual process execution patterns
DeviceProcessEvents
| where TimeGenerated > ago(30d)
| summarize ProcessCount=count() by DeviceName, ProcessName, bin(TimeGenerated, 1d)
| summarize avg_count=avg(ProcessCount), stdev_count=stdev(ProcessCount), max_count=max(ProcessCount) by DeviceName, ProcessName
| extend upper_threshold = avg_count + (3 * stdev_count)
| where max_count > upper_threshold and max_count > 10

5. Rare Event Detection

Identify rare or first-time occurrences:

// Find rare PowerShell commands
DeviceProcessEvents
| where TimeGenerated > ago(30d)
| where FileName =~ "powershell.exe"
| where ProcessCommandLine != ""
| summarize count() by ProcessCommandLine
| where count_ <= 3
| order by count_ asc

Practical Hunting Scenarios

Scenario 1: Detecting Lateral Movement

// Detect potential lateral movement using administrative tools
let timeframe = 1d;
let adminTools = dynamic(["net.exe", "runas.exe", "psexec.exe", "wmic.exe", "mmc.exe", "powershell.exe", "sc.exe"]);

DeviceProcessEvents
| where TimeGenerated > ago(timeframe)
| where FileName in~ (adminTools)
| where ProcessCommandLine has_any("user", "group", "localgroup", "member", "session", "computer", "share", "domain")
| summarize CommandCount=count() by DeviceName, AccountName, ProcessCommandLine
| join kind=leftouter (
    DeviceProcessEvents
    | where TimeGenerated > ago(30d) and TimeGenerated < ago(timeframe)
    | where FileName in~ (adminTools)
    | where ProcessCommandLine has_any("user", "group", "localgroup", "member", "session", "computer", "share", "domain")
    | summarize HistoricalCount=count() by DeviceName, AccountName, ProcessCommandLine
) on DeviceName, AccountName, ProcessCommandLine
| where isempty(HistoricalCount) or CommandCount > HistoricalCount*2

Scenario 2: Identifying Data Exfiltration

// Detect potential data exfiltration via email
let timeframe = 7d;
let dataExfilKeywords = dynamic(["confidential", "secret", "private", "proprietary", "classified"]);

EmailEvents
| where TimeGenerated > ago(timeframe)
| where SenderFromAddress has "@yourdomain.com"
| where RecipientEmailAddress !has "@yourdomain.com"
| where Subject has_any(dataExfilKeywords) or AttachmentNames has_any(dataExfilKeywords)
| extend AttachmentCount = array_length(AttachmentNames)
| extend AttachmentSizes = AttachmentSizes
| extend TotalAttachmentSize = array_sum(AttachmentSizes)
| where AttachmentCount > 3 or TotalAttachmentSize > 10000000 // 10MB
| project TimeGenerated, SenderFromAddress, RecipientEmailAddress, Subject, AttachmentNames, AttachmentCount, TotalAttachmentSize

Building a Threat Hunting Program

Effective threat hunting requires more than just good queries:

1. Develop Hunting Hypotheses

• Start with a specific hypothesis based on threat intelligence
• Focus on techniques used by threat actors targeting your industry
• Consider your organization's specific vulnerabilities

2. Create a Hunting Calendar

• Schedule regular hunting sessions
• Rotate through different techniques and attack vectors
• Allocate dedicated time for exploratory hunting

3. Document and Share Findings

• Maintain a library of effective hunting queries
• Document both positive and negative findings
• Share insights with the broader security team

4. Automate Successful Hunts

• Convert successful hunting queries into scheduled analytics rules
• Implement automated response actions where appropriate
• Continuously refine detection logic based on new findings

Conclusion

Advanced KQL techniques can significantly enhance threat hunting capabilities in Microsoft Sentinel environments. By mastering these techniques and applying them in a structured hunting program, security teams can proactively identify threats that might otherwise remain hidden. Remember that effective threat hunting is an iterative process that combines technical skills with creativity and critical thinking.