Microsoft Sentinel KQL Tutorial 2026: 20 Must-Know Queries for SOC Analysts

Rudra Verma, Senior Security Architect & Researcher · 2026-10-02

Kusto Query Language (KQL) is the single most leverage-heavy skill a Microsoft Sentinel analyst can own. Every detection rule, every hunt, every incident drill-down and every workbook tile is a KQL query underneath. Learn the language and you stop clicking through blades and start answering questions in seconds.

This tutorial is deliberately practical. We cover the six operators that carry 90% of a SOC shift, the tables you will actually query, and then 20 must-know queries spanning identity, endpoint, network and cloud — each paired with its Splunk SPL equivalent so the logic transfers no matter which SIEM you sit in front of. Every query here is read-only and safe to run in a lab or production workspace.

◈ Table of Contents

01 What KQL Is & Why It Matters 02 Prerequisites & Where to Run KQL 03 The Six Core Operators 04 Tables You'll Actually Query 05 Identity & Sign-In Queries (1–5) 06 Endpoint & Process Queries (6–10) 07 Network & Exfil Queries (11–14) 08 Cloud, Audit & Alert Queries (15–20) 09 Performance & Optimization 10 From Query to Detection Rule 11 Troubleshooting & Common Errors 12 MITRE ATT&CK Mapping 13 Sources & References

🧭

01 — What KQL Is & Why It Matters

Phase 1 / 13

Kusto Query Language is a read-only query language built for large-scale, time-series log data. It powers Microsoft Sentinel, Microsoft Defender XDR advanced hunting, Azure Monitor / Log Analytics, and Azure Data Explorer. If you can write KQL, the same syntax follows you across all four surfaces — a rare thing in the SIEM world.

The mental model is a pipeline. You start with a table, then push rows through a chain of operators separated by the pipe (|) character. Each operator takes a table in and returns a table out. Read a query top-to-bottom, left-to-right, and it narrates exactly what it does — no nested subqueries required to get started.

🔎

Triage

Pull the sign-in trail for one user, the process tree for one host, or every alert on one IP in a single query.

🎯

Threat Hunting

Ask questions no built-in rule covers — rare parent-child pairs, first-seen destinations, MFA anomalies.

🚨

Detection Rules

Any query that returns rows can become a scheduled or near-real-time analytics rule that raises incidents.

📊

Reporting

Summarize into workbooks and QuickChart-style visuals for shift handover and management metrics.

KQL is case-sensitive for table and column names but the =~ operator gives you case-insensitive string comparison. Use =~ for values like usernames and process names that show up in mixed case.

As of 2026 Microsoft Sentinel is generally available inside the unified Microsoft Defender portal, and the standalone Azure portal experience is scheduled to lose support after March 31, 2027. Your KQL does not change — only the URL you type it into. Plan your bookmarks and playbooks accordingly.

🧰

02 — Prerequisites & Where to Run KQL

Phase 2 / 13

You need a Log Analytics workspace with Microsoft Sentinel enabled and at least one data connector sending logs. If you are following along in a lab and have no data yet, the easiest path is to connect the free Azure Activity log and Microsoft Entra ID sign-in logs — both fill up quickly. The table below is the pre-flight checklist.

Requirement	Detail	Notes
Log Analytics workspace	Sentinel enabled	Free tier / trial is fine for learning min
Role — read queries	Log Analytics Reader or Microsoft Sentinel Reader	Enough for everything in this guide
Role — save rules	Microsoft Sentinel Contributor	Needed only for Phase 10
Defender portal access	Security Reader (Entra role)	For advanced hunting over XDR tables
Sample data	Entra sign-in + Azure Activity connectors	Free, high-volume, great for practice
Browser	Any modern browser	KQL runs server-side; nothing to install

There are three practical places to run the exact same KQL. Pick the one that matches your task — the query text is identical.

Method 1 — Azure Portal Logs Blade

Interactive

The classic ad-hoc surface. Best for iterating on a query, checking a schema, or building a workbook tile. Portal path: portal.azure.com → Microsoft Sentinel → [workspace] → Logs. Set the time picker in the top bar — it silently overrides any ago() filter that is broader than the picker.

KUSTO — smoke test

// Confirms the workspace is alive and shows which tables have data in 24h union withsource=TableName * | where TimeGenerated > ago(24h) | summarize Rows=count() by TableName | sort by Rows desc

The union * smoke test scans every table — run it only over a short time window (24h or less) and never save it as a rule. It is a one-off orientation query.

Method 2 — Defender Portal Advanced Hunting

Unified

