Kusto Query Language (KQL) is the analytical engine behind every Microsoft Sentinel detection, investigation, and hunt. Fluency in KQL is what separates analysts who chase alerts from those who surface threats before they escalate. This cheat sheet covers the patterns you will reach for every single day — organised by category, validated against real Sentinel table schemas, and ready to copy-paste directly into your workspace.

All queries use production field names. No synthetic examples — everything here has been tested in live Sentinel environments.

1. Core Operators & Filtering

Every KQL query is a pipeline. Data flows left-to-right through operators that filter, shape, and transform the result set. Placing where early is the single biggest performance lever you have.

// Basic time filter and field selection
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4625                     // Failed logon
| where AccountType == "User"
| project TimeGenerated, Account, Computer, IpAddress, LogonType
| order by TimeGenerated desc

// Multiple conditions
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType != 0                     // Failed sign-ins only
| where AppDisplayName != "Microsoft Authentication Broker"
| project TimeGenerated, UserPrincipalName, IPAddress, ResultDescription

// Case-insensitive membership test
AuditLogs
| where TimeGenerated > ago(30d)
| where OperationName in~ ("Add user", "Delete user", "Reset password")
| project TimeGenerated, OperationName, Result,
          Actor = tostring(InitiatedBy.user.userPrincipalName)

// Dynamic column creation with extend
SigninLogs
| where TimeGenerated > ago(1d)
| extend Country  = tostring(LocationDetails.countryOrRegion)
| extend City     = tostring(LocationDetails.city)
| extend IsGuest  = UserType == "Guest"
| project TimeGenerated, UserPrincipalName, IPAddress, Country, City, IsGuest

2. String & Pattern Matching

String operators vary significantly in speed. Choose the right one for each use case to avoid slow full-table scans.

// has — word-boundary match (fastest for keywords)
SecurityEvent
| where TimeGenerated > ago(1d)
| where CommandLine has "powershell" and CommandLine has "-EncodedCommand"
| project TimeGenerated, Account, Computer, CommandLine

// contains — substring anywhere in field (slower)
AuditLogs
| where TimeGenerated > ago(30d)
| where OperationName contains "role"
| project TimeGenerated, OperationName, Result

// startswith / endswith
SecurityEvent
| where TimeGenerated > ago(1d)
| where Process startswith "cmd"

// has_any — match any keyword from a list
CommonSecurityLog
| where TimeGenerated > ago(1d)
| where Activity has_any ("deny", "block", "drop", "reject")
| project TimeGenerated, SourceIP, DestinationIP, Activity, DeviceVendor

// matches regex — for complex patterns (use sparingly)
SigninLogs
| where TimeGenerated > ago(7d)
| where UserPrincipalName matches regex @"^(admin|svc|sa)[._@]"
| project TimeGenerated, UserPrincipalName, IPAddress, ResultType

// extract — pull structured value from unstructured string
AzureActivity
| where TimeGenerated > ago(7d)
| extend ResourceGroup = extract(@"/resourceGroups/([^/]+)/", 1, ResourceId)
| project TimeGenerated, Caller, OperationName, ResourceGroup, ResourceId

3. Aggregation & Summarisation

Aggregation is the foundation of anomaly detection. Instead of looking at individual events, you measure patterns — counts, distinct values, baselines — and surface deviations.

// Count failed logons per account
SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 4625
| summarize FailedCount = count() by Account, IpAddress
| where FailedCount > 10
| order by FailedCount desc

// Distinct count — unique source IPs per user
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| summarize UniqueIPs = dcount(IPAddress) by UserPrincipalName
| where UniqueIPs > 5
| order by UniqueIPs desc

// make_set — collect distinct values into an array
SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID in (4624, 4625, 4648)
| summarize EventTypes = make_set(EventID), TotalCount = count() by Account

// Percentile — detect data-exfiltration outliers
CommonSecurityLog
| where TimeGenerated > ago(1d)
| where DeviceVendor == "Palo Alto Networks"
| summarize BytesSent = sum(SentBytes) by SourceIP
| summarize
    p50 = percentile(BytesSent, 50),
    p95 = percentile(BytesSent, 95),
    p99 = percentile(BytesSent, 99)

// Time binning for trend charts
SecurityEvent
| where TimeGenerated > ago(7d)
| where EventID == 4625
| summarize FailCount = count() by bin(TimeGenerated, 1h)
| render timechart

4. Joins & Lookups

Correlation across tables is where KQL becomes genuinely powerful. Understanding join kinds prevents the common “result explosion” mistake where a many-to-many join multiplies rows unexpectedly.

// inner join — return only rows matched on both sides
let ThreatIPs =
    ThreatIntelligenceIndicator
    | where TimeGenerated > ago(7d)
    | where Active == true
    | where isnotempty(NetworkIP)
    | project NetworkIP;
CommonSecurityLog
| where TimeGenerated > ago(1d)
| join kind=inner (ThreatIPs) on $left.SourceIP == $right.NetworkIP
| project TimeGenerated, SourceIP, DestinationIP, Activity, DeviceVendor

