← All cheat sheets

MICROSOFT SENTINEL KQL

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

OVERVIEW#

Microsoft Sentinel is the cloud SIEM built on Log Analytics. It uses
KQL over ingested tables (Windows events, sign-ins, Azure activity,
syslog, custom). This sheet covers the key tables, KQL patterns, and
analytics/hunting queries. Overlaps with Defender XDR KQL but targets
Log Analytics schemas.

CORE TABLES#

SecurityEvent                # Windows Security log (via AMA/MMA)
SigninLogs                   # Entra ID interactive sign-ins
AADNonInteractiveUserSignInLogs   # Non-interactive (tokens)
AuditLogs                    # Entra ID directory audit
AzureActivity                # Azure control-plane operations
SecurityAlert                # Alerts from connected products
DeviceProcessEvents          # If Defender XDR connector enabled
Syslog / CommonSecurityEvent # Linux syslog / CEF (firewalls)
OfficeActivity               # M365 / Exchange / SharePoint audit
ThreatIntelligenceIndicator  # TI feeds

KQL ESSENTIALS#

| where TimeGenerated > ago(1d)              # Time filter (always first)
| where EventID == 4625                      # Field filter
| summarize count() by Account, IPAddress    # Aggregate
| extend GeoIP = geo_info_from_ip_address(IP)# Enrichment function
| join kind=inner (T) on Account             # Correlate
| render timechart                           # Visualize
| project TimeGenerated, Account, Computer   # Select

COMMON WINDOWS EVENT IDS#

# 4624 logon | 4625 failed logon | 4634 logoff
# 4672 special privileges | 4688 process create (w/ cmdline)
# 4697/7045 service install | 4698 scheduled task
# 4720 user created | 4728/4732 added to group
# 4768 TGT | 4769 TGS (Kerberoast) | 4771 preauth failed

HUNT - BRUTE FORCE / PASSWORD SPRAY#

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType != 0                       // failed
| summarize failures=count(), users=dcount(UserPrincipalName)
    by IPAddress, bin(TimeGenerated, 1h)
| where users > 10 and failures > 50          // spray = many users
| order by failures desc

HUNT - IMPOSSIBLE TRAVEL (SIMPLE)#

SigninLogs
| where ResultType == 0
| project TimeGenerated, UserPrincipalName, IPAddress,
    City=tostring(LocationDetails.city),
    Country=tostring(LocationDetails.countryOrRegion)
| order by UserPrincipalName, TimeGenerated asc
// pair with the built-in Anomalous Sign-in analytics rule

HUNT - RC4 KERBEROAST (4769)#

SecurityEvent
| where EventID == 4769
| where TicketEncryptionType == "0x17"        // RC4
| where ServiceName !endswith "$"             // user SPN, not machine
| summarize count() by Computer, Account, ServiceName, bin(TimeGenerated,1h)
| where count_ > 10

HUNT - NEW SERVICE INSTALL (PSEXEC)#

SecurityEvent
| where EventID in (7045, 4697)
| project TimeGenerated, Computer, ServiceFileName=ServiceName, Account
| where ServiceFileName has_any ("\\\\","%COMSPEC%","cmd /c","powershell")

HUNT - PRIVILEGED GROUP CHANGE#

SecurityEvent
| where EventID in (4728, 4732, 4756)         // added to group
| where TargetUserName has_any ("Domain Admins","Enterprise Admins",
    "Administrators")
| project TimeGenerated, Computer, MemberName=SubjectUserName, TargetUserName

HUNT - RISKY AZURE OPERATIONS#

AzureActivity
| where OperationNameValue has_any
    ("Microsoft.Authorization/roleAssignments/write",
     "Microsoft.KeyVault/vaults/write",
     "Microsoft.Network/networkSecurityGroups/write")
| project TimeGenerated, Caller, OperationNameValue, ActivityStatusValue

ANALYTICS RULE PATTERN#

# In a scheduled analytics rule:
#   - query returns entities (Account, IP, Host)
#   - map entities in "Entity mapping" for investigation graph
#   - set severity, MITRE tactics, and (optional) automation playbook
# Keep query runtime < the lookback; add | summarize to bound volume

EXAMPLES#

// password spray from a single IP against many accounts, last 24h
SigninLogs
| where TimeGenerated > ago(1d) and ResultType != 0
| summarize u=dcount(UserPrincipalName), f=count() by IPAddress
| where u > 15 and f > 60

// RC4 service-ticket spikes indicative of Kerberoasting
SecurityEvent
| where EventID == 4769 and TicketEncryptionType == "0x17"
| where ServiceName !endswith "$"
| summarize count() by Account, ServiceName, bin(TimeGenerated, 30m)

NOTES#

- Always filter TimeGenerated first - Log Analytics bills/scans by
  data volume; narrow time + table early
- 4688 command-line auditing must be enabled by policy or it is blank
- Sentinel + Defender XDR can share tables when the XDR connector is
  on; prefer XDR device tables for endpoint depth
- Watchlists enrich queries with asset criticality / allowlists
- Convert Sigma with -p sentinel-windows (SecurityEvent schema)
- Map to DORA incident detection + NIS2; export confirmed incidents
  to the CSSF reporting workflow (see CSSF-CIRCULARS.txt)

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