The 2026 default. Once your workspace is onboarded to the Defender portal, advanced hunting exposes your Sentinel Log Analytics tables and the Defender XDR tables (DeviceProcessEvents, EmailEvents, IdentityInfo) on one query surface — so you can join across both in a single query. Portal path: security.microsoft.com → Hunting → Advanced hunting.

KUSTO — cross-domain join only possible here

// Enrich an endpoint alert with the signed-in user's Entra risk level DeviceProcessEvents | where TimeGenerated > ago(1d) | where FileName =~ "powershell.exe" | join kind=leftouter (IdentityInfo | project AccountUpn, AccountName) on $left.AccountName == $right.AccountName | project TimeGenerated, DeviceName, AccountName, AccountUpn, ProcessCommandLine

Advanced hunting caps interactive queries at 30 minutes of compute and returns up to 30,000 rows. For heavier retrospective work, target the Sentinel data lake tier, which lets KQL run over long-retention data without the hot-tier cost.

Method 3 — Log Analytics REST API / CLI

Automation

Run the same KQL from a script, a pipeline, or a SOAR playbook. Useful for scheduled exports, enrichment lookups from Logic Apps, or feeding a query result into a ticketing system. Authenticate with a service principal that holds Log Analytics Reader.

BASH — Azure CLI

$ az monitor log-analytics query \ --workspace "<workspace-guid>" \ --analytics-query "SigninLogs | where TimeGenerated > ago(1h) | summarize count() by ResultType" \ -o table

BASH — raw REST call

$ curl -s -X POST \ "https://api.loganalytics.io/v1/workspaces/<guid>/query" \ -H "Authorization: Bearer $TOKEN" \ -H "Content-Type: application/json" \ -d '{"query":"AzureActivity | take 10"}'

⚙️

03 — The Six Core Operators

Phase 3 / 13

Master these six and you can express almost any question a shift throws at you. Everything else is a refinement. The diagram shows how rows flow through a typical chain.

A KQL query is a left-to-right pipeline; each operator returns a table for the next.

Before the operators, the string-matching keywords you'll reach for constantly. Choosing the right one is the difference between a query that returns in a second and one that scans the whole table.

Operator	Matches	Case	Speed / use when
==	Exact whole value	Sensitive	Fast — IDs, event codes, exact strings
=~	Exact whole value	Insensitive	Fast — usernames, filenames, hostnames
has	A whole indexed term	Insensitive	Fastest substring-like — whole words/terms
has_any	Any term in a list	Insensitive	Fast — multiple keywords at once
contains	Any substring	Insensitive	Slow — partial matches, punctuation
in / in~	Value in a set	Sensitive / insensitive	Fast — allow-lists, block-lists
startswith / endswith	Prefix / suffix	Insensitive	Medium — file extensions, path anchors
matches regex	Regular expression	Sensitive	Slowest — only when nothing else fits

where — filter rows

Filter

The workhorse. Always filter on TimeGenerated first so the engine prunes data early. String matching offers has (indexed, fast, term-based), contains (substring, slower), == (exact, case-sensitive) and =~ (exact, case-insensitive).

SecurityEvent | where TimeGenerated > ago(1d) // time filter first — always | where EventID == 4625 // failed logon | where Account !endswith "$" // drop machine accounts

Prefer has over contains whenever you are matching a whole term (a hostname, a filename). has uses the term index and is dramatically faster at scale; contains forces a full substring scan.

project — choose & rename columns

Shape

project keeps only the columns you name (and can rename them inline); project-away drops the ones you don't want; project-rename renames without reordering. Trimming columns early makes results readable and queries cheaper.

SigninLogs | project TimeGenerated, User=UserPrincipalName, IP=IPAddress, ResultType, App=AppDisplayName

summarize — aggregate

Aggregate

Collapses rows into groups. The high-value aggregation functions: count(), dcount() (distinct count), countif() (conditional count), make_set() / make_list() (collect values), min()/max(), and arg_max() (the full row with the largest value of a column).

SigninLogs | summarize Total=count(), Failed=countif(ResultType != 0), Apps=make_set(AppDisplayName) by UserPrincipalName

extend — compute new columns

Compute

Adds a calculated column without dropping the originals. Use it to parse dynamic fields, compute a ratio, or derive a category before you filter or summarize on it.

SigninLogs | extend Country = tostring(LocationDetails.countryOrRegion) | extend IsRisky = iff(RiskLevelDuringSignIn == "high", "YES", "no")

bin() — bucket time (with summarize)

Time series

bin() rounds a timestamp down to a fixed interval so summarize can build a time series. Pair it with render timechart in the Logs blade for an instant trend line — invaluable for spotting beaconing or a burst of failures.