// leftouter join — keep all left rows, enrich with right side
SigninLogs
| where TimeGenerated > ago(1d)
| join kind=leftouter (
    IdentityInfo
    | project AccountUPN, Department, JobTitle, Manager
) on $left.UserPrincipalName == $right.AccountUPN
| project TimeGenerated, UserPrincipalName, Department, JobTitle, IPAddress, ResultType

// leftanti join — anomaly detection: events NOT in an allowlist
let KnownAdmins = dynamic(["admin@corp.com","svc-sentinel@corp.com"]);
AuditLogs
| where TimeGenerated > ago(1d)
| where OperationName has "role" and Result == "success"
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| where Actor !in (KnownAdmins)
| project TimeGenerated, OperationName, Actor, TargetResources

5. Time Intelligence

// Common relative windows
| where TimeGenerated > ago(24h)
| where TimeGenerated > ago(7d)
| where TimeGenerated between (ago(48h) .. ago(24h))   // Yesterday only

// Calendar alignment
| where TimeGenerated >= startofday(ago(1d))            // Start of yesterday
| where TimeGenerated >= startofweek(ago(7d))           // Start of last week
| where TimeGenerated >= startofmonth(now())            // This month

// Date arithmetic
| extend AgeDays  = datetime_diff('day',  now(), TimeGenerated)
| extend AgeHours = datetime_diff('hour', TimeGenerated, CreatedTime)

// After-hours logon detection
SecurityEvent
| where TimeGenerated > ago(30d)
| where EventID == 4624
| extend HourOfDay  = hourofday(TimeGenerated)
| extend DayOfWeek  = dayofweek(TimeGenerated)
| where HourOfDay !between (8 .. 18)
   or DayOfWeek in (0d, 6d)                            // Weekend = 0 (Sun), 6 (Sat)
| summarize Count = count() by Account, HourOfDay
| order by Count desc

6. Watchlist Integration — Correct Patterns

Watchlists allow you to inject reference data (IP blocklists, asset inventories, user tiers) into queries. There are three correct patterns — each suited to a different use case.

Pattern A — Exact set membership

// Project the lookup key, then use 'in'
let BlockedIPs = (_GetWatchlist('BlockedIPs') | project SearchKey);
CommonSecurityLog
| where TimeGenerated > ago(24h)
| where SourceIP in (BlockedIPs)
| project TimeGenerated, SourceIP, DestinationIP, Activity, DeviceVendor

Pattern B — CIDR range matching

// CORRECT: build a scalar dynamic array with toscalar + make_list
let CorpRanges = toscalar(
    _GetWatchlist('CorporateRanges')
    | summarize make_list(SearchKey)
);
SigninLogs
| where TimeGenerated > ago(24h)
| extend IsInternal = ipv4_is_in_any_range(IPAddress, CorpRanges)
| where not(IsInternal)
| summarize ExternalSignins = count() by UserPrincipalName, IPAddress
| order by ExternalSignins desc

Pattern C — Enrichment join

// Enrich alerts with asset owner from watchlist
let AssetOwners = _GetWatchlist('AssetInventory')
    | project SearchKey, Owner = Column1, Criticality = Column2;
SecurityAlert
| where TimeGenerated > ago(24h)
| extend TargetHost = tostring(parse_json(Entities)[0].HostName)
| join kind=leftouter (AssetOwners) on $left.TargetHost == $right.SearchKey
| project TimeGenerated, AlertName, AlertSeverity, TargetHost, Owner, Criticality

7. Real-World SOC Queries

Brute Force Detection

let Threshold = 10;
let Window    = 1h;
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4625
| summarize
    FailCount  = count(),
    Computers  = make_set(Computer),
    FirstSeen  = min(TimeGenerated),
    LastSeen   = max(TimeGenerated)
  by Account, IpAddress, bin(TimeGenerated, Window)
| where FailCount >= Threshold
| project FirstSeen, LastSeen, Account, IpAddress, FailCount, Computers

Impossible Travel Detection

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| project TimeGenerated, UserPrincipalName, IPAddress,
          Lat = toreal(LocationDetails.geoCoordinates.latitude),
          Lon = toreal(LocationDetails.geoCoordinates.longitude)
| sort by UserPrincipalName asc, TimeGenerated asc
| extend PrevUser = prev(UserPrincipalName),
         PrevLat  = prev(Lat),
         PrevLon  = prev(Lon),
         PrevTime = prev(TimeGenerated)
| where UserPrincipalName == PrevUser and isnotnull(PrevTime)
| extend TimeDiffHours = datetime_diff('hour', TimeGenerated, PrevTime)
| where TimeDiffHours > 0 and TimeDiffHours < 4
| extend DistKm = geo_distance_2points(Lon, Lat, PrevLon, PrevLat) / 1000
| where DistKm > 500
| project TimeGenerated, UserPrincipalName, IPAddress, DistKm, TimeDiffHours

Privileged Role Assignment Alert

AuditLogs
| where TimeGenerated > ago(7d)
| where OperationName == "Add member to role"
| where Result == "success"
| extend Actor      = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend Target     = tostring(TargetResources[0].userPrincipalName)
| extend RoleName   = tostring(TargetResources[1].displayName)
| where RoleName has_any ("Global Admin","Privileged Role","Security Admin","Exchange Admin")
| project TimeGenerated, Actor, Target, RoleName