Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 36 ยท Search Processing Language

The SPL Pipeline: where, table, fields, sort, dedup, head and rename

Learn how the pipe character chains SPL commands and use the everyday shaping commands: where, search, table, fields, rename, sort, dedup, head, tail and reverse.

The Search Processing Language (SPL) is built around the pipe. The first part of a search retrieves events; each | then hands the results to the next command, like a Unix pipeline or an assembly line.

SPL
index=web sourcetype=access_combined status>=500
| table _time, host, uri_path, status
| sort - _time
| head 20

Retrieve, pick columns, sort newest first, keep 20. In this lesson you learn the commands you will type most often.

Types of commands

Type What it does Examples
Generating Starts the pipeline with data search, makeresults, inputlookup, tstats
Streaming Processes each event on its own, can run on indexers in parallel eval, where, fields, rex, rename
Transforming Turns events into a table of statistics stats, chart, timechart, top
Orchestrating/other Needs all results together sort, dedup, transaction

Streaming commands are cheap; transforming commands drop the raw events. After stats, the events are gone and only a table remains, so put filters before it.

Both filter results. After the first pipe:

SPL
... | search status=500 host=web*
... | where status=500 AND like(host, "web%")

search uses the same syntax as the initial search (wildcards, case-insensitive values). where evaluates an expression like eval does: field comparisons are case-sensitive, strings need double quotes, == or = both work, and you have functions such as like(), match(), isnull() and len().

SPL
... | where bytes > 1000000
... | where isnotnull(user) AND len(user) > 3
... | where match(uri_path, "^/api/v[12]/")

table, fields and rename

  • table displays the listed columns in that order (and is a transforming-style display command, so avoid it in the middle of a pipeline you still need events from).
  • fields includes or excludes fields: fields + host, status keeps them; fields - _raw removes the raw text and speeds up the rest of the search because less data moves along.
  • rename old AS new changes a field name; use quotes for names with spaces: rename uri_path AS "Page".
SPL
... | fields host, uri_path, status, bytes
| rename uri_path AS Page, bytes AS "Bytes sent"
| table host, Page, status, "Bytes sent"

sort, head, tail, reverse

SPL
... | sort - bytes            # descending (minus before the field)
... | sort 0 + _time          # ascending; 0 means "no limit"
... | head 10                 # first 10 results
... | head status=200         # leading results while the condition holds
... | tail 5                  # last 5
... | reverse                 # flip the order

Important: sort returns only 10,000 results by default. Use sort 0 ... to sort everything. Also, sorting numbers needs numeric fields; use sort - num(bytes) if the field is text. Events normally arrive newest first, so head gives the latest.

dedup

dedup removes duplicates, keeping the first event for each value (or combination) of the fields:

SPL
... | dedup host                       # one event per host
... | dedup user, uri_path             # one per user+page pair
... | dedup 3 host                     # keep three per host

Because events are newest first, dedup host gives each host's latest event.

Python Stand-in: where, sort, head and dedup over a small result set

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

fillnull, where isnull() and missing fields

Missing fields cause empty cells and silently wrong math. fillnull value=0 bytes fills empty values; where isnotnull(bytes) removes events without it. With no value=, fillnull writes 0.

makeresults: practice without data

makeresults generates events with only a _time, so you can test SPL without any index. You can create sample data with eval:

SPL
| makeresults count=3
| streamstats count as n
| eval host="web0".n, status=if(n=2, 500, 200)
| table host status

(streamstats numbers the rows; eval builds fields.) This pattern is great for experimenting, and it is exactly how this course's stand-ins model data.

Python Stand-in: makeresults, streamstats and eval

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Performance rules of the pipeline

  1. Filter as early as possible (in the first search command, not with where later).
  2. Remove unneeded fields with fields before expensive commands.
  3. Do transforming work (stats) before sort and dedup on large data.
  4. Use head while developing.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the pipe character do in SPL?

    Each command in the pipeline receives the output of the previous one.
  2. 2. How do you sort all results (not just the first 10,000) from newest to oldest by _time?

    sort has a default limit of 10,000; sort 0 removes it, and the minus sign means descending.
  3. 3. Which command keeps the first event for each distinct host?

    dedup keeps the first result for each value of the field, which is the newest when events arrive newest first.
  4. 4. What is the main difference between search and where after a pipe?

    where uses eval-style expressions and functions; search uses search syntax.

Summary

  • A search is a retrieve step followed by commands joined with |.
  • Filter early, then shape with fields, rename, table, sort, head, dedup.
  • where takes expressions; search takes search syntax.
  • sort defaults to 10,000 results; use sort 0.
  • makeresults lets you practice without data.

Next: stats, chart and timechart