Splunk SPL for Security: 5 Queries Every SOC Analyst Needs

Splunk is one of the most common SIEM tools in real SOCs, and its search language, SPL, is a skill that shows up in job descriptions again and again. The good news is that you only need about a dozen commands to answer most security questions. This guide teaches those commands through practical queries for failed logons, new accounts, suspicious processes and rare behaviour.

This is Part 13 of 30 in the Blue Team Learning Path. It builds on the Windows events from Part 3 and the Sysmon data from Part 12.

Key takeaways

Key takeaways

• An SPL search is a pipeline: start broad with an index and time range, then pipe results through commands.
• Filter as early as possible. Narrow searches by index, source type and time before using heavy commands.
• stats, eval, where, rex and timechart cover most daily detection work.
• Names like index and EventCode depend on how your data was onboarded, so check your own field names first.

Get Splunk running for practice

You can practise with a free copy on your lab SIEM machine from Part 10, or on a spare laptop. According to Splunk’s documentation on Splunk Free, the free licence allows 500 MB of indexing per day and does not expire, but it has no user authentication and no alerting. That is fine for learning, but not for a real SOC. Licence terms change, so confirm the current limits on Splunk’s site before you install.

Then load some data. The simplest options are to upload an exported log file in the web interface, or install the universal forwarder on your Windows lab machine and point it at the Windows Security and Sysmon logs.

The anatomy of a search

SPL reads left to right, like a shell pipeline. The first part selects events. Each pipe symbol sends the result to a command that transforms it.

index=wineventlog EventCode=4625 earliest=-24h
| stats count by Account_Name, src_ip
| sort - count
| head 10

This reads: find failed logons (event 4625) in the last 24 hours, count them per account and source, sort the biggest first, and keep ten. The field names above are common in the Windows add-on, but yours may differ. Always click a sample event and read the field list on the left before writing a query. The Splunk Search Reference documents every command.

The commands you will use daily

• search and where: keep events that match a condition. Use search for simple keyword matches and where for expressions and comparisons.
• stats: count, sum or list values grouped by a field. This is the workhorse of detection.
• eval: create or change a field, for example convert a time or label a result.
• rex: extract a value from raw text with a regular expression.
• table: choose which columns to show.
• sort and head: order the results and limit how many you keep.
• dedup: keep one event per unique value.
• top and rare: list the most or least common values of a field.
• timechart: count events over time as a chart.

Query 1: brute force detection

A burst of failed logons followed by a success is the pattern behind password guessing, technique T1110 on the ATT&CK site. First find sources with many failures:

index=wineventlog EventCode=4625 earliest=-1h
| stats count dc(Account_Name) as accounts by src_ip
| where count > 20
| sort - count

The dc function means distinct count. A source that failed against many accounts with few attempts each looks like password spraying, while one account with many failures looks like guessing. Event 4625 is described on Microsoft’s reference page. To find a possible successful compromise, search the same sources for event 4624 afterwards.

Query 2: new local accounts

Creating a user account is a common persistence step (T1136.001). Windows records it as event 4720, and adding that account to the administrators group is event 4732.

index=wineventlog (EventCode=4720 OR EventCode=4732) earliest=-7d
| table _time, host, EventCode, Account_Name, Subject_Account_Name

Review who created the account, from which machine and at what time. Out-of-hours creations by non-administrators are worth an immediate look.

Query 3: suspicious process creation with Sysmon

With Sysmon data you can search the command line directly. This looks for encoded PowerShell started by an Office program:

index=sysmon EventCode=1 earliest=-24h
  (ParentImage="*\\winword.exe" OR ParentImage="*\\excel.exe")
  (Image="*\\powershell.exe" OR Image="*\\cmd.exe")
| table _time, host, User, ParentImage, Image, CommandLine

This detects behaviour rather than a file, which is the high-value end of the Pyramid of Pain. Field names such as ParentImage and Image come from your Sysmon add-on, so verify them in your own data.

Query 4: rare processes with rare and stats

Attackers often run programs that no other machine has. Count how many hosts ran each process:

index=sysmon EventCode=1 earliest=-7d
| stats dc(host) as hosts, count by Image
| where hosts=1
| sort count

Programs that ran on exactly one machine are not malicious by default, but they make a short, useful review list. Hunting by rarity is a basic technique we expand in Part 21.

Query 5: logons over time

A chart often shows what a table hides. This plots successful logons per hour by logon type, so a night-time spike stands out:

index=wineventlog EventCode=4624 earliest=-3d
| timechart span=1h count by Logon_Type

Extract fields with rex

When a field was not extracted automatically, pull it out with a regular expression. For example, to read the user name from a Linux SSH failure in a raw log line:

index=linux "Failed password" earliest=-24h
| rex "Failed password for (invalid user )?(?<user>\S+) from (?<src>\S+)"
| stats count by user, src
| sort - count

This connects to Part 5, where we did the same job with grep and awk. The idea is identical, only the tool changes.

Turn searches into alerts and dashboards

A query you run by hand is a hunt. A query that runs on a schedule and notifies someone is a detection. In a full Splunk Enterprise setup you can save a search as an alert with a schedule, a time window and a trigger condition, such as “more than zero results”. Keep the logic simple, put a clear description in the alert, and include the ATT&CK technique so analysts know what it means. Remember that Splunk Free has no alerting, so for practice on the free version, run the saved search on demand and judge it by its results.

Performance habits that save time

1. Always set a time range. Searching all time is slow and rarely needed.
2. Name the index and source type in the first line.
3. Filter before you aggregate. Put search or field conditions before stats.
4. Use table late, only to format the final output.
5. Start small. Test a query on one hour, then widen.

Reading results critically

A query returning results does not mean an attack happened, and a query returning nothing does not mean you are safe. After each search, ask three questions. First, is the data complete: did the log source send events for the whole time range, and are the field names the ones I used? Second, is the result explainable: could a scheduled job, a vulnerability scanner or an administrator task produce exactly this? Third, what is the next pivot: which host, account or address should I search next to confirm or rule out the finding? Writing the answers in the ticket, even in one line each, makes your investigations repeatable and gives the next analyst a head start. It also shows managers that your detections are tested, not guessed.

Lookups and context: make results meaningful

A raw list of addresses and account names is hard to judge. Splunk lets you enrich results with a lookup, which is a small table you upload, such as a list of known administrators, server names or approved scanners. You then compare each event against the table. For example, a new local account created by someone who is not on the administrators list is much more interesting than one created by a known admin. Start with the simplest table you can build by hand, and grow it as you learn what your environment normally does.

Practice plan for this week

1. Day 1: load a Windows Security log export and run the failed-logon query. Change the threshold and see how results change.
2. Day 2: generate a few failed and successful logons in your lab, then find them with timechart.
3. Day 3: create a test user on the lab machine and find events 4720 and 4732.
4. Day 4: load Sysmon data and write the Office-to-shell query. Trigger it safely by opening a harmless macro-free document and a command prompt from a script.
5. Day 5: write down which ATT&CK technique each query covers, using Part 6 as a guide, and note what you would need to log to cover a technique you currently miss.

After a week you will have five working searches, each tagged to a technique and tested on real events. That is a much better starting point than a long list of commands you have only read about.

Common pitfalls

• Copying queries without checking field names. A typo or different add-on name returns nothing, and you assume nothing happened.
• Forgetting time zones. Events and your clock may disagree.
• Counting events instead of distinct values. Use dc when you care about spread.
• No baseline. A threshold like twenty failures means nothing until you know your normal.
• Silent failures. If a data source stops forwarding, your alert stays quiet. Monitor ingestion too.

Frequently asked questions