CyberKit

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.

SPL cheat sheet
SyntaxMeaning
search terms | command | commandA 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=valueA field-value pair. Splunk applies it at search time against extracted fields; values containing spaces or special characters need quoting.
AND / OR / NOTBoolean 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.
| makeresultsGenerates empty search results with no index behind them. The fastest way to test an eval expression or a regex without touching real data.
| head NReturns 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 fieldRemoves 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 fieldKeeps 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 yTime 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 ... @w6Snaps 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=5mBuckets 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+3hAligns the bucket boundaries to a specific time rather than to the search window, for lining up buckets with a business day.
| fields field, fieldKeeps only the named fields (a leading `+` is the default). Doing this early cuts the volume every downstream command has to carry.
| fields - fieldRemoves the named fields. Internal fields such as `_raw` and `_time` have to be removed explicitly by name.
| table field, fieldBuilds a table of the named fields in the order given. Where `fields` prunes, `table` also fixes column order for a report.
| rename old AS newRenames 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 -fieldSorts 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 -_timeThe idiomatic "give me the single most recent event": a count in front of the sort clause caps the output.
| mvexpand fieldExpands a multivalue field into one event per value. `limit=<int>` caps how many values are expanded per input event.
| fieldformat field=exprChanges how a field DISPLAYS without changing its underlying value, so a formatted epoch still sorts and compares numerically.
| stats <func>(field) BY fieldThe 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 / medianThe 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 fieldComputes 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=NLimits 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 fieldThe 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 fieldTop 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 fieldSplits 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 fieldTabular 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=NDiscretises 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 left=L right=R where L.a=R.bJoins on fields with different names on each side, using aliases. `max=0` lifts the one-matching-row-per-key default.
| 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=sedSed-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{}.fieldWalks 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 ... | timechartEmits 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=bAccepts several indexes and wildcards at once: a quick inventory of which hosts are actually reporting.

Related

All references