SreejithReji/spl-soc-queries
GitHub: SreejithReji/spl-soc-queries
Stars: 0 | Forks: 0
# SPL SOC Query Library 🔎
A comprehensive SPL (Search Processing Language) reference for SOC analysts — from first search to advanced threat hunting.
Used in **Splunk Enterprise**, **Splunk Cloud**, and **Splunk ES (Enterprise Security)**.
Built by a SOC analyst, for SOC analysts.
## What is SPL?
SPL is the query language for Splunk — one of the most widely deployed SIEMs in enterprise SOC environments. If your organisation runs Splunk, you will use SPL every shift for alert investigation, threat hunting, dashboard building, and detection engineering.
## Repository Structure
spl-soc-queries/
│
├── 01_foundations/
│ ├── 01_basic_search.spl
│ ├── 02_filtering.spl
│ ├── 03_fields_and_tables.spl
│ ├── 04_stats_and_aggregation.spl
│ └── 05_time_ranges.spl
│
├── 02_authentication/
│ ├── 01_failed_logons.spl
│ ├── 02_brute_force_detection.spl
│ ├── 03_successful_after_failures.spl
│ ├── 04_account_lockouts.spl
│ └── 05_privileged_account_activity.spl
│
├── 03_network/
│ ├── 01_suspicious_ports.spl
│ ├── 02_c2_beaconing.spl
│ ├── 03_dns_tunnelling.spl
│ ├── 04_large_data_transfers.spl
│ └── 05_port_scanning.spl
│
├── 04_endpoint/
│ ├── 01_suspicious_processes.spl
│ ├── 02_powershell_execution.spl
│ ├── 03_persistence_mechanisms.spl
│ ├── 04_lateral_movement.spl
│ └── 05_credential_dumping.spl
│
├── 05_threat_hunting/
│ ├── 01_lolbins_detection.spl
│ ├── 02_ransomware_indicators.spl
│ ├── 03_data_exfiltration.spl
│ ├── 04_cobalt_strike_indicators.spl
│ └── 05_mitre_attack_mapping.spl
│
├── 06_incident_response/
│ ├── 01_user_activity_timeline.spl
│ ├── 02_ip_investigation.spl
│ ├── 03_host_investigation.spl
│ ├── 04_alert_summary.spl
│ └── 05_ioc_search.spl
│
└── 07_dashboards/
├── 01_soc_daily_overview.spl
├── 02_top_alerts_by_severity.spl
└── 03_shift_handover_summary.spl
## Quick Reference — SPL Syntax Cheatsheet
### Basic structure
index=windows sourcetype=WinEventLog
| where EventCode=4625
| table _time, Account_Name, src_ip, Workstation_Name
| sort -_time
| head 100
### Common commands
| Command | What it does | Example |
|---|---|---|
| `search` | Filter events | `search EventCode=4625` |
| `where` | Filter with expressions | `where count > 10` |
| `table` | Select columns | `table _time, src_ip, user` |
| `fields` | Include/exclude fields | `fields - _raw` |
| `stats` | Aggregate data | `stats count by src_ip` |
| `chart` | Chart data | `chart count by src_ip` |
| `timechart` | Time-based chart | `timechart count by EventCode` |
| `eval` | Create/modify fields | `eval severity=if(count>50,"High","Low")` |
| `rex` | Regex extraction | `rex field=_raw "src=(?\d+\.\d+\.\d+\.\d+)"` |
| `lookup` | Enrich with lookup table | `lookup threat_intel ip as src_ip` |
| `join` | Join two searches | `join src_ip [search index=threat]` |
| `transaction` | Group related events | `transaction src_ip maxspan=1h` |
| `dedup` | Remove duplicates | `dedup src_ip` |
| `sort` | Sort results | `sort -count` |
| `head` / `tail` | Limit results | `head 100` |
| `rename` | Rename field | `rename src_ip as SourceIP` |
### Time filters
| earliest=-24h latest=now
| earliest=-7d
| earliest="01/15/2024:00:00:00" latest="01/16/2024:00:00:00"
### String operations
| where like(user, "%admin%")
| where match(user, "^svc_")
| search CommandLine="*mimikatz*"
| search CommandLine IN ("*mimikatz*", "*invoke-mimikatz*", "*sekurlsa*")
## Common Splunk Indexes and Sourcetypes
| Index | Sourcetype | Contains |
|---|---|---|
| `index=windows` | `WinEventLog:Security` | Windows Security Event Logs |
| `index=windows` | `WinEventLog:System` | Windows System Logs |
| `index=windows` | `XmlWinEventLog:Security` | Windows XML Security Events |
| `index=network` | `cisco:asa` | Cisco ASA Firewall |
| `index=network` | `palo_alto_networks` | Palo Alto Firewall |
| `index=web` | `access_combined` | Web server access logs |
| `index=dns` | `stream:dns` | DNS query logs |
| `index=endpoint` | `sysmon` | Sysmon endpoint logs |
| `index=main` | `syslog` | Generic syslog |
## The Queries
### 01 — Foundations
#### Basic search
`Your first SPL search — get recent Windows Security events`
index=windows sourcetype="WinEventLog:Security"
| head 10
`Count all events in last 24 hours`
index=windows sourcetype="WinEventLog:Security" earliest=-24h
| stats count
`See all unique Event Codes`
index=windows sourcetype="WinEventLog:Security" earliest=-24h
| stats count by EventCode
| sort -count
#### Filtering
`Filter by single EventCode`
index=windows sourcetype="WinEventLog:Security" EventCode=4625
`Filter by multiple EventCodes`
index=windows sourcetype="WinEventLog:Security" (EventCode=4624 OR EventCode=4625 OR EventCode=4740)
`Filter with text search`
index=windows sourcetype="WinEventLog:Security" Account_Name="*admin*"
`Combine filters`
index=windows sourcetype="WinEventLog:Security" EventCode=4625 earliest=-24h
| where like(Account_Name, "%admin%")
#### Table and fields
`Show only the columns you care about`
index=windows sourcetype="WinEventLog:Security" EventCode=4625 earliest=-24h
| table _time, Account_Name, Workstation_Name, src_ip, Logon_Type
| sort -_time
#### Stats and aggregation
`Count events by EventCode`
index=windows sourcetype="WinEventLog:Security" earliest=-24h
| stats count by EventCode
| sort -count
`Count failed logons per account`
index=windows sourcetype="WinEventLog:Security" EventCode=4625 earliest=-24h
| stats count as FailedAttempts by Account_Name
| sort -FailedAttempts
### 02 — Authentication Queries
#### Failed logon detection (EventCode 4625)
index=windows sourcetype="WinEventLog:Security" EventCode=4625 earliest=-24h
| table _time, Account_Name, src_ip, Workstation_Name, Logon_Type, Sub_Status
| sort -_time
#### Brute force detection — threshold based
`Accounts with more than 10 failed logons in 1 hour`
index=windows sourcetype="WinEventLog:Security" EventCode=4625 earliest=-1h
| stats count as FailedAttempts by Account_Name, src_ip
| where FailedAttempts > 10
| eval Severity=case(
FailedAttempts > 50, "Critical",
FailedAttempts > 20, "High",
FailedAttempts > 10, "Medium",
true(), "Low")
| sort -FailedAttempts
#### Successful logon after multiple failures
`Classic brute force success pattern`
index=windows sourcetype="WinEventLog:Security" (EventCode=4624 OR EventCode=4625) earliest=-1h
| stats
count(eval(EventCode=4625)) as FailedCount,
count(eval(EventCode=4624)) as SuccessCount
by Account_Name, src_ip
| where FailedCount > 5 AND SuccessCount > 0
| eval Verdict="Possible Brute Force Success"
| sort -FailedCount
#### Account lockouts (EventCode 4740)
index=windows sourcetype="WinEventLog:Security" EventCode=4740 earliest=-24h
| table _time, TargetUserName, Computer, SubjectUserName
| sort -_time
#### Privileged account activity outside business hours
index=windows sourcetype="WinEventLog:Security" EventCode=4624 earliest=-7d
| eval Hour=strftime(_time, "%H")
| where (Hour < "07" OR Hour > "19")
| where like(Account_Name, "%admin%") OR like(Account_Name, "%svc_%")
| table _time, Account_Name, src_ip, Workstation_Name, Hour
| sort -_time
### 03 — Network Queries
#### Connections to suspicious ports
index=network earliest=-24h
| where dest_port IN (22, 23, 445, 3389, 4444, 8080, 9001, 1337, 31337)
| where action="blocked"
| table _time, src_ip, dest_ip, dest_port, protocol, action
| sort -_time
#### C2 beaconing detection
`Regular interval connections — C2 beacon pattern`
index=network earliest=-24h
| stats
count as ConnectionCount,
min(_time) as FirstSeen,
max(_time) as LastSeen
by src_ip, dest_ip, dest_port
| eval Duration=round((LastSeen-FirstSeen)/60,2)
| eval BeaconRate=round(ConnectionCount/max(Duration,1),2)
| where ConnectionCount > 10 AND BeaconRate > 1
| sort -BeaconRate
| convert ctime(FirstSeen) ctime(LastSeen)
#### DNS tunnelling detection
`Long subdomain queries — DNS tunnelling indicator`
index=dns earliest=-24h
| eval DomainLength=len(query)
| where DomainLength > 50
| stats
count as QueryCount,
dc(query) as UniqueDomains
by src_ip, host
| where QueryCount > 5
| sort -QueryCount
#### Large outbound data transfers
`Possible data exfiltration`
index=network earliest=-24h
| where bytes_out > 10000000
| where NOT (like(dest_ip, "10.%") OR like(dest_ip, "192.168.%") OR like(dest_ip, "172.1%"))
| eval MB_Sent=round(bytes_out/1048576,2)
| table _time, src_ip, dest_ip, dest_port, MB_Sent, protocol
| sort -MB_Sent
#### Port scanning detection
`Single IP hitting many ports — port scan indicator`
index=network earliest=-1h
| stats dc(dest_port) as UniquePortsScanned, values(dest_port) as PortList
by src_ip
| where UniquePortsScanned > 20
| sort -UniquePortsScanned
### 04 — Endpoint Queries
#### Suspicious process execution
index=endpoint sourcetype="WinEventLog:Security" EventCode=4688 earliest=-24h
| search Process_Name IN (
"*mimikatz*", "*procdump*", "*psexec*",
"*wce.exe*", "*fgdump*", "*pwdump*",
"*meterpreter*", "*cobaltrike*", "*empire*"
)
| table _time, Computer, Account_Name, Process_Name, Process_Command_Line
| sort -_time
#### Suspicious PowerShell execution
index=endpoint sourcetype="WinEventLog:Security" EventCode=4688 earliest=-24h
| where like(Process_Name, "%powershell%") OR like(Process_Name, "%pwsh%")
| where
like(Process_Command_Line, "%-enc%") OR
like(Process_Command_Line, "%-nop%") OR
like(Process_Command_Line, "%invoke-expression%") OR
like(Process_Command_Line, "%iex%") OR
like(Process_Command_Line, "%downloadstring%") OR
like(Process_Command_Line, "%bypass%") OR
like(Process_Command_Line, "%-hidden%")
| table _time, Computer, Account_Name, Process_Command_Line
| sort -_time
#### Persistence — scheduled tasks and services
`New scheduled tasks created`
index=windows sourcetype="WinEventLog:Security" EventCode=4698 earliest=-24h
| table _time, Computer, Account_Name, TaskName
| sort -_time
`New services installed`
index=windows sourcetype="WinEventLog:System" EventCode=7045 earliest=-24h
| table _time, ComputerName, AccountName, ServiceName, ImagePath
| sort -_time
#### Lateral movement detection
`Remote logons to multiple hosts from same account — lateral movement`
index=windows sourcetype="WinEventLog:Security" EventCode=4624 Logon_Type=3 earliest=-24h
| stats dc(Computer) as RemoteHosts, values(Computer) as HostList
by Account_Name, src_ip
| where RemoteHosts > 3
| sort -RemoteHosts
#### Credential dumping indicators
`LSASS access from suspicious processes`
index=endpoint sourcetype="sysmon" EventCode=10 earliest=-24h
| where like(TargetImage, "%lsass.exe%")
| where NOT SourceImage IN (
"C:\\Windows\\System32\\MsMpEng.exe",
"C:\\Windows\\System32\\svchost.exe",
"C:\\Windows\\System32\\lsass.exe"
)
| table _time, Computer, SourceImage, TargetImage, GrantedAccess
| sort -_time
### 05 — Threat Hunting Queries
#### Living off the Land (LOLBins)
index=endpoint sourcetype="WinEventLog:Security" EventCode=4688 earliest=-24h
| search Process_Name IN (
"*certutil*", "*bitsadmin*", "*regsvr32*",
"*mshta*", "*wscript*", "*cscript*",
"*rundll32*", "*msiexec*", "*installutil*",
"*regasm*", "*wmic*", "*netsh*", "*at.exe*"
)
| where NOT Parent_Process_Name IN (
"*explorer.exe*", "*services.exe*", "*svchost.exe*"
)
| table _time, Computer, Account_Name, Process_Name,
Process_Command_Line, Parent_Process_Name
| sort -_time
#### Ransomware indicators
`Mass file changes in short time window`
index=endpoint sourcetype="sysmon" (EventCode=11 OR EventCode=23) earliest=-1h
| bucket span=5m _time
| stats count as FileChanges, dc(TargetFilename) as UniqueFiles
by _time, Computer, Image
| where FileChanges > 100
| sort -FileChanges
#### Cobalt Strike indicators
`Named pipe creation — Cobalt Strike default named pipes`
index=endpoint sourcetype="sysmon" EventCode=17 earliest=-24h
| search PipeName IN (
"*mojo*", "*wkssvc*", "*ntsvcs*",
"*DserNamePipe*", "*SearchTextHarvester*",
"*msagent_*", "*MSSE-*"
)
| table _time, Computer, ProcessId, PipeName, Image
| sort -_time
#### Data exfiltration hunting
`Unusual outbound volume per host per hour`
index=network earliest=-24h
| where NOT (like(dest_ip,"10.%") OR like(dest_ip,"192.168.%"))
| bucket span=1h _time
| stats sum(bytes_out) as TotalBytesSent, dc(dest_ip) as UniqueDestinations
by _time, src_ip
| eval TotalMB=round(TotalBytesSent/1048576,2)
| where TotalMB > 100
| sort -TotalMB
#### MITRE ATT&CK mapping — T1059 Command and Scripting Interpreter
index=endpoint sourcetype="WinEventLog:Security" EventCode=4688 earliest=-24h
| search Process_Name IN (
"*powershell*", "*cmd.exe*", "*wscript*",
"*cscript*", "*mshta*", "*bash*", "*python*"
)
| eval MITRE_Technique="T1059 - Command and Scripting Interpreter"
| eval MITRE_Tactic="Execution"
| table _time, Computer, Account_Name, Process_Name,
Process_Command_Line, MITRE_Technique, MITRE_Tactic
| sort -_time
### 06 — Incident Response Queries
#### Full user activity timeline
`Complete timeline for a specific user — change the username`
index=* earliest=-24h
(Account_Name="administrator" OR user="administrator" OR src_user="administrator")
| eval EventDescription=case(
EventCode=4624, "Successful Logon",
EventCode=4625, "Failed Logon",
EventCode=4688, "Process Created: ".Process_Command_Line,
EventCode=4698, "Scheduled Task Created",
EventCode=4720, "Account Created",
true(), "Event: ".EventCode)
| table _time, host, EventDescription, src_ip
| sort _time
#### IP address investigation
`Everything associated with a suspicious IP`
index=* earliest=-24h (src_ip="185.220.101.45" OR dest_ip="185.220.101.45" OR src="185.220.101.45")
| eval Direction=if(src_ip="185.220.101.45","Outbound","Inbound")
| table _time, index, sourcetype, Direction, src_ip, dest_ip, dest_port, Account_Name
| sort _time
#### Host investigation
`Full picture of a specific host`
index=* host="DESKTOP-HR-042" earliest=-24h
| eval Category=case(
sourcetype="WinEventLog:Security" AND EventCode=4624, "Logon Success",
sourcetype="WinEventLog:Security" AND EventCode=4625, "Logon Failure",
sourcetype="WinEventLog:Security" AND EventCode=4688, "Process Execution",
sourcetype="sysmon" AND EventCode=3, "Network Connection",
true(), sourcetype)
| table _time, Category, Account_Name, src_ip, Process_Name, Process_Command_Line
| sort _time
#### IOC search across all indexes
`Search for a hash, IP or domain everywhere at once`
index=* earliest=-24h
("185.220.101.45" OR "d41d8cd98f00b204e9800998ecf8427e" OR "malicious-domain.xyz")
| table _time, index, sourcetype, host, src_ip, dest_ip, Account_Name, Process_Name
| sort _time
### 07 — Dashboard Queries
#### SOC daily overview
index=windows sourcetype="WinEventLog:Security"
(EventCode=4624 OR EventCode=4625 OR EventCode=4740
OR EventCode=4720 OR EventCode=4728 OR EventCode=7045)
earliest=-24h
| eval EventDescription=case(
EventCode=4624, "Successful Logons",
EventCode=4625, "Failed Logons",
EventCode=4740, "Account Lockouts",
EventCode=4720, "Accounts Created",
EventCode=4728, "Added to Admin Group",
EventCode=7045, "Services Installed",
true(), "Other")
| stats count by EventDescription
| sort -count
#### Top alerts by severity
index=notable earliest=-8h
| stats count as AlertCount by rule_name, urgency
| eval SeverityOrder=case(
urgency="critical", 1,
urgency="high", 2,
urgency="medium", 3,
urgency="low", 4,
true(), 5)
| sort SeverityOrder, -AlertCount
| table urgency, rule_name, AlertCount
#### Shift handover summary
index=windows sourcetype="WinEventLog:Security"
(EventCode=4625 OR EventCode=4740 OR EventCode=4720 OR EventCode=4728)
earliest=-8h
| stats
count(eval(EventCode=4625)) as FailedLogons,
count(eval(EventCode=4740)) as AccountLockouts,
count(eval(EventCode=4720)) as NewAccounts,
count(eval(EventCode=4728)) as AdminGroupChanges
| eval ShiftSummary="Shift Summary | Failed Logons: ".FailedLogons
." | Lockouts: ".AccountLockouts
." | New Accounts: ".NewAccounts
." | Admin Changes: ".AdminGroupChanges
| table ShiftSummary, FailedLogons, AccountLockouts, NewAccounts, AdminGroupChanges
## Key EventCodes Reference
| EventCode | Description | Why it matters |
|---|---|---|
| 4624 | Successful logon | Baseline — check for unusual times/sources |
| 4625 | Failed logon | Brute force indicator |
| 4634 | Logoff | Session duration |
| 4648 | Explicit credential logon | Lateral movement |
| 4672 | Special privileges assigned | Privilege escalation |
| 4688 | Process created | Malware execution |
| 4698 | Scheduled task created | Persistence |
| 4720 | User account created | Backdoor account |
| 4728 | Added to security group | Privilege escalation |
| 4740 | Account locked out | Brute force |
| 7045 | Service installed | Malware persistence |
| 1 | Sysmon: Process created | Detailed process tracking |
| 3 | Sysmon: Network connection | Outbound connection tracking |
| 10 | Sysmon: Process accessed | LSASS dumping detection |
| 11 | Sysmon: File created | Dropper/ransomware detection |
| 17 | Sysmon: Pipe created | Cobalt Strike detection |
## SPL vs KQL — Quick Comparison
| Concept | SPL | KQL |
|---|---|---|
| Filter | `search EventCode=4625` | `where EventID == 4625` |
| Select columns | `table _time, user` | `project TimeGenerated, Account` |
| Count by field | `stats count by user` | `summarize count() by Account` |
| Rename field | `rename src_ip as SourceIP` | `extend SourceIP = SrcIpAddr` |
| Conditional | `eval x=if(count>10,"High","Low")` | `extend x = iif(count>10,"High","Low")` |
| Regex extract | `rex field=_raw "src=(?P...)"` | `parse kind=regex ...` |
| Time bucket | `bucket span=1h _time` | `bin TimeGenerated span=1h` |
| Join | `join src_ip [search ...]` | `join kind=inner ... on field` |
## Learning Resources
- 📖 [Splunk Documentation](https://docs.splunk.com/Documentation/Splunk)
- 🎓 [Splunk Free Training — Fundamentals 1](https://www.splunk.com/en_us/training/free-courses/splunk-fundamentals-1.html)
- 🧪 [Splunk Attack Range](https://github.com/splunk/attack_range)
- 🎯 [TryHackMe — Splunk Rooms](https://tryhackme.com)
- 🔍 [Splunk Security Essentials App](https://splunkbase.splunk.com/app/3435)
- 📚 [MITRE ATT&CK Framework](https://attack.mitre.org)
- 🛡️ [Splunk ES (Enterprise Security) Docs](https://docs.splunk.com/Documentation/ES)
## About
Built as part of a SOC analyst learning portfolio.
**Author:** Sreejith Reji | MSc Cyber Security | CEH | Security+
Part of the [cybersecurity-portfolio](https://github.com/SreejithReji/cybersecurity-portfolio) collection.