SigninLogs | where ResultType != 0 | summarize Failures=count() by bin(TimeGenerated, 15m) | render timechart

join / union — combine tables

Correlate

join correlates two tables on a shared key (kinds: inner, leftouter, rightouter, leftanti, rightanti). leftanti is a hunter's favourite — it returns rows in the left table with no match on the right, perfect for "seen today but never before" logic. union stacks tables with similar schemas.

// Hosts that beaconed today to a destination never seen in the prior 14 days let baseline = DeviceNetworkEvents | where TimeGenerated between (ago(14d) .. ago(1d)) | distinct RemoteUrl; DeviceNetworkEvents | where TimeGenerated > ago(1d) and isnotempty(RemoteUrl) | join kind=leftanti baseline on RemoteUrl | summarize Hits=count() by DeviceName, RemoteUrl

In a join, put the smaller table on the left. KQL loads the left side into memory; a giant left table is the most common cause of "query used too much memory" failures.

🗄️

04 — Tables You'll Actually Query

Phase 4 / 13

A workspace can hold hundreds of tables, but a working analyst lives in a dozen. Know these and their key columns and you rarely need to look anything up. Column availability depends on the connectors you've enabled.

Table	What's in it	Key columns
SigninLogs	Entra ID interactive sign-ins	UserPrincipalName, IPAddress, ResultType, LocationDetails, AppDisplayName
AADNonInteractiveUserSignInLogs	Token / service sign-ins	UserPrincipalName, ResultType, ClientAppUsed
AuditLogs	Entra directory changes	OperationName, InitiatedBy, TargetResources
SecurityEvent	Windows Security event log (via agent)	EventID, Account, Computer, LogonType
DeviceProcessEvents	Defender for Endpoint process creation	FileName, ProcessCommandLine, InitiatingProcessFileName
DeviceNetworkEvents	Endpoint outbound connections	RemoteIP, RemoteUrl, RemotePort, ActionType
DeviceLogonEvents	Endpoint logon activity	AccountName, LogonType, RemoteIP, ActionType
AzureActivity	Azure control-plane operations	OperationNameValue, Caller, CallerIpAddress, ActivityStatusValue
CommonSecurityLog	CEF — firewalls, proxies	SourceIP, DestinationIP, SentBytes, ReceivedBytes, DeviceVendor
SecurityAlert	Alerts from all connected products	AlertName, AlertSeverity, Entities
ThreatIntelligenceIndicator	Ingested TI IOCs	NetworkIP, DomainName, FileHashValue, Active

Not sure a column exists in your tenant? Run TableName | getschema to list every column and its type, or TableName | take 5 to eyeball real rows before you build the full query.

🔐

05 — Identity & Sign-In Queries (1–5)

Phase 5 / 13

Identity is the number-one attacked surface. These five queries cover the sign-in patterns that show up in nearly every intrusion: brute force, spray success, geo-anomalies, MFA fatigue and legacy-auth abuse.

Failed sign-ins by user & IP (brute force)

T1110

DETECTS: A single account or source IP racking up many authentication failures in a short window — the signature of password brute force.

KUSTO

SigninLogs | where TimeGenerated > ago(24h) | where ResultType != 0 | summarize Failures=count(), Reasons=make_set(ResultDescription), FirstSeen=min(TimeGenerated), LastSeen=max(TimeGenerated) by UserPrincipalName, IPAddress | where Failures > 10 | sort by Failures desc

SPLUNK SPL

index=azure sourcetype="azure:aad:signin" "properties.status.errorCode"!=0 | stats count as Failures values("properties.status.failureReason") as Reasons by "properties.userPrincipalName" "properties.ipAddress" | where Failures > 10 | sort - Failures

Successful login after repeated failures (spray hit)

T1110.003

DETECTS: An account that failed many times and then succeeded from the same IP — a likely successful password spray or brute force. This is the query that turns noise into an incident.

KUSTO

SigninLogs | where TimeGenerated > ago(24h) | summarize Failed=countif(ResultType != 0), Success=countif(ResultType == 0), SuccessTime=maxif(TimeGenerated, ResultType == 0) by UserPrincipalName, IPAddress | where Failed >= 10 and Success > 0 | sort by Failed desc

SPLUNK SPL

index=azure sourcetype="azure:aad:signin" | eval failed=if('properties.status.errorCode'!=0,1,0), ok=if('properties.status.errorCode'==0,1,0) | stats sum(failed) as Failed sum(ok) as Success by "properties.userPrincipalName" "properties.ipAddress" | where Failed>=10 AND Success>0

