Side-by-side syntax for the two query languages you’ll actually touch in a SOC — KQL (Microsoft Sentinel / Defender) and SPL (Splunk) — plus the query patterns that come up constantly during BTL1-style triage.
Prepared as a reference for BTL1 and general SOC operations.
| Task | KQL (Sentinel/Defender) | SPL (Splunk) |
|---|---|---|
| Pick a data source | SecurityEvent |
index=security |
| Filter | \| where EventID == 4625 |
EventID=4625 |
| Filter on time | \| where TimeGenerated > ago(24h) |
earliest=-24h |
| Select fields | \| project TimeGenerated, Account, Computer |
\| table _time, user, host |
| Sort | \| sort by TimeGenerated desc |
\| sort -_time |
| Limit results | \| take 50 |
\| head 50 |
| Count | \| summarize count() |
\| stats count |
| Group by | \| summarize count() by Account |
\| stats count by user |
| Distinct values | \| distinct Account |
\| dedup user |
| String contains | \| where Account contains "admin" |
\| search user="*admin*" |
| Regex match | \| where Account matches regex "^adm.*" |
\| regex user="^adm.*" |
| Join | \| join kind=inner (OtherTable) on Computer |
\| join host [search index=other] |
| Time-bucket / bin | \| summarize count() by bin(TimeGenerated, 1h) |
\| bucket _time span=1h \| stats count by _time |
Failed logon spike (brute-force / spray candidate):
SecurityEvent
| where EventID == 4625
| summarize FailCount = count() by Account, bin(TimeGenerated, 10m)
| where FailCount > 10
| order by FailCount desc
Password spray pattern (many accounts, one source IP):
SecurityEvent
| where EventID == 4625
| summarize DistinctAccounts = dcount(Account) by IpAddress, bin(TimeGenerated, 15m)
| where DistinctAccounts > 15
Kerberoasting indicator (bursts of 4769 requests):
SecurityEvent
| where EventID == 4769
| summarize TicketRequests = count() by Account, bin(TimeGenerated, 5m)
| where TicketRequests > 20
Suspicious PowerShell (encoded command):
DeviceProcessEvents
| where FileName =~ "powershell.exe"
| where ProcessCommandLine contains "-enc" or ProcessCommandLine contains "-EncodedCommand"
Impossible travel / new-location sign-in:
SigninLogs
| where ResultType == 0
| summarize Countries = make_set(LocationDetails.countryOrRegion) by UserPrincipalName, bin(TimeGenerated, 1h)
| where array_length(Countries) > 1
Failed logon spike:
index=security EventCode=4625
| bucket _time span=10m
| stats count as fail_count by user, _time
| where fail_count > 10
| sort -fail_count
Password spray pattern:
index=security EventCode=4625
| bucket _time span=15m
| stats dc(user) as distinct_accounts by src_ip, _time
| where distinct_accounts > 15
Suspicious PowerShell (encoded command):
index=endpoint process_name="powershell.exe"
| search process="*-enc*" OR process="*-EncodedCommand*"
Beaconing detection (regular-interval outbound connections):
index=network
| stats count by src_ip, dest_ip
| eventstats avg(count) as avg_conn by src_ip, dest_ip
| where count > (avg_conn * 3)
Top talkers by bytes transferred:
index=network
| stats sum(bytes) as total_bytes by src_ip, dest_ip
| sort -total_bytes
| head 20
| Concept | KQL common field | SPL common field |
|---|---|---|
| Timestamp | TimeGenerated |
_time |
| Source IP | IpAddress / SrcIpAddr |
src_ip / src |
| Destination IP | DestinationIpAddress |
dest_ip / dest |
| Username | Account / UserPrincipalName |
user |
| Hostname | Computer / DeviceName |
host |
| Process name | FileName |
process_name |
| Full command line | ProcessCommandLine |
process / CommandLine |
| Event/Windows Event ID | EventID |
EventCode |
| Operator | KQL | SPL |
|---|---|---|
| Logical AND | and |
(implicit space, or AND) |
| Logical OR | or |
OR |
| NOT | not / != |
NOT |
| Wildcard | * inside contains/has |
* directly in search terms |
| Case-insensitive equals | =~ |
search is case-insensitive by default |
| Time range shorthand | ago(1h), ago(7d) |
earliest=-1h, earliest=-7d@d |
has over contains when matching whole words — has is indexed and much faster on large tables.\| pipes) — Splunk applies index-time filtering before the pipeline, so index=x sourcetype=y EventCode=4625 beats filtering EventCode later with a search pipe.Companion to siem-splunk-elk-cheatsheet-professional.md in this folder, and threat-intelligence-mitre-attack-cheatsheet-professional.md.
Prepared as a reference for BTL1 and general SOC operations.