Home / Blog / SIEM and Tools
SPL First Searches: stats, sort, where, table and rename
A beginner guide to five Splunk SPL commands, stats, sort, where, table and rename, with short searches explained line by line and what to check before you escalate.
Most useful Splunk searches follow one pattern: find the events, count them, put the biggest first and show only the columns you need. This guide teaches the five SPL (Search Processing Language) commands behind that pattern: stats, sort, where, table and rename.
Scenario: An alert says the public web server had a burst of "page not found" errors this afternoon. You open Splunk and find thousands of events. Reading them one by one would take hours. Which IP addresses caused the most errors, and are there only a few of them or hundreds?
How a search reads from left to right
An SPL search starts with plain filters, such as an index, a sourcetype and a field value. Then you add a pipe symbol (|) and a command. The pipe passes the results of the left side into the command on the right, like items moving along a conveyor belt. Each command changes the results a little, and the next one works on what is left.
The previous article in this series covered index, host, sourcetype and time range. Here is a search that uses all four ideas. It uses placeholder names, so swap in the ones your organisation uses.
index=web sourcetype=access_combined status=404 earliest=-4h latest=now
This finds web access log events in the web index where the HTTP (Hypertext Transfer Protocol) status is 404, which means page not found, over the last four hours. The result is a list of events. That is still too long to read, so the commands below turn it into something short.
Count events with stats
The stats command calculates summary numbers over your results, such as a count, a sum or an average. It is a transforming command, which means it turns a list of events into a small table of numbers. Start with the simplest version.
index=web sourcetype=access_combined status=404 earliest=-4h latest=now
| stats count
Line 1 finds the events. Line 2 counts them and returns one number. Now add a by clause, which splits the count into groups.
index=web sourcetype=access_combined status=404 earliest=-4h latest=now
| stats count by clientip
Line 2 now counts events for each value of clientip, which is the field that holds the visitor's IP address in this web log format. You get one row per address. The output looks like this, using documentation example addresses:
| clientip | count |
|---|---|
| 198.51.100.7 | 14 |
| 203.0.113.50 | 2318 |
| 192.0.2.15 | 9 |
Watch out: after stats runs, the original events are gone. Only the fields you named in the command remain, here clientip and count. If you try to filter on status or uri_path afterwards, there is nothing left to filter. Do that filtering before the pipe.
Put the biggest first with sort
The table above is not in a useful order. The sort command fixes that.
index=web sourcetype=access_combined status=404 earliest=-4h latest=now
| stats count by clientip
| sort -count
Line 3 sorts by the count field. The minus sign means descending, so the largest number comes first. Without a sign, the default is ascending. If you want only the top ten, put a number straight after the word sort, as in sort 10 -count. Splunk's documentation says that when you give no number, sort returns at most 10000 results, and a number of 0 returns everything, which can slow a search down.
Tip: sort works best near the end of a search, once stats has already shrunk the data to a few rows.
Filter counts with where
You have two ways to filter, and they work at different moments.
index=web sourcetype=access_combined status=404 earliest=-4h latest=now
| stats count by clientip
| sort -count
| where count > 50
Line 4 keeps only the rows where the count is greater than 50. The number 50 is only an example. A sensible threshold depends on how busy your site is, so look at a normal day first.
Two rules about where are worth learning early. First, it uses the same expression syntax as the eval command, and that syntax is case-sensitive. Second, text values go in double quotes, for example where clientip="203.0.113.50". A word without quotes is treated as a field name, not as text. Also, where does not accept the star wildcard the way a plain search term does. Splunk's documentation says to use the like function with a percent sign instead, for example where like(clientip, "203.0.113.%").
Tidy the output with table and rename
The table command shows only the fields you list, in the order you list them. The rename command gives a field a new name, using the word as. Together they make results easy to paste into a ticket.
index=web sourcetype=access_combined status=404 earliest=-4h latest=now
| stats count by clientip
| sort -count
| where count > 50
| rename clientip as source_ip, count as not_found_errors
| table source_ip not_found_errors
Line 5 renames both columns so a colleague understands them without opening the search. Separate several renames with commas. If a new name contains spaces, wrap it in double quotes. Line 6 lists the columns in the order you want to read them. Here it changes nothing, but on real data table is how you drop noisy fields.
A small habit saves trouble: use rename last. If you rename count early, a later where count line will stop working, because the field no longer has that name.
A four step routine for every first search
- L0FilterName the index, sourcetype, field values and time range before the first pipe.search
- L1CountUse stats count by one field to turn events into a short table.stats
- L2Rank and trimSort the largest first, then keep rows above a sensible threshold.sort, where
- L3PresentRename the columns and show only the ones a reader needs.rename, table
Reading the result like an analyst
Back to the scenario. Suppose the final table shows one address, 203.0.113.50, with 2318 errors and every other address under 20. That tells you the burst came from one source, not from hundreds of visitors. It does not tell you what that source was doing, so treat the number as a question, not a verdict.
Several harmless things look the same in a count. A search engine crawler can request pages that no longer exist. A broken link on your own website can send every visitor to a missing page. An approved vulnerability scanner will also produce many 404 errors. Before you escalate, check these:
- Which uri_path values were requested? A list of random admin and backup file names looks different from one missing image.
- What did the same address do just before and after, and did any request succeed with status 200?
- Is the address on your list of approved scanners or known crawlers?
- Does the timing look steady and automated, or like a person clicking?
- Did the count change when you widened the time range, or was it only this afternoon?
Note: field names depend on how your data is parsed. The names clientip and status are standard for the access_combined web format, but your firewalls, proxies and Windows logs may use different ones. Open one event and read its field list before you copy any search.
What to study next
This article supports the CySA+ and Security+ skill of reading and searching logs in a SIEM (Security Information and Event Management) tool. I did not confirm that any named product appears in the exam objectives, so read the current CompTIA objectives on their website, since exam versions change.
- Splunk's own Search Tutorial on docs.splunk.com, which walks through these same commands with sample data.
- The head and dedup commands, which return the first few rows and remove duplicates.
- Using stats with other functions, such as dc for distinct count and sum for totals.
- Practice: load a sample web log into a lab Splunk instance, then run each search above and change one thing at a time.
Key takeaways
- The pipe passes results from left to right, and each command works on what the one before it returned.
- stats count by a field turns thousands of events into one short row per value.
- sort -count puts the biggest first, and sort returns at most 10000 results unless you say otherwise.
- Filter events before the pipe, and use where to filter the totals after stats.
- Use rename and table last, so the output is clear for the person reading your ticket.
These five commands cover a large share of everyday Splunk work. Run the searches above on a lab system today, change the threshold and the field, and notice how the table changes each time.
Written by Ben, Cybersecurity Student