Introduction to Microsoft Sentinel KQL
Most organizations run “reactive” detection: an alert fires, you investigate, you remediate. Threat hunting changes the logic: you form hypotheses, explore data, and look for weak signals at scale, at a time when attackers automate everything.
Microsoft Sentinel is particularly well suited to this work, as long as you have a basic command of KQL (Kusto Query Language). The goal of this article isn’t to drown you in syntax: it’s to give you a method, key commands, and “reusable” queries to hunt effectively through logs.
What you’ll learn in this guide
- Understand the role of threat hunting in Sentinel
- Know where to look: tables, time ranges, and useful fields
- Master essential KQL commands (search, where, project, summarize, join, parse, mv-expand)
- Build hunting queries to spot advanced behaviors
- Analyze and interpret results to move into the “investigation” phase
Threat hunting vs detection vs investigation (in 30 seconds)
- Detection: rules/analytics based on known events (alerts).
- Investigation: you follow an alert and reconstruct an event chain.
- Threat hunting: you start from a hypothesis (e.g., “quiet lateral movement”) and explore data to find traces.
Prerequisites (so hunting is actually useful)
Before writing queries, make sure you have:
- Connectors enabled (Microsoft 365, Azure, Defender, Windows, etc.)
- Consistent retention (otherwise you hunt over 7 days and miss the signal)
- A naming and tagging convention (workspaces, tables, VIP accounts)
- A clear objective: “reduce time to detect,” “detect exfiltration,” etc.
SC-200 module to expand (what we’ll cover)
The module “Create queries for Microsoft Sentinel using KQL” aims to:
- Build KQL statements for Microsoft Sentinel
- Analyze query results
- Generate multi-table queries
- Use Sentinel data through KQL
1) Where to hunt: understanding tables and the “datastore”
In Sentinel, your data is stored in Log Analytics as tables. Each connector feeds one or more tables.
How to discover available tables
Start by exploring your data ecosystem:
- Global search:
search "keyword"
- Quick time filter:
| where TimeGenerated > ago(24h)
- Project only the fields you need:
| project TimeGenerated, Computer, Account, IPAddress, OperationName
2) Essential KQL commands (the hunter’s “kit”)
| where Account contains "admin"| where IPAddress startswith "10."
project (select columns)
| project TimeGenerated, Account, IPAddress, ActionType
extend (create a field)
| extend Hour = datetime_part("hour", TimeGenerated)
summarize (aggregate)
| summarize count() by Account| summarize dcount(IPAddress) by Account
order by and take
| order by TimeGenerated desc| take 50
join (correlate)
| join kind=inner (...) on DeviceId
parse / extract (extract)
- Useful for semi-structured fields.
mv-expand (expand arrays)
- Useful when a field contains a list (e.g., IPs, URLs).
3) “Hunting” query patterns (ready to copy)
The examples below are patterns. Adapt the tables based on your connectors (Defender, Azure AD/Entra, M365, etc.).
A) Detect abnormal authentication (impossible travel / unexpected country)
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == 0
| summarize Countries = make_set(LocationDetails.countryOrRegion) by UserPrincipalName
| where array_length(Countries) > 1
B) Count MFA / sign-in failures (noise vs signal)
SigninLogs
| where TimeGenerated > ago(24h)
| summarize Failures = countif(ResultType != 0), Success = countif(ResultType == 0) by UserPrincipalName
| where Failures > 10 and Success > 0
| order by Failures desc
C) Broad search (when you only have one clue)
search "rundll32"
| where TimeGenerated > ago(7d)
| take 200
D) Spot suspicious PowerShell executions (example)
DeviceProcessEvents
| where TimeGenerated > ago(24h)
| where FileName in~ ("powershell.exe", "pwsh.exe")
| where ProcessCommandLine has_any ("-enc", "IEX", "DownloadString", "FromBase64String")
| project TimeGenerated, DeviceName, AccountName, ProcessCommandLine
| order by TimeGenerated desc
E) Correlate a user + a device (join)
let suspiciousUsers = SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == 0
| summarize by UserPrincipalName;
DeviceLogonEvents
| where TimeGenerated > ago(24h)
| join kind=inner suspiciousUsers on $left.AccountUpn == $right.UserPrincipalName
| project TimeGenerated, DeviceName, AccountUpn, LogonType4) How to analyze results (without fooling yourself)
Avoid classic false positives:
- Service accounts and automation accounts
- VPN/proxy hops (same user, different IP)
- Internal scans (security tools)
Move from “list” to “story”:
A good hunting result should let you tell:
- Who? (account)
- What? (action)
- Where? (device, IP, country)
- When? (timeline)
- What next? (potential impact)
External links (useful references)
Your next steps
- Pick onr hunting hypothesis (e.g., encoded PowerShell execution).
- Run the query over 24 hours, then 7 days.
- Add one correlation (
join) to enrich context. - Turn your query into a reusable rule / workbook / hunting query.
Recommended certification & training paths (practical options)
FAQ
Do I need to be a developer to use KQL?
No. KQL is designed for log analysis. With just a few commands (where, project, summarize), you can already produce useful results.
What is the difference between a hunting query and an analysis rule?
A hunting query is exploratory (hypothesis, research). An analysis rule automatically generates alerts.
Which tables should I use first?
Start with the tables linked to your main connectors (SigninLogs, AuditLogs, DeviceProcessEvents, DeviceNetworkEvents, etc.).
How to reduce false positives?
Create exception lists (service accounts, VPN IPs), add context (join), and use thresholds (summarize) adapted to your environment.
