KQL Query Examples for Threat Detection in Microsoft Sentinel

Most KQL tutorials start with SecurityAlert | take 10. Fine for orientation, useless for building detections that survive production traffic. After running detection engineering across Sentinel tenants handling 50 to 200 GB per day of ingestion, the patterns that actually matter look different from what shows up in most blog posts. These are the queries we reach for first, annotated with why.

The Summarize-Then-Join Pattern

The most common mistake we see in customer rule libraries: joining on raw event tables before aggregating. In Log Analytics, joins are expensive when the left side is large. Summarize first, then join the aggregate.

Here is a sign-in anomaly detector that finds accounts logging in from more than three countries in a 24-hour window, enriched with the most recent user risk level:

let lookback = 24h;
let multi_geo_accounts =
    SigninLogs
    | where TimeGenerated > ago(lookback)
    | where ResultType == "0"                          // successful sign-ins only
    | summarize
        country_count = dcount(LocationDetails.countryOrRegion),
        countries = make_set(LocationDetails.countryOrRegion, 10),
        sign_in_count = count()
      by UserPrincipalName, bin(TimeGenerated, 1h)
    | where country_count >= 3;
multi_geo_accounts
| join kind=leftouter (
    AADUserRiskEvents
    | where TimeGenerated > ago(lookback)
    | summarize arg_max(TimeGenerated, RiskLevel, RiskEventType) by UserPrincipalName
) on UserPrincipalName
| project
    TimeGenerated,
    UserPrincipalName,
    country_count,
    countries,
    sign_in_count,
    RiskLevel,
    RiskEventType
| order by country_count desc

The arg_max on the risk events table pulls the most recent risk record per user without a second full scan. Small detail, meaningful cost difference at scale. Also note the explicit kind=leftouter: the default inner join silently drops users with no risk events, which makes the query look clean while discarding the accounts you most want to see.

mv-expand: The Operator Teams Underuse

Process execution logs from Defender for Endpoint pack parent process command lines into arrays. mv-expand unpacks them into rows so you can filter normally instead of parsing strings with contains and hoping.

This query hunts for PowerShell spawned by Office processes, a standard initial access indicator:

let office_procs = dynamic(["WINWORD.EXE", "EXCEL.EXE", "OUTLOOK.EXE", "POWERPNT.EXE"]);
DeviceProcessEvents
| where TimeGenerated > ago(7d)
| where FileName =~ "powershell.exe" or FileName =~ "pwsh.exe"
| extend InitiatingProcessFileName = toupper(InitiatingProcessFileName)
| where InitiatingProcessFileName in (office_procs)
| summarize
    cmd_list = make_set(ProcessCommandLine, 50),
    device_count = dcount(DeviceName),
    first_seen = min(TimeGenerated),
    last_seen = max(TimeGenerated)
  by InitiatingProcessFileName
| mv-expand cmd_list to typeof(string)
| where cmd_list !contains "-EncodedCommand"
| project
    InitiatingProcessFileName,
    device_count,
    first_seen,
    last_seen,
    SuspiciousCommand = cmd_list

The to typeof(string) cast on mv-expand saves a downstream tostring() call. Minor individually, but it compounds across result sets that run to tens of thousands of rows per day.

Time-Series Baselining for Anomaly Detection

Static thresholds age poorly. An account that normally generates 2,000 events per hour will produce constant false positives on a 500-event threshold. series_decompose_anomalies() handles this without requiring per-entity baselines maintained in watchlists.

let interval = 1h;
let lookback = 14d;
SigninLogs
| where TimeGenerated > ago(lookback)
| where ResultType != "0"                             // failed sign-ins
| make-series
    failed_count = count()
    on TimeGenerated
    from ago(lookback) to now()
    step interval
    by UserPrincipalName
| extend (anomalies, score, baseline) =
    series_decompose_anomalies(failed_count, 2.5)     // 2.5 sigma starting point
| mv-expand
    TimeGenerated to typeof(datetime),
    failed_count to typeof(long),
    anomalies to typeof(int),
    score to typeof(double),
    baseline to typeof(double)
| where anomalies == 1
| project
    TimeGenerated,
    UserPrincipalName,
    failed_count,
    baseline = round(baseline, 1),
    anomaly_score = round(score, 2)
| order by anomaly_score desc

The 2.5 sigma value is a starting point. In high-noise tenants we have pushed it to 3.5 before alert volume becomes workable. Tune per environment and revisit when you onboard a new identity provider or change MFA policy, both of which shift the baseline distribution.

Lateral Movement Detection via Pass-the-Hash

This surfaces on almost every red team debrief we have reviewed. Pass-the-hash presents as an NTLM network logon (LogonType 3) with no Kerberos package, originating from a machine that is not a domain controller:

let dc_names =
    DeviceInfo
    | where DeviceType == "DomainController"
    | summarize make_set(DeviceName);
SecurityEvent
| where TimeGenerated > ago(1d)
| where EventID == 4624
| where LogonType == 3
| where AuthenticationPackageName == "NTLM"
| where SubjectDomainName != "-"                      // reduce machine account noise
| extend SourceDevice = toupper(WorkstationName)
| where SourceDevice !in (dc_names)
| summarize
    target_count = dcount(Computer),
    targets = make_set(Computer, 20),
    event_count = count(),
    first_seen = min(TimeGenerated),
    last_seen = max(TimeGenerated)
  by SourceDevice, SubjectUserName
| where target_count >= 2
| order by target_count desc

One caveat: Windows 11 24H2 changed how some logon metadata fields populate in certain domain configurations. If WorkstationName comes back empty for your environment, fall back to IpAddress and cross-reference against a device inventory watchlist.

On performance: always put where TimeGenerated > ago(X) as the first filter in any query. The engine uses it for partition pruning. Moving it to third or fourth in the pipe, after a join, can multiply scan cost by 10x or more. We have seen customer-written rules that ran for 45 seconds be cut to 4 seconds just by reordering filters.

AI-to-KQL: Where It Fits and Where It Does Not

The Hacker News thread on AI-powered natural-language-to-KQL describes something we have tested against Copilot for Security and several internal tools. The honest assessment: useful for scaffolding, not for shipping.

For well-described scenarios ("show me failed MFA events grouped by user in the last hour"), generated queries are usually syntactically correct and cover roughly 80% of intent. The gaps cluster in predictable places: missing kind= on joins (the default inner join drops unmatched rows silently and the query still runs without error), wrong handling of multi-value dynamic fields, and threshold assumptions baked in without a comment explaining them.

The workflow that holds up: use AI to generate the structural skeleton, manually review join types and summarize key columns, then QA the query against a known-populated time window before wiring it to an alert rule. We have also seen AI-generated queries skip the project statement entirely, which returns 40-plus columns to the alert payload and makes incident triage slower than it needs to be.

This connects directly to how we think about detection engineering: the query is one artifact in a pipeline that includes rule lifecycle management, suppression logic, severity calibration, and SOAR integration. The query alone is not the detection.

When to Call Us

If you are standing up Sentinel from scratch, migrating off a legacy SIEM, or your current KQL rule library is a pile of copy-pasted queries with no lifecycle management or cost controls, reach out to us at /contact and we can scope a detection engineering engagement.