Sign-ins from multiple countries (geo-anomaly)

T1078

DETECTS: One account authenticating successfully from more than one country in a day — a lightweight "impossible travel" proxy that needs no ML.

KUSTO

SigninLogs | where TimeGenerated > ago(24h) and ResultType == 0 | extend Country = tostring(LocationDetails.countryOrRegion) | summarize Countries=make_set(Country), Cities=make_set(tostring(LocationDetails.city)), IPs=make_set(IPAddress), Logins=count() by UserPrincipalName | where array_length(Countries) > 1 | sort by Logins desc

SPLUNK SPL

index=azure sourcetype="azure:aad:signin" "properties.status.errorCode"=0 | stats dc("properties.location.countryOrRegion") as nCountries values("properties.location.countryOrRegion") as Countries by "properties.userPrincipalName" | where nCountries > 1

MFA fatigue / prompt bombing

T1621

DETECTS: A rapid burst of MFA challenges to one user — the fingerprint of push-notification "fatigue" attacks where an operator holds a valid password and spams approvals.

KUSTO

SigninLogs | where TimeGenerated > ago(6h) | where ResultType in ("50074", "500121", "50076") // MFA required / failed / satisfied-by-claim denied | summarize Prompts=count(), Results=make_set(ResultType) by UserPrincipalName, bin(TimeGenerated, 10m) | where Prompts > 5 | sort by Prompts desc

SPLUNK SPL

index=azure sourcetype="azure:aad:signin" ("properties.status.errorCode"=50074 OR "properties.status.errorCode"=500121 OR "properties.status.errorCode"=50076) | bucket _time span=10m | stats count as Prompts by "properties.userPrincipalName" _time | where Prompts > 5

Legacy authentication usage

T1078.004

DETECTS: Sign-ins over legacy protocols (IMAP, POP3, SMTP, ActiveSync) that bypass modern MFA — a favourite path for spray attacks against Microsoft 365.

KUSTO

union SigninLogs, AADNonInteractiveUserSignInLogs | where TimeGenerated > ago(7d) | where ClientAppUsed in ("IMAP4", "POP3", "SMTP", "Other clients", "Exchange ActiveSync") | summarize Attempts=count(), Success=countif(ResultType == 0) by UserPrincipalName, ClientAppUsed, IPAddress | sort by Attempts desc

SPLUNK SPL

index=azure sourcetype="azure:aad:signin" "properties.clientAppUsed" IN ("IMAP4","POP3","SMTP","Other clients","Exchange ActiveSync") | stats count as Attempts by "properties.userPrincipalName" "properties.clientAppUsed" "properties.ipAddress"

Legacy auth should be near-zero in a hardened 2026 tenant. If this query returns rows, treat it as both an incident lead and a hardening backlog item — Conditional Access can block legacy auth outright.

💻

06 — Endpoint & Process Queries (6–10)

Phase 6 / 13

Once an attacker lands on a host, they run commands. These queries target the execution patterns — encoded PowerShell, living-off-the-land binaries, suspicious parent-child chains, new services and log clearing — that separate an operator from a user.

Encoded / obfuscated PowerShell

T1059.001

DETECTS: PowerShell launched with base64-encoded commands or hidden windows — classic loader and fileless-malware behaviour.

KUSTO

DeviceProcessEvents | where TimeGenerated > ago(7d) | where FileName in~ ("powershell.exe", "pwsh.exe") | where ProcessCommandLine has_any ("-enc", "-EncodedCommand", "FromBase64String", "-w hidden", "-nop", "IEX") | project TimeGenerated, DeviceName, AccountName, ProcessCommandLine, InitiatingProcessFileName | sort by TimeGenerated desc

SPLUNK SPL (Sysmon)

index=win sourcetype="XmlWinEventLog:Microsoft-Windows-Sysmon/Operational" EventCode=1 (Image="*\\powershell.exe" OR Image="*\\pwsh.exe") | search CommandLine="*-enc*" OR CommandLine="*EncodedCommand*" OR CommandLine="*FromBase64String*" | table _time Computer User CommandLine ParentImage

Living-off-the-land binaries (LOLBins)

T1218

DETECTS: Trusted system binaries used for download or proxy execution — certutil pulling a file, mshta running remote script, regsvr32 with scrobj.

KUSTO

DeviceProcessEvents | where TimeGenerated > ago(7d) | where FileName in~ ("certutil.exe", "bitsadmin.exe", "mshta.exe", "regsvr32.exe", "rundll32.exe") | where ProcessCommandLine has_any ("http", "-urlcache", "javascript:", "scrobj", "/i:") | project TimeGenerated, DeviceName, AccountName, FileName, ProcessCommandLine

