KQL for Threat Hunting: Essential Queries Every Analyst Should Know

KQL (Kusto Query Language) is the query language behind Microsoft Sentinel, Microsoft Defender XDR, and Azure Monitor. If your organization runs any Microsoft security stack, KQL is the most important language you can invest in. This guide covers the core operators and five essential hunting queries every analyst should have in their toolkit.

KQL Fundamentals

KQL is a read-only query language with a pipe (|) syntax. Each statement starts with a table and passes results through operators.

Core Operators

// Filter rows
| where FileName == "powershell.exe"
| where TimeGenerated > ago(24h)
| where isnotempty(CommandLine)

// Select columns
| project TimeGenerated, DeviceName, AccountName, FileName, CommandLine

// Create new columns
| extend ProcessPath = strcat(FolderPath, "\\", FileName)

// Summarize (aggregate)
| summarize Count=count() by DeviceName, AccountName

// Sort results
| sort by TimeGenerated desc

// Limit output
| take 100

// Join tables
| join kind=inner (
    DeviceNetworkEvents
    | where RemotePort == 443
) on DeviceId

String Functions Most Useful for Hunting

// Case-insensitive contains
| where CommandLine has "mimikatz"

// Match any item in a list
| where FileName has_any ("psexec.exe", "wmic.exe", "bitsadmin.exe")

// Regular expression
| where CommandLine matches regex @"(?i)(net\s+user|net\s+group)"

// Extract with regex
| extend Domain = extract(@"\\([^\\]+)\\", 1, AccountName)

// Base64 decode inline
| extend DecodedCommand = base64_decode_tostring(EncodedPayload)

Essential Hunting Query 1: Unusual Process Execution Chains

LOLBins (Living-off-the-Land Binaries) are legitimate Windows tools abused by attackers. This query finds suspicious parent-child process relationships:

DeviceProcessEvents
| where TimeGenerated > ago(7d)
| where InitiatingProcessFileName in~ ("winword.exe", "excel.exe", "outlook.exe", "mshta.exe", "wscript.exe", "cscript.exe")
| where FileName in~ ("cmd.exe", "powershell.exe", "pwsh.exe", "wmic.exe", "certutil.exe", "bitsadmin.exe", "rundll32.exe")
| project TimeGenerated, DeviceName, AccountName,
          ParentProcess=InitiatingProcessFileName,
          ChildProcess=FileName,
          CommandLine
| sort by TimeGenerated desc

Why it matters: Office apps spawning command shells is a classic indicator of macro-based malware or phishing document exploitation (T1566.001).

Essential Hunting Query 2: Impossible Travel Detection

Flags logins from two geographically distant locations within a short window — a strong indicator of credential compromise:

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == 0  // Successful sign-in
| project TimeGenerated, UserPrincipalName, Location,
          IPAddress, AppDisplayName
| sort by UserPrincipalName, TimeGenerated asc
| extend PreviousTime = prev(TimeGenerated, 1),
         PreviousLocation = prev(Location, 1),
         PreviousUser = prev(UserPrincipalName, 1)
| where UserPrincipalName == PreviousUser
| where Location != PreviousLocation
| extend TimeDiffMinutes = datetime_diff('minute', TimeGenerated, PreviousTime)
| where TimeDiffMinutes between (0 .. 120)  // Within 2 hours
| where isnotempty(PreviousLocation)
| project TimeGenerated, UserPrincipalName, 
          Location1=PreviousLocation, Location2=Location,
          TimeDiffMinutes, IPAddress
| sort by TimeDiffMinutes asc

Tuning tip: Add | where not (Location has "VPN") if your org uses a known VPN exit country to reduce noise.

Essential Hunting Query 3: Brute Force & Password Spray Detection

Password spraying uses one common password across many accounts to avoid lockout. This query differentiates spraying (many accounts, one IP) from brute force (one account, many attempts):

