Splunk Search Processing Language (SPL) Cheatsheet

A practical reference for common SPL queries, commands, and operators.

Basic Event Counting

Count All Events in an Index

index=VPN_Logs
| stats count

Count Events for a Specific User

index=VPN_Logs
| spath
| search UserName="User_A"
| stats count

Count Events from a Specific IP Address

index=VPN_Logs
| spath
| search Source_ip="107.3.x.x"
| stats count

Count Events Excluding a Specific Country

index=VPN_Logs
| spath
| search Source_Country!="Country_A"
| stats count

Retrieve User Associated with an IP

index=VPN_Logs
| spath
| search Source_ip="107.14.x.x"
| stats values(UserName) as UserName, count

Index and Field Searching

Search by Index Name

index="Windowslogs"

Free Text Search

index=main UserName_A

Search for a Phrase (Exact Match)

index=windowslogs "failed login"

Search for Multiple Keywords (Any Order)

index=windowslogs failed login

Search with Logical Operators

index=windowslogs "TO BE OR NOT TO BE"

Use quotes when searching for reserved words like NOT, OR, and AND.

Search Operators

Operator Precedence Note

OR is evaluated before AND by default.

# This is evaluated as: alice AND (bob OR charlie)
index=windowslogs alice AND bob OR charlie

# Use parentheses to override precedence
index=windowslogs (alice AND bob) OR charlie

Useful Filtering Commands

# Matches any word ending with "manager"
... | regex _raw="manager$"

Table Command

The table command displays only selected fields in a clean, readable format. Ideal for building timelines.

index=windowslogs
| table _time, EventID, Hostname, SourceName

Related Commandshead

• `head` — Show the first N results
• `tail` — Show the last N results
• `reverse` — Reverse the result order
• `sort` — Sort results by one or more fields

Get Zein Al-Ali’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Top Values and Frequency

Show Most Frequent Values

index=windowslogs
| top limit=20 SourceAddress

Show Rarest Values

index=windowslogs
| rare User limit=5

Subsearches and Joins

Use join to correlate events across different data sources using a common field (e.g., LogonId).

index=windowslogs EventID=1
| join LogonId
    [ search index=windowslogs EventID=4624
    | rename TargetLogonId as LogonId
    | fields LogonId, LogonType, IpAddress ]
| table _time, Image, User, LogonType, IpAddress

Statistical and Transformational Commands

Timechart Example

index=windowslogs Image!=""
| timechart span=30m count by Image limit=5

Data Enrichment and Field Manipulation

IP Geolocation

Enrich events with geographic data based on IP addresses.

index=windowslogs
| iplocation SourceIp
| stats count by Country

Lookup Tables

Enrich events using external CSV files or lookup tables.

index=windowslogs
| lookup user_roles Hostname OUTPUT UserRole
| stats count by Hostname, UserRole

Eval and Case Statements

Create new fields or modify existing ones with conditional logic.

index=windowslogs
| eval LogonTypeDesc = case(
    LogonType == 3, "Network Logon",
    LogonType == 5, "Service"
  )
| stats count by LogonType, LogonTypeDesc

Advanced Use Cases

Detect Unusual Country Login Frequency

Identify users logging in from a country that represents less than 10% of their total logins.

index=vpnlogs
| eventstats count as logins_by_user by user
| eventstats count as logins_by_user_country by user, src_country
| eval country_freq = logins_by_user_country / logins_by_user
| where country_freq < 0.1
| table _time, user, src_ip, src_country, country_freq

Detect Anomalous Login Hours (Z-Score)

Identify users logging in at unusual times based on their historical patterns.

index=vpnlogs
| eval hour = tonumber(strftime(_time, "%H")) + tonumber(strftime(_time, "%M")) / 60
| eventstats avg(hour) as typical_hour, stdev(hour) as stdev_hour by user
| eval zscore = abs(hour - typical_hour) / stdev_hour
| where zscore > 3
| eval hour = round(hour, 2),
       typical_hour = round(typical_hour, 2),
       stdev_hour = round(stdev_hour, 2),
       zscore = round(zscore, 2)
| table _time, user, src_ip, src_country, hour, typical_hour, stdev_hour, zscore
| sort - zscore

Quick Reference Notes

• spath is used to extract fields from structured data formats (JSON/XML).
• eventstats calculates statistics without aggregating or removing events from the result set.
• eval supports mathematical operations, string functions, and conditional logic.
• Always use fields or table at the end of complex queries to improve readability.

Practice Exercises:

1. Find all failed login attempts (EventCode 4625) in last 24 hours
2. Identify top 5 users with most failed logins
3. Detect brute force: >10 failed logins from same IP in 5 min window
   tell me about the most queries did you find it useful :)

Connect with Me

If you found this cheatsheet useful, feel free to connect with me:

• X (Twitter): Zein Al-Ali
• GitHub: Zein Al-Ali
• email:zeinalali961@gmail.com

I’d love to hear your feedback, suggestions, or ideas for future articles!