Skip to content
StratQuad

KQL for Microsoft Defender and Sentinel

3 min read

Kusto Query Language syntax and ready to adapt hunting queries for Microsoft Defender XDR advanced hunting and Microsoft Sentinel.

At a glance

Owner
StratQuad
Version
November 2026
Applies to
Microsoft Defender XDR advanced hunting, Microsoft Sentinel, Log Analytics
Mandatory
Reference

Key points

  • A query starts with a table and pipes rows through operators: Table | where ... | project ... | summarize ....
  • Filter on time and the most selective column first; it is the biggest factor in query speed.
  • Defender advanced hunting uses Device*, Email*, Identity* and Cloud* tables with a Timestamp column; Sentinel and Log Analytics tables use TimeGenerated.
  • Table and column names are case sensitive; string comparison with == is case sensitive, =~ is not.
  • Queries below are starting points. Tune thresholds and exclusions to your estate before using them for alerting.

Core operators

OperatorUse
whereFilter rows
project / project-awayChoose / drop columns
extendAdd calculated columns
summarize ... byAggregate (count, dcount, make_set, arg_max)
join kind=innerCombine tables on a key
top N by / sort byOrder results
bin(Timestamp, 1h)Group times into buckets
has / contains / startswithString matching; has uses the term index and is faster
in~ (...)Case insensitive list match
ago(7d)Relative time
parse_json() / mv-expandWork with dynamic (JSON) columns

Sign ins

Failed sign ins by user and result code (Sentinel, Microsoft Entra ID):

KQL
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType != "0"
| summarize Failures = count(), IPs = dcount(IPAddress) by UserPrincipalName, ResultType
| sort by Failures desc

Password spraying pattern: one IP, many accounts:

KQL
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType in ("50126", "50053")
| summarize Accounts = dcount(UserPrincipalName), Attempts = count() by IPAddress
| where Accounts > 10
| sort by Accounts desc

Successful sign ins from countries a user has not used in the previous 30 days. Users with no earlier sign ins are kept and flagged as NewUser, so new accounts are not silently dropped:

KQL
let lookback = SigninLogs
| where TimeGenerated between (ago(30d) .. ago(1d)) and ResultType == "0"
| summarize KnownCountries = make_set(tostring(LocationDetails.countryOrRegion)) by UserPrincipalName;
SigninLogs
| where TimeGenerated > ago(1d) and ResultType == "0"
| extend Country = tostring(LocationDetails.countryOrRegion)
| join kind=leftouter lookback on UserPrincipalName
| extend NewUser = isnull(array_length(KnownCountries))
| where NewUser or not(set_has_element(KnownCountries, Country))
| project TimeGenerated, UserPrincipalName, Country, IPAddress, AppDisplayName, NewUser

Email and mailbox

New or changed inbox rules (Defender, CloudAppEvents); check RuleConfig for forwarding, redirect or delete actions:

KQL
CloudAppEvents
| where Timestamp > ago(7d)
| where ActionType in ("New-InboxRule", "Set-InboxRule", "UpdateInboxRules")
| project Timestamp, AccountDisplayName, ActionType, IPAddress, RuleConfig = RawEventData.Parameters, RawEventData
| sort by Timestamp desc

Who received a known phishing message (Defender):

KQL
EmailEvents
| where Timestamp > ago(7d)
| where Subject has "invoice" and SenderFromDomain == "example.com"
| project Timestamp, RecipientEmailAddress, SenderFromAddress, Subject, DeliveryAction

Endpoint

Encoded PowerShell command lines (Defender). PowerShell accepts any short form of -EncodedCommand, such as -e, -ec or -enc, so match the pattern followed by a Base64 string:

KQL
DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName in~ ("powershell.exe", "pwsh.exe")
| where ProcessCommandLine matches regex @"(?i)\s[-/](e|ec|en[a-z]*)\s+[A-Za-z0-9+/=]{20,}"
| project Timestamp, DeviceName, AccountName, ProcessCommandLine, InitiatingProcessFileName

Remote monitoring and management tools starting for the first time on a device (Defender):

KQL
let rmm = dynamic(["anydesk.exe", "screenconnect.windowsclient.exe", "ateraagent.exe", "teamviewer.exe"]);
DeviceProcessEvents
| where Timestamp > ago(30d)
| where FileName in~ (rmm)
| summarize FirstSeen = min(Timestamp) by DeviceName, FileName
| where FirstSeen > ago(1d)

Performance tips

  • Put the time filter first and keep the window as small as the question allows.
  • Prefer has over contains for whole terms.
  • project only the columns you need before a join.
  • Use summarize instead of returning raw rows when hunting across large tables.

Practitioner note

Save tuned versions of these as hunting queries or analytics rules rather than retyping them during an incident. Note the Sentinel and Defender column names differ for similar data (TimeGenerated against Timestamp, for example), so test each query in the console it will run in.

Sources

Suggest a correction