siem-query-languages

SIEM Query Languages

Reference for Splunk SPL, Elastic KQL/EQL, and Microsoft Sentinel KQL with security-focused query patterns and platform comparison.

Knowledge Base

Language Overview

Feature	Splunk SPL	Elastic KQL	Sentinel KQL (Kusto)
Paradigm	Pipe-based search	Filter expressions	Pipe-based tabular
Strengths	Flexible, powerful stats	Simple filtering, EQL sequences	Rich analytics, joins, time-series
Learning curve	Medium	Low	Medium-High
Sequence detection	Transaction command	EQL sequences	Custom via joins/mv-expand
Machine learning	MLTK addon	ML jobs	Built-in KQL functions
Subsearch/subquery	Subsearch, join	N/A (use EQL)	let statements, join

Splunk SPL Reference

Core search syntax:

index=<index> sourcetype=<sourcetype> <keyword>
| where <condition>
| eval <new_field> = <expression>
| stats <function> by <field>
| table <fields>
| sort <field>

Essential SPL commands for security:

# Count events by field
| stats count by src_ip, dest_ip, dest_port

# Time-based aggregation
| timechart span=1h count by action

# Field extraction (regex)
| rex field=_raw "user=(?<username>\w+)"

# Lookup enrichment
| lookup asset_inventory ip AS src_ip OUTPUT hostname, owner, criticality

# Transaction (session grouping)
| transaction src_ip maxspan=5m maxpause=30s

# Subsearch
[search index=threat_intel | fields ioc_ip | rename ioc_ip AS dest_ip]

# Statistical outlier detection
| eventstats avg(bytes) AS avg_bytes, stdev(bytes) AS stdev_bytes by src_ip
| where bytes > (avg_bytes + 3*stdev_bytes)

# Accelerated datamodel search
| tstats count FROM datamodel=Authentication WHERE Authentication.action=failure BY Authentication.src, Authentication.user

Security query examples:

# Failed logins followed by success (brute force)
index=windows sourcetype=WinEventLog:Security EventCode IN (4625, 4624)
| stats values(EventCode) AS events, count(eval(EventCode=4625)) AS failures, count(eval(EventCode=4624)) AS successes, earliest(_time) AS first_seen, latest(_time) AS last_seen BY TargetUserName, IpAddress
| where failures >= 5 AND successes >= 1

# PowerShell encoded command execution
index=sysmon EventCode=1 Image="*\\powershell.exe"
| where match(CommandLine, "(?i)-e(nc|ncodedcommand)\s+[A-Za-z0-9+/=]{20,}")
| table _time, ComputerName, User, CommandLine, ParentImage

# Rare outbound connections (hunting)
index=proxy
| stats count BY dest_domain
| where count < 5
| sort count

# Data exfiltration (large transfers)
index=firewall action=allowed direction=outbound
| stats sum(bytes_out) AS total_bytes BY src_ip
| where total_bytes > 1073741824
| eval GB=round(total_bytes/1073741824, 2)
| sort -total_bytes