KQL Core Operators & Tactical Cheat Sheet

KQL (Kusto Query Language) is the primary query engine for Microsoft Sentinel, Microsoft Defender XDR (formerly M365 Defender), and Azure Monitor Log Analytics.

---

⚡ Performance Optimization Rules

[!IMPORTANT] Cardinality Order Matters: Always filter on TimeGenerated / Timestamp first, followed by high-cardinality filters (DeviceName, AccountUpn, FileName) before using string matches or expensive joins.

1. Filter Early: Put where Timestamp > ago(24h) at the top of every query.
2. Use Case-Sensitive Operators when possible:
   • == is faster than =~
   • has_cs is faster than has
   • has is significantly faster than contains (which scans entire substrings).
3. Project Early: Use project or project-away to drop unused columns before large join or summarize operations.
4. Prefer in~ / in with dynamic sets: Avoid chaining dozens of or conditions.

---

🛠️ Core Operators Reference

Aggregations & Summaries (summarize)

• 🎯 What it looks for: Compiles execution frequency, distinct binary hashes (SHA256), and the exact set of user accounts running each executable across endpoints over the past 7 days.
• 💡 Why it matters: Baselining normal execution volume vs rare outliers. Malware droppers, living-off-the-land utilities, and one-off administrative scripts stand out immediately when sorted by low execution count or sudden hash variance across single workstations.

// Summarize counts by computer and list unique usernames observed
DeviceProcessEvents
| where Timestamp > ago(7d)
| summarize
    ExecutionCount = count(),
    UniqueUsers = make_set(AccountName),
    DistinctHashes = dcount(SHA256),
    FirstSeen = min(Timestamp),
    LastSeen = max(Timestamp)
  by DeviceName, FileName
| where ExecutionCount > 1
| sort by ExecutionCount desc

Unpacking Arrays & Nested Objects (mv-expand)

• 🎯 What it looks for: Unpacks multi-value JSON arrays, location dictionaries, or nested role assignments into individual, queryable relational rows.
• 💡 Why it matters: Microsoft Entra logs and M365 audit logs store rich contextual telemetry (e.g. LocationDetails, TargetResources, DeviceProperties) in packed JSON fields. Without mv-expand, analysts cannot perform granular filtering, count occurrences, or correlate nested objects against threat intel IOCs.

// Expand dynamic arrays (e.g. IPs or group memberships) into individual rows
SigninLogs
| where TimeGenerated > ago(24h)
| mv-expand LocationDetails = parse_json(LocationDetails)
| project TimeGenerated, UserPrincipalName, LocationDetails.countryOrRegion, IPAddress

Regular Expressions & Token Extraction (extract, parse)

• 🎯 What it looks for: Inspects process arguments for Base64 encoded payload blocks (-enc, -encodedcommand) and regex-extracts the raw encoded string into a standalone column.
• 💡 Why it matters: Attackers routinely obfuscate malicious PowerShell scripts using Base64 to bypass keyword-based command-line detections. Extracting the payload allows instant decoding via cyberchef or automated KQL base64 decoding to reveal C2 callbacks or staged downloads.

// Extract process command line flags using regex
DeviceProcessEvents
| where Timestamp > ago(24h)
| where ProcessCommandLine has "powershell"
| extend EncodedCommand = extract(@"-(?:e|enc|encodedcommand)\s+([A-Za-z0-9+/=]+)", 1, ProcessCommandLine, typeof(string))
| where isnotempty(EncodedCommand)
| project Timestamp, DeviceName, AccountName, EncodedCommand, ProcessCommandLine

Efficient Table Correlation (join)

• 🎯 What it looks for: Correlates process execution events directly with outbound network socket creation on the same host using matching Process IDs within a 24-hour window.
• 💡 Why it matters: Script interpreters like cmd.exe, powershell.exe, or cscript.exe should rarely make direct outbound TCP connections to external IP addresses. Correlating execution with network events attributes C2 beacons and reverse shells directly to the spawning process and command-line arguments.

// Left outer join matching process execution to outbound network connections
let Lookback = 24h;
DeviceProcessEvents
| where Timestamp > ago(Lookback)
| where FileName in~ ("cmd.exe", "powershell.exe", "wscript.exe", "cscript.exe")
| project ProcessTimestamp = Timestamp, DeviceId, DeviceName, ActionType, FileName, ProcessCommandLine, InitiatingProcessId = ProcessId
| join kind=inner (
    DeviceNetworkEvents
    | where Timestamp > ago(Lookback)
    | where RemotePort in (443, 80, 8080, 8443, 4444)
    | project NetworkTimestamp = Timestamp, DeviceId, RemoteIP, RemotePort, RemoteUrl, InitiatingProcessId
) on DeviceId, InitiatingProcessId
| project DeviceName, FileName, ProcessCommandLine, RemoteIP, RemotePort, RemoteUrl, ProcessTimestamp, NetworkTimestamp

---

⏱️ Useful Time & Bin Bucketing Functions

• 🎯 What it looks for: Groups security alert volumes into discrete 1-hour time buckets across a 14-day timeline.
• 💡 Why it matters: Threat hunting requires spotting sudden bursts of activity (such as password sprays, automated lateral movement, or ransomware pre-encryption reconnaissance) compared against steady-state baseline noise.

// Group alerts into 1-hour intervals for trend / spike analysis
SecurityAlert
| where TimeGenerated > ago(14d)
| summarize AlertCount = count() by bin(TimeGenerated, 1h), AlertSeverity, ProviderName
| render timechart

---

🔎 String Operators Comparison

Operator	Case-Sensitive?	Token-Based?	Relative Speed	Best Used For
has	No	Yes (indexed words)	⚡⚡⚡ Very Fast	Matching whole terms, filenames, IPs
has_cs	Yes	Yes (indexed words)	⚡⚡⚡ Fastest	Exact casing known
contains	No	No (scans substring)	🐢 Slow	Substring matching within long paths
startswith	No	No	⚡⚡ Fast	Checking paths or command prefixes
matches regex	Configurable	No	🐢 Slowest	Complex pattern extraction (use late)