← All cheat sheets

MICROSOFT 365 DEFENDER KQL

Plain-text reference · 5 KB. Read it, search it (Ctrl-F) or print it.

OVERVIEW#

Microsoft Defender XDR (advanced hunting) uses KQL over unified
tables spanning endpoint, identity, email, and cloud apps. This sheet
covers the core tables, KQL operators, and ready-to-adapt hunting
queries. Runs in security.microsoft.com > Advanced hunting.

CORE TABLES#

DeviceProcessEvents          # Process creation (endpoint)
DeviceNetworkEvents          # Network connections
DeviceFileEvents             # File create/modify/delete
DeviceRegistryEvents         # Registry changes
DeviceLogonEvents            # Interactive/network logons (endpoint)
DeviceImageLoadEvents        # DLL / image loads
DeviceEvents                 # Misc sensor events (AMSI, WMI, etc.)
IdentityLogonEvents          # AD/Entra logons (identity)
IdentityDirectoryEvents      # Directory changes
IdentityQueryEvents          # LDAP/SAMR queries
EmailEvents / EmailAttachmentInfo / EmailUrlInfo   # Defender for O365
CloudAppEvents               # Defender for Cloud Apps
AlertEvidence / AlertInfo    # Alerts + linked entities

KQL OPERATORS#

| where <cond>               # Filter rows
| project Col1, Col2         # Select columns
| extend NewCol = ...        # Add computed column
| summarize count() by X     # Aggregate
| join kind=inner (T2) on Key# Join tables
| order by Timestamp desc    # Sort
| take 100 / | limit 100     # Cap rows
| distinct AccountName       # Unique values
| where Timestamp > ago(24h) # Time window

STRING / MATCH FUNCTIONS#

| where FileName =~ "certutil.exe"           # Case-insensitive equals
| where ProcessCommandLine has "urlcache"    # Token match (fast)
| where ProcessCommandLine contains "-enc"   # Substring
| where InitiatingProcessFileName in~ ("cmd.exe","powershell.exe")
| where FolderPath matches regex @"\\Temp\\"
| where AccountName startswith "svc"

HUNT - SUSPICIOUS PROCESS#

DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName =~ "certutil.exe"
| where ProcessCommandLine has_any ("urlcache","-decode","-f http")
| project Timestamp, DeviceName, AccountName, ProcessCommandLine

HUNT - ENCODED POWERSHELL#

DeviceProcessEvents
| where FileName in~ ("powershell.exe","pwsh.exe")
| where ProcessCommandLine has_any ("-enc","-encodedcommand","frombase64")
| project Timestamp, DeviceName, AccountName, ProcessCommandLine
| order by Timestamp desc

HUNT - LSASS ACCESS#

DeviceEvents
| where ActionType == "OpenProcessApiCall"
| where FileName =~ "lsass.exe"
| summarize count() by DeviceName, InitiatingProcessFileName, AccountName

HUNT - LATERAL MOVEMENT (WMI/PSEXEC)#

DeviceProcessEvents
| where InitiatingProcessFileName in~ ("wmiprvse.exe","services.exe")
| where FileName in~ ("cmd.exe","powershell.exe")
| where ProcessCommandLine has_any ("\\\\","-nop","-enc")
| project Timestamp, DeviceName, AccountName, ProcessCommandLine

HUNT - RC4 KERBEROAST SIGNAL (IDENTITY)#

IdentityLogonEvents
| where Protocol == "Kerberos"
// correlate with high-volume service ticket requests per host;
// pair with DC 4769 (RC4 0x17) in Sentinel/QRadar for full signal

HUNT - NEW/RARE SIGNED BINARY#

DeviceProcessEvents
| where Timestamp > ago(30d)
| summarize first=min(Timestamp), n=count() by FileName, SHA256
| where first > ago(2d)                       // newly seen
| order by n asc

INCIDENTS & ALERTS#

AlertInfo
| where Timestamp > ago(24h)
| join AlertEvidence on AlertId
| where EntityType == "User"
| project Timestamp, Title, Severity, AccountName=EntityName

EXAMPLES#

// certutil web downloads across the estate, last 7 days
DeviceProcessEvents
| where Timestamp > ago(7d) and FileName =~ "certutil.exe"
| where ProcessCommandLine has "http"
| project Timestamp, DeviceName, AccountName, ProcessCommandLine

// find devices where lsass was opened by a non-standard process
DeviceEvents
| where ActionType == "OpenProcessApiCall" and FileName =~ "lsass.exe"
| where InitiatingProcessFileName !in~ ("wininit.exe","csrss.exe")
| summarize count() by DeviceName, InitiatingProcessFileName

NOTES#

- has/has_any are indexed and faster than contains for token matches
- Defender XDR retention is typically 30 days for advanced hunting
- Convert Sigma to these tables with pipeline -p microsoft_xdr
- Build detections as custom detection rules (they raise alerts +
  can trigger automated response)
- For LU FS clients, map hunts to DORA ICT incident detection and
  feed confirmed incidents into the 4h/72h/1-month reporting flow
- Pair with SENTINEL-KQL.txt (log analytics) and DETECTION-ENGINEERING

Defensive reference on CyberRamen. Offensive / red-team sheets live on OffensiveRamen.com.