SPLUNK SPL (Sysmon)

index=win sourcetype="XmlWinEventLog:Microsoft-Windows-Sysmon/Operational" EventCode=1 (Image="*certutil.exe" OR Image="*mshta.exe" OR Image="*regsvr32.exe" OR Image="*bitsadmin.exe") | search CommandLine="*http*" OR CommandLine="*scrobj*" OR CommandLine="*-urlcache*" | table _time Computer User Image CommandLine

Office app spawning a shell

T1566

DETECTS: Word, Excel, PowerPoint or Outlook launching a command interpreter — the hallmark of a malicious macro or exploit payload firing after a phishing open.

KUSTO

DeviceProcessEvents | where TimeGenerated > ago(7d) | where InitiatingProcessFileName in~ ("winword.exe", "excel.exe", "powerpnt.exe", "outlook.exe") | where FileName in~ ("cmd.exe", "powershell.exe", "wscript.exe", "cscript.exe", "mshta.exe") | project TimeGenerated, DeviceName, InitiatingProcessFileName, FileName, ProcessCommandLine

SPLUNK SPL (Sysmon)

index=win sourcetype="XmlWinEventLog:Microsoft-Windows-Sysmon/Operational" EventCode=1 (ParentImage="*winword.exe" OR ParentImage="*excel.exe" OR ParentImage="*outlook.exe") (Image="*cmd.exe" OR Image="*powershell.exe" OR Image="*wscript.exe" OR Image="*mshta.exe") | table _time Computer ParentImage Image CommandLine

New service installed

T1543.003

DETECTS: Windows Security event 4697 (a service was installed) — a common persistence and lateral-movement technique, especially services whose binary path points at a temp or user directory.

KUSTO

SecurityEvent | where TimeGenerated > ago(7d) | where EventID == 4697 | extend Suspicious = ServiceFileName has_any ("\\Temp\\", "\\Users\\", "powershell", "cmd /c", "\\ProgramData\\") | project TimeGenerated, Computer, SubjectUserName, ServiceName, ServiceFileName, Suspicious | sort by Suspicious desc, TimeGenerated desc

SPLUNK SPL

index=win sourcetype="WinEventLog:Security" EventCode=4697 | eval Suspicious=if(match(Service_File_Name,"(?i)\\\\Temp\\\\|\\\\Users\\\\|powershell"),1,0) | table _time ComputerName Account_Name Service_Name Service_File_Name Suspicious

Security log cleared

T1070.001

DETECTS: Windows event 1102 (audit log cleared) — a high-fidelity anti-forensics signal that almost always means someone is covering tracks.

KUSTO

SecurityEvent | where TimeGenerated > ago(30d) | where EventID == 1102 | project TimeGenerated, Computer, ClearedBy=SubjectUserName, SubjectDomainName | sort by TimeGenerated desc

SPLUNK SPL

index=win sourcetype="WinEventLog:Security" EventCode=1102 | table _time ComputerName Account_Name

Event 1102 has an extremely low false-positive rate. Any hit outside a documented maintenance window deserves an immediate look at the host — attackers clear logs after they've done the damage, not before.

🌐

07 — Network & Exfil Queries (11–14)

Phase 7 / 13

Command-and-control and data theft both leave network fingerprints: regular beacons, first-seen destinations, oversized uploads and oddly long domains. These four queries surface them.

Beaconing — many hits to one destination

T1071

DETECTS: A host making an unusually high, steady number of connections to a single external destination — the coarse but effective first pass for C2 beacon hunting.

KUSTO

DeviceNetworkEvents | where TimeGenerated > ago(1d) | where RemoteIPType == "Public" | summarize Conns=count(), Ports=make_set(RemotePort), Buckets=dcount(bin(TimeGenerated, 5m)) by DeviceName, RemoteIP, RemoteUrl | where Conns > 100 and Buckets > 20 // spread across time = steady, not a burst | sort by Conns desc

SPLUNK SPL

index=network sourcetype="pan:traffic" | bucket _time span=5m | stats count as Conns dc(_time) as Buckets by src_ip dest_ip | where Conns > 100 AND Buckets > 20

Large outbound transfer (exfil)

T1048

DETECTS: Source hosts pushing an abnormal volume of bytes to a single external destination — a candidate for data exfiltration. Uses firewall/proxy CEF data where byte counts live.

KUSTO

SPLUNK SPL

index=network sourcetype="pan:traffic" | stats sum(bytes_out) as BytesOut by src_ip dest_ip | eval TotalMB=round(BytesOut/1024/1024,1) | where TotalMB > 500 | sort - TotalMB