SigninLogs
| where TimeGenerated > ago(1h)
| where ResultType == 50126  // Invalid credentials
| summarize
    AttemptCount = count(),
    DistinctAccounts = dcount(UserPrincipalName),
    DistinctIPs = dcount(IPAddress),
    Accounts = make_set(UserPrincipalName, 20)
    by IPAddress, bin(TimeGenerated, 10m)
| extend AttackType = case(
    DistinctAccounts > 10 and DistinctIPs == 1, "Password Spray",
    DistinctAccounts == 1 and AttemptCount > 20, "Brute Force",
    "Suspicious"
  )
| where AttemptCount > 5
| sort by AttemptCount desc

Essential Hunting Query 4: Data Exfiltration by Volume

Unusual outbound data volume can indicate exfiltration. This baselining approach finds devices sending significantly more data than their 30-day average:

let lookback = 30d;
let threshold = 3.0;  // Standard deviations

let baseline = DeviceNetworkEvents
| where TimeGenerated > ago(lookback)
| where ActionType == "ConnectionSuccess"
| where RemoteIPType == "Public"
| summarize DailyBytes=sum(SentBytes) by DeviceId, DeviceName, bin(TimeGenerated, 1d)
| summarize AvgBytes=avg(DailyBytes), StdDev=stdev(DailyBytes) by DeviceId, DeviceName;

DeviceNetworkEvents
| where TimeGenerated > ago(1d)
| where ActionType == "ConnectionSuccess"
| where RemoteIPType == "Public"
| summarize TodayBytes=sum(SentBytes) by DeviceId, DeviceName
| join kind=inner baseline on DeviceId
| extend ZScore = (TodayBytes - AvgBytes) / (StdDev + 1)
| where ZScore > threshold
| project DeviceName, TodayBytes, AvgBytes, ZScore
| sort by ZScore desc

Essential Hunting Query 5: Lateral Movement via SMB

Attackers use SMB to move laterally and drop tools on remote hosts. This query looks for unusual SMB connections, especially from workstations to workstations:

DeviceNetworkEvents
| where TimeGenerated > ago(24h)
| where RemotePort == 445
| where ActionType == "ConnectionSuccess"
| join kind=inner (
    DeviceInfo
    | where DeviceType == "Workstation"
    | project RemoteDeviceId=DeviceId, RemoteDeviceName=DeviceName
) on $left.RemoteIP == $right.RemoteDeviceId
| summarize
    ConnectionCount = count(),
    DistinctTargets = dcount(RemoteIP)
    by DeviceId, DeviceName, bin(TimeGenerated, 1h)
| where DistinctTargets > 5 or ConnectionCount > 50
| sort by DistinctTargets desc

Note: In most environments, workstation-to-workstation SMB is unusual. Servers communicating over SMB is expected (file shares, domain controllers), so scope accordingly.

Query Optimization Tips

1. Filter early and filter on indexed fields:

// Good — TimeGenerated is indexed, filter first
DeviceProcessEvents
| where TimeGenerated > ago(24h)
| where FileName == "powershell.exe"

// Bad — full table scan before time filter
DeviceProcessEvents
| where FileName == "powershell.exe"
| where TimeGenerated > ago(24h)

2. Use has instead of contains for faster string matching:

// Faster — uses inverted index
| where CommandLine has "mimikatz"

// Slower — requires substring scan
| where CommandLine contains "mimikatz"

3. Use summarize with dcount for approximate distinct counts at scale:

// Use dcount for large datasets (faster, ~2% error)
| summarize EstimatedUnique = dcount(AccountName)
// Use count_distinct for exact counts (slower)
| summarize ExactUnique = count_distinct(AccountName)

Building a KQL Hunting Library

The real power of KQL comes from building a reusable query library. A few practices:

• Save queries as functions in Sentinel to reuse across notebooks and workbooks
• Version control your queries in a Git repo alongside your SIGMA rules
• Parameterize time ranges using let timeRange = 7d; at the top so users can easily adjust scope
• Document false positives in query comments so the next analyst knows what to expect

KQL is one of those tools where 20% of the operators handle 80% of real-world hunting tasks. Master where, project, summarize, join, and extend — everything else builds on those.