KQL Cheat Sheet: Essential Microsoft Sentinel Queries for SOC Analysts

If your SOC runs on Microsoft Sentinel or Defender, SPL won't help you. You need KQL.

Kusto Query Language is how you actually hunt in the Microsoft security stack — Sentinel, Defender for Endpoint, Advanced Hunting, all of it. And if you already know SPL from the Splunk world, the good news is KQL clicks fast once you see the patterns.

Here are the KQL queries that show up in most Tier 1 and Tier 2 work. Bookmark it.

Why KQL Matters

Microsoft's security tooling is everywhere now, and it all speaks KQL. Learning it opens up a huge chunk of the SOC job market that pure-Splunk analysts can't touch.

The language reads left to right: you start with a table, then pipe it through operators that filter, shape, and summarize. Once that clicks, the rest is just vocabulary.

Let's build it up query by query.

Query 1: Basic Filtering with where

What it does: Pull specific events from a table.

SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4625

ago(24h) is your time range. where filters. This is the foundation of every query you'll write.

Query 2: Pick Your Columns with project

What it does: Trim the noise to just the fields you care about.

SecurityEvent
| where EventID == 4625
| project TimeGenerated, Account, IpAddress, Computer

project is KQL's version of "just show me these columns." Use it constantly — it makes results readable.

Query 3: Count and Group with summarize

What it does: Aggregate events — the KQL equivalent of stats count by.

SecurityEvent
| where EventID == 4625
| summarize FailedLogons = count() by Account, IpAddress
| sort by FailedLogons desc

This surfaces brute-force patterns instantly: which accounts and IPs have the most failed logons.

Query 4: Failed Logon Investigation

What it does: Find accounts getting hammered with failed logins.

SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == 4625
| summarize Attempts = count() by TargetAccount, IpAddress
| where Attempts > 10

Anything over your threshold is worth a look. This is one of the most-run queries in any SOC.

Query 5: Successful Logon After Failures

What it does: Catch a brute force that finally worked.

SecurityEvent
| where EventID in (4624, 4625)
| summarize Fails = countif(EventID == 4625), Success = countif(EventID == 4624) by Account, IpAddress
| where Fails > 10 and Success > 0

countif counts conditionally. A pile of failures followed by a success is a much stronger signal than either alone.

Query 6: Process Creation Hunting

What it does: Hunt suspicious processes in Defender for Endpoint.

DeviceProcessEvents
| where TimeGenerated > ago(24h)
| where InitiatingProcessFileName == "winword.exe"
| where FileName in ("powershell.exe", "cmd.exe", "wscript.exe")
| project TimeGenerated, DeviceName, InitiatingProcessFileName, FileName, ProcessCommandLine

Office apps spawning shells — the classic macro-payload pattern, caught in one query.

Query 7: Encoded PowerShell

What it does: Find hidden, encoded PowerShell commands.

DeviceProcessEvents
| where FileName == "powershell.exe"
| where ProcessCommandLine has_any ("-enc", "-EncodedCommand", "-w hidden", "-nop")
| project TimeGenerated, DeviceName, AccountName, ProcessCommandLine

has_any matches any term in the list. These flags together are a near-universal sign of something trying to stay hidden.

Query 8: Network Connections to Rare Destinations

What it does: Spot outbound connections to uncommon IPs.

DeviceNetworkEvents
| where TimeGenerated > ago(24h)
| where RemotePort == 443
| summarize Connections = count() by RemoteIP, InitiatingProcessFileName
| sort by Connections asc

Sorting ascending surfaces the rarest destinations — often where beaconing hides.

Query 9: Enrich with extend

What it does: Create calculated fields on the fly.

SecurityEvent
| where EventID == 4624
| extend Hour = datetime_part("hour", TimeGenerated)
| where Hour >= 0 and Hour <= 5
| project TimeGenerated, Account, Computer, Hour

extend adds a computed column. Here it isolates logons during off-hours — a simple but effective anomaly filter.

Query 10: Time Bucketing with bin

What it does: Group events into time windows for trend analysis.

SecurityEvent
| where EventID == 4625
| summarize Failures = count() by bin(TimeGenerated, 1h)
| sort by TimeGenerated asc

bin() buckets timestamps — here into hourly windows, so you can see a spike the moment it happens.

Query 11: Joining Tables

What it does: Correlate across two data sources.

SigninLogs
| where ResultType == 0
| join kind=inner (
    DeviceNetworkEvents
    | where RemotePort == 3389
) on $left.UserPrincipalName == $right.InitiatingProcessAccountName

join connects tables — the same correlation muscle you'd use in SPL, just different syntax. This is where real investigation starts.

Pro Tips for Writing Better KQL

1. Filter early, filter often. Put your where clauses (especially the time range) as high as possible. KQL is fast, but a tight filter up front keeps it that way.

2. has beats contains. has is term-indexed and much faster than contains for whole-word matches. Use contains only when you truly need substring matching.

3. project before you scroll. Trimming columns early makes results readable and queries faster.

4. Learn the tables. SecurityEvent, SigninLogs, DeviceProcessEvents, DeviceNetworkEvents — knowing which table holds what is half the battle.

TL;DR – The KQL Patterns That Matter

where filters, project trims columns, summarize aggregates, extend calculates, bin buckets time, and join correlates. Master those six operators and you can write most of the queries a SOC shift demands. The syntax differs from SPL, but the investigative thinking is identical.

---

FAQs

Is KQL hard to learn if I already know SPL?

No — the concepts map almost one to one (stats count by → summarize count() by, etc.). You're mostly learning new syntax for skills you already have.

Where is KQL actually used?

Microsoft Sentinel, Defender for Endpoint (Advanced Hunting), Azure Monitor, and Log Analytics. If an org runs the Microsoft security stack, they run KQL.

Do I need an Azure subscription to practice KQL?

Microsoft offers free KQL practice environments and the Advanced Hunting demo, and you can run realistic correlation in story-driven SIEM practice without standing up any infrastructure.

KQL or SPL — which should I learn first?

Learn whichever your target employers use. If you're not sure, knowing both makes you far more hireable — the detection-engineering mindset behind them is the same either way.

---

Final thought: SIEM languages come and go by vendor, but the thinking behind a good query — filter, shape, correlate, conclude — is the actual skill. KQL is just one dialect of it.

How EpicDetect Can Help

Reading query syntax only gets you so far. If you want to run this kind of correlation inside a real unfolding incident — not against clean sample data — Adventures drops you into a story-driven SOC investigation where SIEM queries are how you crack the case. Season 0 is completely free, no credit card required.

Want the fuller skill tree too? Head to the EpicDetect Atlas for structured lessons on SIEM and detection engineering.

New here? Sign up and start for free.