SPL Cheat Sheet
Splunk SPL: search commands, eval functions, stats, and field extraction.
SPL is a pipeline. A base search selects events and each command after a pipe reshapes what the one before produced.
Before you read the table
- Name an index and sourcetype in the base search. It is the biggest performance lever you have.
- Filter before you transform. Every command works on every row you hand it.
- where reads an unquoted value as a field name. Forget the quotes and it silently matches nothing.
100 rows.
| Syntax | Meaning |
|---|---|
| search terms | command | command | A search is a set of terms followed by commands chained with the pipe character. `search` is implicit at the start of any pipeline that does not begin with another generating command, so you rarely type it first. |
| index= / sourcetype= / host= / source= | The default fields that scope a search. Naming an index and a sourcetype up front is the single biggest thing you can do for search performance. |
| field=value | A field-value pair. Splunk applies it at search time against extracted fields; values containing spaces or special characters need quoting. |
| AND / OR / NOT | Boolean operators. `AND` is implied between adjacent terms. Evaluation order is: parentheses first, then NOT, then OR, then AND, so parenthesise anything you are not certain about. |
| * | Wildcard. Works inside a field value as well as at the end of it, but a leading wildcard forces a full scan, so avoid it on a wide search. |
| field IN (a, b, c) | Matches any value in a list, and the list entries accept wildcards. Much more readable than a chain of ORs. |
| [ search ... ] | A subsearch. It runs first and its results are passed into the outer search, so keep it small and bounded, because subsearch results are capped and time out. |
| | makeresults | Generates empty search results with no index behind them. The fastest way to test an eval expression or a regex without touching real data. |
| | head N | Returns the first N results. `tail` returns the last N and `reverse` flips the order. The three of them are how you cheaply preview a pipeline mid-build. |
| | where <eval-expression> | Arbitrary filtering with an eval expression that must return true or false. The key difference from `search`: `where` treats an unquoted value as a FIELD NAME, so it can compare two fields against each other. |
| = != < <= > >= | Comparison operators, valid in both `search` and `where`. In `where`, quote a literal string or it will be read as a field name. |
| like(field, "pattern") | SQL-style pattern match inside `where`. `%` matches any sequence, `_` matches a single character. |
| cidrmatch("CIDR", field) | True when an IP field falls inside a CIDR block. The correct way to scope by subnet, string prefix matching on IPs is a well-known way to get this wrong. |
| | regex field="pattern" | REMOVES results whose field does not match a PCRE pattern (`!=` inverts it). Distinct from `rex`, which extracts or replaces rather than filters. |
| | dedup field | Removes results matching criteria you have already seen. Add an integer to keep N of each, `consecutive=true` to drop only adjacent repeats, and `sortby` to control which copy survives. |
| | dedup N field field | Keeps the first N results for each combination of the named fields, useful for sampling a few events per host rather than one. |
| | fillnull value=<string> | Replaces null values with a value (default 0), across all fields or just the ones you name. Run it before a `stats` that would otherwise silently drop rows. |
| earliest= / latest= | Scope the search window inline instead of using the time picker. Format: `[+|-]<integer><unit>@<snap-unit>`. Both accept relative and absolute forms. |
| @ (snap to) | Rounds DOWN to the nearest unit. `-1d@d` means the start of yesterday, not this time yesterday. This is what makes a scheduled search cover a whole clean day. |
| s m h d w mon q y | Time units, each with long forms: `s|sec|secs|second|seconds`, `m|min|mins|minute|minutes`, `h|hr|hrs|hour|hours`, `d|day|days`, `w|week|weeks`, `mon|month|months`, `q|qtr|qtrs|quarter|quarters`, `y|yr|yrs|year|years`. Subsecond units are `us`, `ms`, `cs`, `ds`. |
| @w0 ... @w6 | Snaps to a specific day of the week, where `w0` is Sunday. The building block for "the last full business week". |
| earliest="MM/DD/YYYY:HH:MM:SS" | Absolute timestamps for a fixed incident window that must not move as the search is re-run. |
| earliest=1 latest=now() | Searches over all time. Use it deliberately and rarely; on a real index it is the most expensive thing you can type. |
| (earliest=... latest=...) OR (...) | Several disjoint windows in one search, for correlating two separate bursts of activity without running two searches. |
| | bin _time span=5m | Buckets a continuous value into discrete sets. On `_time` this is how you build a timeline without `timechart`. `bucket` is an alias for the same command. |
| | bin _time aligntime=@d+3h | Aligns the bucket boundaries to a specific time rather than to the search window, for lining up buckets with a business day. |
| | fields field, field | Keeps only the named fields (a leading `+` is the default). Doing this early cuts the volume every downstream command has to carry. |
| | fields - field | Removes the named fields. Internal fields such as `_raw` and `_time` have to be removed explicitly by name. |
| | table field, field | Builds a table of the named fields in the order given. Where `fields` prunes, `table` also fixes column order for a report. |
| | rename old AS new | Renames a field. Wildcards work on both sides; quote any name containing a space. |
| | rename prefix* AS prefix* | Bulk-renames a family of fields by pattern, for normalizing a vendor prefix across a whole sourcetype. |
| | sort -field | Sorts by one or more fields; `-` is descending, `+` ascending. Defaults to 10,000 results. Pass `0` to sort everything. |
| | sort num(field) / str() / ip() | Forces a sort type instead of letting Splunk guess. `ip()` in particular sorts addresses numerically rather than as strings, which is almost always what you want. |
| | sort 1 -_time | The idiomatic "give me the single most recent event": a count in front of the sort clause caps the output. |
| | mvexpand field | Expands a multivalue field into one event per value. `limit=<int>` caps how many values are expanded per input event. |
| | fieldformat field=expr | Changes how a field DISPLAYS without changing its underlying value, so a formatted epoch still sorts and compares numerically. |
| | stats <func>(field) BY field | The workhorse. Computes aggregates, optionally grouped by one or more fields, and discards everything that is not part of the result. |
| count / count(field) | Bare `count` counts events; `count(field)` counts events where that field is present. The difference matters on sparse sourcetypes. |
| count(eval(<condition>)) | Conditional counting: the idiom for putting success and failure counts side by side in a single pass instead of running two searches. |
| dc(field) / distinct_count(field) | Distinct count. `estdc()` is the cheaper estimated version, with `estdc_error()` reporting its error bound. |
| values(field) / list(field) | Multivalue aggregates: `values()` returns the DISTINCT values in a group, `list()` returns all of them in order. The way to collapse "everything this host did" into one row. |
| earliest(field) / latest(field) | Returns the field value from the chronologically first / last event in the group. `earliest_time()` and `latest_time()` return the timestamps themselves. |
| first(field) / last(field) | Returns the field value from the first / last event in RESULT order, not time order. Use `earliest()`/`latest()` when you actually mean chronology. |
| sum / avg / min / max / median | The standard numeric aggregates. `range()`, `mode()`, `stdev()`, `stdevp()`, `var()`, `varp()` and `sumsq()` round out the set. |
| perc<num>(field) | Percentile estimate: `perc95(duration)`. `exactperc<num>()` is the exact (and far more expensive) form; `upperperc<num>()` gives the upper bound. |
| | stats <func>(*wildcard*) | Aggregate every field matching a wildcard in one clause instead of naming them individually. |
| | eventstats <func> BY field | Computes the same aggregates as `stats` but ATTACHES them to every original event instead of collapsing them. This is how you compare each event to its own group average. |
| | streamstats <func> | Running aggregates, computed in event order. `current=f` excludes the current event, which is what you want when comparing an event to what came before it. |
| | streamstats window=N | Limits the running aggregate to a sliding window of N events. `time_window=5m` does the same by time, and requires the events to be sorted by time. |
| | top limit=N field | The most common values of a field, with count and percent. `rare` is the mirror image and takes the same arguments, and on a DFIR hunt the rare values are usually the interesting ones. |
| | top field BY field | Top values computed per group rather than globally. "the most common action for each referring domain". |
| | top N field showperc=f countfield=<name> | Drops the percent column and renames the count column: the tidy form for a report table. |
| | timechart span=<span> <func> | Builds a time series and its statistics table in one step. `span` sets the bucket size; if you give both `bins` and `span`, span wins. |
| | timechart ... BY field | Splits the series by a field, producing one line per distinct value. Beware of high-cardinality split fields. You will get a column per value. |
| | chart <func> OVER field | Tabular output for charting where the x-axis is a field rather than time. `OVER` sets the rows; a following `BY` splits them into columns. |
| | chart ... BY field span=<span> | Bins a numeric split field while charting. `span=log2` gives logarithmic buckets, which is the right shape for durations and sizes. |
| | bin field bins=N | Discretises a numeric field into a fixed number of buckets (default 100). `start=` and `end=` clamp the range. |
| | transaction <field-list> | Groups events into transactions by shared field values. It adds `duration`, `eventcount` and `closed_txn` fields to each transaction. |
| startswith= / endswith= | Anchors a transaction to the events that open and close it. This is how you turn a stream of web events into per-session stories. |
| maxspan= / maxpause= / maxevents= | Bounds a transaction: total elapsed time, maximum gap between consecutive events, and maximum member count (default 1000). Without bounds a transaction can swallow a whole index. |
| | join field [search ...] | SQL-style join between the main pipeline and a subsearch. Default `type=inner`; `type=outer` (equivalently `type=left`) keeps unmatched left rows. |
| join left=L right=R where L.a=R.b | Joins on fields with different names on each side, using aliases. `max=0` lifts the one-matching-row-per-key default. |
| | append [search ...] | Appends subsearch results as extra ROWS. `appendcols` appends them as extra COLUMNS, pairing first result with first result. |
| | lookup <table> <field> OUTPUT <field> | Enriches events from a configured lookup table. `OUTPUTNEW` only writes fields that are not already present, which is the safer default when enriching. |
| | inputlookup <table> | Reads a lookup table as its own result set: the way to inspect a threat-intel list, or to use one as the seed of a subsearch. `outputlookup` writes back. |
| | inputlookup <table> WHERE ... | Filters a lookup while reading it, instead of pulling the whole table and filtering after. |
| | iplocation <ip-field> | Adds City, Country, Region, lat and lon from an IP field. `allfields=true` adds Continent, MetroCode and Timezone; `prefix=` namespaces the new fields. |
| | rex "(?<name>pattern)" | Extracts fields at search time using a PCRE pattern with named capture groups. Defaults to matching against `_raw`. |
| rex field=<field> | Runs the extraction against a specific field instead of the whole raw event. Faster, and avoids collisions with unrelated text. |
| max_match=<int> | Number of matches to extract (default 1). Set it to 0 for unlimited, which produces a multivalue field. |
| rex mode=sed | Sed-style substitution instead of extraction: the standard way to mask a card number, token or hostname in output you are about to share. |
| offset_field=<string> | Writes the match position into a field (as `start-end`): useful when you need to prove where in the event a match landed. |
| | spath path=<datapath> | Extracts fields from structured JSON or XML. Steps are separated by periods; array indices go in curly brackets, and `{}` means "every element". |
| spath input=<field> | Parses structured data out of a named field rather than `_raw`: for a JSON blob nested inside a syslog line. |
| spath path=arr{}.field | Walks into every element of a JSON array, producing a multivalue field you can then `mvexpand`. |
| | eval field=<expression> | Calculates an expression and writes it into a field. Comma-separate several assignments in one `eval`; later ones can use earlier ones. |
| + - * / % . | Arithmetic operators plus `.` for concatenation. Boolean operators are `AND OR NOT XOR`, and `=` and `==` are interchangeable in eval. |
| if(predicate, true, false) | Two-branch conditional. |
| case(cond, val, cond, val, ...) | Returns the value for the first condition that is TRUE. There is no implicit else. Add `1=1` as the final condition if you want one. |
| coalesce(a, b, ...) | Returns the first non-null argument, for collapsing two sourcetypes that name the same idea differently. |
| match(str, regex) | True when the string matches the regex. `searchmatch()` tests a whole search string against the event instead. |
| replace(str, regex, replacement) | Regex substitution inside an eval expression. |
| lower() / upper() / len() | Case normalization and character count. Normalize before a `stats ... BY` or two spellings become two groups. |
| substr(str, start, len) | Substring extraction. `ltrim()`, `rtrim()` and `trim()` strip characters from the edges. |
| split(str, delim) | Splits a string into a multivalue field. `mvjoin()` puts one back together; `mvcount()`, `mvindex()`, `mvfilter()` and `mvdedup()` work on the result. |
| strftime(epoch, format) | Formats an epoch time as a readable string. `strptime()` is the inverse and is how you turn a log-embedded timestamp into a real time field. |
| relative_time(time, specifier) | Applies a relative-time specifier (the same `-1d@d` grammar as `earliest`) to a time value inside an expression. |
| now() / time() | `now()` is the search start time and is constant for the whole search; `time()` is the wall-clock time when the eval ran. |
| tostring(value, format) | Converts to a string, optionally with a format such as "commas", "hex" or "duration". `tonumber()` goes the other way. |
| md5() / sha1() / sha256() | Computes a hash of a string inside a search. Useful for building a stable correlation key, not for re-deriving a file hash from telemetry. |
| isnull() / isnotnull() | Null tests. `typeof()`, `isnum()`, `isstr()`, `isint()` and `ismv()` cover the rest of the type checks. |
| ipmask(mask, ip) | Applies a bitwise mask to an IPv4 address: the way to group traffic by network rather than by host. |
| json_extract(json, path) | Pulls values out of a JSON string by path inside an eval. `json_keys()`, `json_valid()` and `json_array_to_mv()` complete the set. |
| | tstats <func> WHERE index=... | Runs statistics directly against the indexed `tsidx` files rather than raw events. Orders of magnitude faster than `stats`, at the cost of only seeing indexed fields. |
| tstats ... BY field span=<span> | Groups an accelerated search, optionally by time bucket. This is the fast way to build a first-pass timeline over a wide window. |
| tstats FROM datamodel=<name> | Runs against an accelerated data model instead of an index. `summariesonly=t` restricts it to summarized data only, which keeps it genuinely fast. |
| tstats ... where nodename=<dataset> | Selects a child dataset inside a data model, rather than the whole root object. |
| tstats prestats=t ... | timechart | Emits intermediate statistics so a downstream reporting command can finish the job: the accelerated route to a chart. |
| | metadata type=sourcetypes index=... | Lists the hosts, sources or sourcetypes present in an index, with first/last/recent event times. The first thing to run against an unfamiliar environment. |
| metadata type=hosts index=a index=b | Accepts several indexes and wildcards at once: a quick inventory of which hosts are actually reporting. |