First-seen external destination

T1071

DETECTS: Destinations contacted today that were never seen in the prior two weeks — new infrastructure is where fresh C2 hides. Uses a leftanti join against a baseline.

KUSTO

let baseline = DeviceNetworkEvents | where TimeGenerated between (ago(15d) .. ago(1d)) | distinct RemoteUrl; DeviceNetworkEvents | where TimeGenerated > ago(1d) and isnotempty(RemoteUrl) | join kind=leftanti baseline on RemoteUrl | summarize Hits=count(), Hosts=dcount(DeviceName) by RemoteUrl | sort by Hits desc

SPLUNK SPL

``` requires a lookup of previously-seen domains, e.g. seen_domains.csv ``` index=endpoint sourcetype="device:network" earliest=-1d | search NOT [ | inputlookup seen_domains.csv | fields RemoteUrl ] | stats count as Hits dc(DeviceName) as Hosts by RemoteUrl

Suspiciously long domains (DGA)

T1568.002

DETECTS: Very long hostnames with high digit content — a cheap heuristic for domain-generation-algorithm C2 that avoids hard-coded blocklists.

KUSTO

DeviceNetworkEvents | where TimeGenerated > ago(1d) and isnotempty(RemoteUrl) | extend Host = tostring(parse_url(strcat("http://", RemoteUrl)).Host) | extend Len = strlen(Host), Digits = countof(Host, @"\d", "regex") | where Len > 30 and Digits >= 6 | summarize Hits=count() by Host, DeviceName | sort by Hits desc

SPLUNK SPL

index=endpoint sourcetype="device:network" | eval Len=len(RemoteUrl), Digits=len(replace(RemoteUrl,"[^0-9]","")) | where Len>30 AND Digits>=6 | stats count as Hits by RemoteUrl DeviceName

Domain length alone is noisy — CDNs and analytics beacons are long too. Chain this into query 13 (first-seen) or a TI join (query 19) to cut the false positives to something a shift can action.

☁️

08 — Cloud, Audit & Alert Queries (15–20)

Phase 8 / 13

The control plane is where an attacker turns a foothold into ownership. These queries watch Azure role changes, subscription takeover, secret access, and tie the whole shift together with alert summaries and threat-intel matching.

Azure role assignment changes

T1098

DETECTS: New RBAC role assignments in Azure — privilege grants are how a compromised account becomes durable. Watch for Owner/Contributor grants especially.

KUSTO

AzureActivity | where TimeGenerated > ago(7d) | where OperationNameValue =~ "Microsoft.Authorization/roleAssignments/write" | where ActivityStatusValue == "Success" | project TimeGenerated, Caller, CallerIpAddress, ResourceGroup, _ResourceId | sort by TimeGenerated desc

SPLUNK SPL

index=azure sourcetype="azure:activity" operationName="Microsoft.Authorization/roleAssignments/write" status=Succeeded | table _time caller callerIpAddress resourceGroup resourceId

Subscription elevate-access (tenant takeover)

T1078.004

DETECTS: Use of the "elevate access" operation, which grants a Global Admin User Access Administrator rights over all subscriptions — a rare, high-impact action that should always be reviewed.

KUSTO

AzureActivity | where TimeGenerated > ago(30d) | where OperationNameValue has "elevateAccess" | project TimeGenerated, Caller, CallerIpAddress, ActivityStatusValue

SPLUNK SPL

index=azure sourcetype="azure:activity" operationName="*elevateAccess*" | table _time caller callerIpAddress status

elevateAccess should fire maybe once a year in a healthy tenant, during a documented break-glass event. An unexplained hit is a candidate tenant-takeover — escalate immediately (see Phase 10 / escalation).

Key Vault secret access spikes

T1552.001

DETECTS: A caller pulling an unusual number of secrets or keys from Azure Key Vault — a sign of credential harvesting after a compromise.

KUSTO

AzureDiagnostics | where TimeGenerated > ago(1d) | where ResourceType == "VAULTS" | where OperationName in ("SecretGet", "KeyGet", "CertificateGet") | summarize Reads=count(), Secrets=dcount(id_s) by CallerIPAddress, identity_claim_upn_s = tostring(column_ifexists("identity_claim_upn_s", "")) | where Reads > 50 | sort by Reads desc

SPLUNK SPL

index=azure sourcetype="azure:keyvault" (operationName=SecretGet OR operationName=KeyGet OR operationName=CertificateGet) | stats count as Reads dc(id) as Secrets by callerIpAddress identity.claim.upn | where Reads > 50

