SreejithReji/spl-soc-queries
GitHub: SreejithReji/spl-soc-queries
一份覆盖认证、网络、端点、威胁狩猎及事件响应等场景的 Splunk SPL 查询参考库，帮助 SOC 分析师高效进行安全监控与调查。
Stars: 0 | Forks: 1
# SPL SOC 查询库 🔎
一份面向 SOC 分析师的全面 SPL（Search Processing Language）参考指南——从基础搜索到高级威胁狩猎。
适用于 **Splunk Enterprise**、**Splunk Cloud** 和 **Splunk ES (Enterprise Security)**。
由 SOC 分析师构建，为 SOC 分析师服务。
## 什么是 SPL?
SPL 是 Splunk 的查询语言——Splunk 是企业 SOC 环境中部署最广泛的 SIEM 之一。如果你的组织使用 Splunk，你在每个班次中都会使用 SPL 来进行告警调查、威胁狩猎、构建仪表板以及检测工程。
## 仓库结构
```
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
```
## 快速参考 — SPL 语法速查表
### 基本结构
```
index=windows sourcetype=WinEventLog
| where EventCode=4625
| table _time, Account_Name, src_ip, Workstation_Name
| sort -_time
| head 100
```
### 常用命令
| 命令 | 功能说明 | 示例 |
|---|---|---|
| `search` | 过滤事件 | `search EventCode=4625` |
| `where` | 使用表达式过滤 | `where count > 10` |
| `table` | 选择列 | `table _time, src_ip, user` |
| `fields` | 包含/排除字段 | `fields - _raw` |
| `stats` | 聚合数据 | `stats count by src_ip` |
| `chart` | 图表化数据 | `chart count by src_ip` |
| `timechart` | 基于时间的图表 | `timechart count by EventCode` |
| `eval` | 创建/修改字段 | `eval severity=if(count>50,"High","Low")` |
| `rex` | 正则表达式提取 | `rex field=_raw "src=(?\d+\.\d+\.\d+\.\d+)"` |
| `lookup` | 使用查找表丰富数据 | `lookup threat_intel ip as src_ip` |
| `join` | 关联两次搜索 | `join src_ip [search index=threat]` |
| `transaction` | 将相关事件分组 | `transaction src_ip maxspan=1h` |
| `dedup` | 移除重复项 | `dedup src_ip` |
| `sort` | 对结果排序 | `sort -count` |
| `head` / `tail` | 限制结果数量 | `head 100` |
| `rename` | 重命名字段 | `rename src_ip as SourceIP` |
### 时间过滤器
```
| earliest=-24h latest=now
| earliest=-7d
| earliest="01/15/2024:00:00:00" latest="01/16/2024:00:00:00"
```
### 字符串操作
```
| where like(user, "%admin%")
| where match(user, "^svc_")
| search CommandLine="*mimikatz*"
| search CommandLine IN ("*mimikatz*", "*invoke-mimikatz*", "*sekurlsa*")
```
## 常见的 Splunk 索引和 Sourcetype
| 索引 (Index) | Sourcetype | 包含内容 |
|---|---|---|
| `index=windows` | `WinEventLog:Security` | Windows 安全事件日志 |
| `index=windows` | `WinEventLog:System` | Windows 系统日志 |
| `index=windows` | `XmlWinEventLog:Security` | Windows XML 安全事件 |
| `index=network` | `cisco:asa` | Cisco ASA 防火墙 |
| `index=network` | `palo_alto_networks` | Palo Alto 防火墙 |
| `index=web` | `access_combined` | Web 服务器访问日志 |
| `index=dns` | `stream:dns` | DNS 查询日志 |
| `index=endpoint` | `sysmon` | Sysmon 端点日志 |
| `index=main` | `syslog` | 通用 syslog |
## 查询合集
### 01 — 基础
#### 基础搜索
```
`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
```
#### 过滤
```
`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%")
```
#### 表格与字段
```
`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
```
#### 统计与聚合
```
`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 — 身份验证查询
#### 失败登录检测 (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
```
#### 暴力破解检测 — 基于阈值
```
`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
```
#### 多次失败后成功登录
```
`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
```
#### 账户锁定 (EventCode 4740)
```
index=windows sourcetype="WinEventLog:Security" EventCode=4740 earliest=-24h
| table _time, TargetUserName, Computer, SubjectUserName
| sort -_time
```
#### 非工作时间的高权限账户活动
```
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 — 网络查询
#### 连接可疑端口
```
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 信标检测
```
`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 隧道检测
```
`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
```
#### 大量出站数据传输
```
`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
```
#### 端口扫描检测
```
`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 — 端点查询
#### 可疑进程执行
```
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
```
#### 可疑 PowerShell 执行
```
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
```
#### 持久化 — 计划任务与服务
```
`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
```
#### 横向移动检测
```
`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
```
#### 凭据转储指标
```
`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 — 威胁狩猎查询
#### 实战利用 (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
```
#### 勒索软件指标
```
`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 指标
```
`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
```
#### 数据泄露狩猎
```
`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 映射 — T1059 命令和脚本解释器
```
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 — 事件响应查询
#### 完整用户活动时间线
```
`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 地址调查
```
`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
```
#### 主机调查
```
`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 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 — 仪表板查询
#### SOC 每日概览
```
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
```
#### 按严重程度排名的高级告警
```
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
```
#### 班次交接总结
```
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
```
## 关键 EventCode 参考
| EventCode | 描述 | 重要性说明 |
|---|---|---|
| 4624 | 成功登录 | 基线 — 检查异常时间/来源 |
| 4625 | 失败登录 | 暴力破解指标 |
| 4634 | 注销 | 会话持续时间 |
| 4648 | 显式凭据登录 | 横向移动 |
| 4672 | 分配特殊权限 | 权限提升 |
| 4688 | 进程创建 | 恶意软件执行 |
| 4698 | 创建计划任务 | 持久化 |
| 4720 | 创建用户账户 | 后门账户 |
| 4728 | 添加到安全组 | 权限提升 |
| 4740 | 账户被锁定 | 暴力破解 |
| 7045 | 服务已安装 | 恶意软件持久化 |
| 1 | Sysmon: 进程创建 | 详细的进程跟踪 |
| 3 | Sysmon: 网络连接 | 出站连接跟踪 |
| 10 | Sysmon: 进程已访问 | LSASS 转储检测 |
| 11 | Sysmon: 文件创建 | 植入器/勒索软件检测 |
| 17 | Sysmon: 管道创建 | Cobalt Strike 检测 |
## SPL vs KQL — 快速对比
| 概念 | SPL | KQL |
|---|---|---|
| 过滤 | `search EventCode=4625` | `where EventID == 4625` |
| 选择列 | `table _time, user` | `project TimeGenerated, Account` |
| 按字段计数 | `stats count by user` | `summarize count() by Account` |
| 重命名字段 | `rename src_ip as SourceIP` | `extend SourceIP = SrcIpAddr` |
| 条件判断 | `eval x=if(count>10,"High","Low")` | `extend x = iif(count>10,"High","Low")` |
| 正则表达式提取 | `rex field=_raw "src=(?P...)"` | `parse kind=regex ...` |
| 时间分桶 | `bucket span=1h _time` | `bin TimeGenerated span=1h` |
| 关联 | `join src_ip [search ...]` | `join kind=inner ... on field` |
## 学习资源
- 📖 [Splunk 官方文档](https://docs.splunk.com/Documentation/Splunk)
- 🎓 [Splunk 免费培训 — 基础知识 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 专属学习房间](https://tryhackme.com)
- 🔍 [Splunk Security Essentials 应用](https://splunkbase.splunk.com/app/3435)
- 📚 [MITRE ATT&CK 框架](https://attack.mitre.org)
- 🛡️ [Splunk ES (Enterprise Security) 官方文档](https://docs.splunk.com/Documentation/ES)
## 关于
作为 SOC 分析师学习作品集的一部分构建。
**作者：** Sreejith Reji | 网络安全理学硕士 | CEH | Security+
属于 [cybersecurity-portfolio](https://github.com/SreejithReji/cybersecurity-portfolio) 合集的一部分。
标签：IP 地址批量处理, PE 加载器, SOC分析, SPL, 安全运营, 扫描框架