Home / Blog / SIEM and Tools

Beginner 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.

TopicSPL first searches
You will learnstats count by, sort, where, table, rename
DifficultyBeginner, builds on index and time range basics
>

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.

SplunkSPL
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.

SplunkSPL
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.

SplunkSPL
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:

clientipcount
198.51.100.714
203.0.113.502318
192.0.2.159
!

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.

SplunkSPL
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.

Before the pipePlain terms like status=404 choose which events enter the search. Use them first.
After statsThe where command filters the summary rows, for example counts above a number.
Why bothEarly filters keep the search fast. Where answers questions about the totals.
SplunkSPL
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.

SplunkSPL
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

  1. L0
    FilterName the index, sourcetype, field values and time range before the first pipe.
    search
  2. L1
    CountUse stats count by one field to turn events into a short table.
    stats
  3. L2
    Rank and trimSort the largest first, then keep rows above a sensible threshold.
    sort, where
  4. L3
    PresentRename 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?
i

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

  1. The pipe passes results from left to right, and each command works on what the one before it returned.
  2. stats count by a field turns thousands of events into one short row per value.
  3. sort -count puts the biggest first, and sort returns at most 10000 results unless you say otherwise.
  4. Filter events before the pipe, and use where to filter the totals after stats.
  5. 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

Keep going

More in SIEM and Tools