Alert summary for shift triage

Triage

DETECTS: Nothing new — this is your shift-start dashboard in one query: every alert grouped by name and severity so you know where the fire is.

KUSTO

SecurityAlert | where TimeGenerated > ago(24h) | summarize Count=count(), Products=make_set(ProductName) by AlertName, AlertSeverity | sort by AlertSeverity asc, Count desc

SPLUNK SPL

index=notable earliest=-24h | stats count as Count values(source) as Products by rule_name urgency | sort urgency - Count

Threat-intel IOC match on network traffic

T1071

DETECTS: Any outbound connection to an IP flagged as malicious in your ingested threat-intel feed — a direct join of live telemetry against known-bad indicators.

KUSTO

let badIPs = ThreatIntelligenceIndicator | where Active == true and isnotempty(NetworkIP) | summarize by NetworkIP; DeviceNetworkEvents | where TimeGenerated > ago(1d) | where RemoteIP in (badIPs) | project TimeGenerated, DeviceName, RemoteIP, RemoteUrl, InitiatingProcessFileName

SPLUNK SPL

index=endpoint sourcetype="device:network" | lookup threat_intel_ip ip as RemoteIP OUTPUT threat_source | where isnotnull(threat_source) | table _time DeviceName RemoteIP RemoteUrl threat_source

Watchlists are the manual cousin of TI feeds. Load a CSV of known-bad indicators and reference it with _GetWatchlist("MyBadIPs") — handy for incident-specific IOCs you don't want to push into the whole TI pipeline.

Per-user risk roll-up

Triage

DETECTS: A ranked, one-row-per-user summary combining login success/failure counts and geo spread — the fastest way to spot the two or three accounts worth investigating right now.

KUSTO

SigninLogs | where TimeGenerated > ago(7d) | summarize Success=countif(ResultType == 0), Failed=countif(ResultType != 0), Countries=dcount(tostring(LocationDetails.countryOrRegion)), IPs=dcount(IPAddress) by UserPrincipalName | extend FailRatio = round(1.0 * Failed / (Success + Failed + 1), 2) | where Failed > 20 or Countries > 2 | sort by FailRatio desc, Failed desc

SPLUNK SPL

index=azure sourcetype="azure:aad:signin" earliest=-7d | eval ok=if('properties.status.errorCode'==0,1,0), fail=if('properties.status.errorCode'!=0,1,0) | stats sum(ok) as Success sum(fail) as Failed dc("properties.location.countryOrRegion") as Countries dc("properties.ipAddress") as IPs by "properties.userPrincipalName" | eval FailRatio=round(Failed/(Success+Failed+1),2) | where Failed>20 OR Countries>2

🚀

09 — Performance & Optimization

Phase 9 / 13

A correct query that times out is useless during an incident. These habits keep queries fast at enterprise log volumes, where a single table can hold billions of rows.

✓

The optimization checklist

Best practice

Order of operations that keeps queries cheap

1Filter time first, filter narrow second. Put where TimeGenerated > ago() at the very top so the engine skips irrelevant partitions before doing anything else.
2Filter before you summarize or join. Every row you drop early is a row the expensive operators never touch.
3Use has, not contains. Term-indexed search is orders of magnitude faster than substring scanning.
4Project only the columns you need before a join — narrow tables move less data.
5Small table on the left of a join. The left side is materialized in memory.
6Avoid search * and union * in anything you run repeatedly — they scan everything.

Drop | take 100 at the end while you iterate. You get near-instant feedback on shape and correctness, then remove it for the full run. Pair with the Logs blade's built-in query performance pane to see scanned data volume.

🛡️

10 — From Query to Detection Rule

Phase 10 / 13

Any query that returns a row when something bad happens can become an automated detection. In Sentinel these are analytics rules. Portal path: Microsoft Sentinel → Analytics → Create → Scheduled query rule.

Turn query 02 into a scheduled rule

Analytics

Take the "spray hit" query, add entity mapping so the incident is investigable, and schedule it. Entity mapping is what lets Sentinel build an incident graph and correlate across alerts.

KUSTO — rule logic

SigninLogs | where TimeGenerated > ago(1h) | summarize Failed=countif(ResultType != 0), Success=countif(ResultType == 0) by UserPrincipalName, IPAddress | where Failed >= 10 and Success > 0

Rule settings that matter

1Run frequency & lookback: every 1h over the last 1h — keep them aligned to avoid gaps or double-counting.
2Entity mapping: map UserPrincipalName → Account and IPAddress → IP.
3Event grouping: group all rows into a single incident to avoid alert storms.
4Suppression: stop re-alerting on the same entity for a cooldown window after it fires.

For time-critical detections like elevateAccess (query 16), use a Near-Real-Time (NRT) rule instead of scheduled — NRT runs roughly once a minute and skips the scheduling delay.

Tune before you deploy. Run any candidate rule as a plain query across 7–14 days first and count the hits. If it would have fired 300 times, it is a dashboard tile, not an alert — add thresholds or allow-lists until the volume is actionable.

🧯

11 — Troubleshooting & Common Errors

Phase 11 / 13

The errors below account for the overwhelming majority of "my query won't run" tickets. Learn the fix once and stop losing minutes during an incident.

Symptom	Cause	Fix
SemanticError: 'X' could not be resolved	Table or column doesn't exist in this workspace	The connector isn't enabled, or you mistyped. Run `TableName \| getschema`
Empty results, but data exists	Time picker overrides `ago()`; case-sensitive `==`	Widen the time picker; switch `==` to `=~` for values
Query exceeded memory / too complex	Huge table on the left of a join	Swap join sides; `project` before joining; add filters
Results truncated / partial	Hit the 500,000-row / 64 MB result limit	Aggregate with `summarize`; add `set truncationmaxrecords` only if essential
Query timed out	Scanning too much data	Narrow the time range; filter earlier; use `has` over `contains`
Dynamic field returns empty	Accessing a JSON field as a string	Wrap with `tostring(Column.subfield)` before comparing
"has" returns nothing on an IP/GUID	`has` is term-based; punctuation breaks terms	Use `==`/`=~` for exact IDs, or `contains` for partials

KQL let statements must each end with a semicolon, and the final query after the last let must not. A stray or missing semicolon is the most common silent syntax error in multi-part queries.

🎯

12 — MITRE ATT&CK Mapping

Phase 12 / 13

Mapping each query to a technique makes coverage gaps visible and gives your detections a shared language with threat intel and IR.

Query	Technique	ID	Tactic
01 Failed sign-ins	Brute Force	T1110	Credential Access
02 Spray hit	Password Spraying	T1110.003	Credential Access
03 Geo-anomaly	Valid Accounts	T1078	Initial Access / Persistence
04 MFA fatigue	MFA Request Generation	T1621	Credential Access
05 Legacy auth	Valid Accounts: Cloud	T1078.004	Defense Evasion
06 Encoded PowerShell	PowerShell	T1059.001	Execution
07 LOLBins	System Binary Proxy Execution	T1218	Defense Evasion
08 Office spawns shell	Phishing	T1566	Initial Access
09 New service	Windows Service	T1543.003	Persistence
10 Log cleared	Clear Windows Event Logs	T1070.001	Defense Evasion
11 Beaconing	Application Layer Protocol	T1071	Command & Control
12 Large upload	Exfiltration Over Alt Protocol	T1048	Exfiltration
14 Long domains	Domain Generation Algorithms	T1568.002	Command & Control
15 Role assignment	Account Manipulation	T1098	Persistence
17 Key Vault reads	Credentials from Password Stores	T1552.001	Credential Access

You can now

Read and write KQL as a top-to-bottom pipeline
Run identical queries in the Logs blade, advanced hunting and the API
Triage identity, endpoint, network and cloud with 20 field-ready queries
Translate the same logic into Splunk SPL for a mixed-SIEM shop
Promote any query into a tuned, entity-mapped analytics rule

📚

13 — Sources & References

Phase 13 / 13

Microsoft Learn — Common tasks with KQL for Microsoft Sentinel Microsoft Learn — Advanced hunting with Microsoft Sentinel data in Microsoft Defender Microsoft Learn — Data tables in the Microsoft Defender XDR advanced hunting schema Microsoft Learn — Hunting capabilities in Microsoft Sentinel Microsoft Learn — Kusto Query Language reference MITRE ATT&CK — Enterprise techniques

Turn these queries into a working detection stack.

Save the 20 queries as functions, wire the high-fidelity ones into analytics rules, and pair them with the matching CyberHawk SOPs for triage and response. Explore our SOP library, hands-on courses, and the IOC Scanner to enrich query 19's threat-intel matching.

◈ Stay Connected

Follow CyberHawk Threat Intel for threat intelligence, deployment guides and hands-on SOC tooling content.

🌐 Website ▶️ YouTube ▶️ YouTube (2) 𝕏 Twitter / X ♪ TikTok ✈️ Telegram

🔍 IOC Scanner 🛠️ Live Tools 📚 Courses 🚨 Threat Intel 📝 Blog 📋 SOPs

"They can't exploit you if you are the Exploit."