More Commands

Commands for reshaping results, running statistics, and working with multivalue fields. The core commands (search, where, fields, eval, stats, timechart, …) are on the SPL page.

bin

Puts times or numbers into buckets, replacing the field (or writing a new one with as). bucket is the same command.

Syntax:

bin [<field>] [span=<span> | bins=<N>] [minspan=<span>] [start=<num>] [end=<num>] [aligntime=<time>] [as <new>]

• The field defaults to _time. A time span is a number and a unit (500ms, 5m, 1h, 1d, 1mon); without one, the span gives at most bins (default 100) buckets over the time range, and no less than minspan. aligntime= moves where buckets start: earliest, latest or a time such as @d+30m; it applies to spans shorter than a day.
• For a number, span=100 gives buckets labelled 0-100, 100-200, …; bins=N picks a power of ten that gives at most N buckets, over at least the range start to end when given, with buckets no smaller than minspan.
• span=log10, span=log2 or span=2log10 make logarithmic buckets: 1-10, 10-100, … (a coefficient multiplies the bounds: 2-20, 20-200, …). Values of 0 or less get no bucket.

Examples:

bin _time span=5m | stats count by _time, level
bin duration span=250 | stats count by duration
bin duration span=log10 | stats count by duration
bin _time span=1h aligntime=@d+30m | stats count by _time

chart

Aggregates like stats, laid out as a table: one row per value of the row field, and, with a column field, one column per value of it.

Syntax:

chart [options] <aggregation> [as <alias>] ... [over <row-field>] [by <column-field>]
chart [options] <aggregation> by <row-field> <column-field>

With a column field, chart takes one aggregation, and keeps the columns with the largest values of it. format=, sep= and agg= are not supported. A cell with no rows is empty; use fillnull to show 0.

Examples:

chart count over host by level
chart avg(duration) over url by method limit=5
chart count by workload level | fillnull value=0
chart dc(pod_name) as pods over namespace

eventstats

Adds aggregates to every row, without combining the rows: each row gets its group's values.

Syntax:

eventstats <aggregation> [as <alias>] ... [by <field> ...]

Examples:

Keep the logs from pods that logged more than 1,000 times:

eventstats count as pod_logs by pod_name
| where pod_logs > 1000

Compare each request with its endpoint's average:

eventstats avg(duration) as endpoint_avg by url
| where duration > 3 * endpoint_avg

streamstats

Adds running aggregates: each row gets the aggregate of the rows before it and itself, in the current order.

Syntax:

streamstats [current=<bool>] [window=<N>] [time_window=<span>] [reset_on_change=<bool>] <aggregation> [as <alias>] ... [by <field> ...]

Log rows are newest first. Sort them oldest first for totals that build up over time.

Examples:

sort _time | streamstats count as nth_log
sort _time | streamstats window=5 avg(duration) as moving_avg by service
streamstats time_window=1m count as last_minute by pod_name
timechart span=1h count | streamstats sum(count) as running_total

fillnull

Replaces empty values.

Syntax:

fillnull [value=<value>] [<field> ...]

The value defaults to 0. Without fields, every column of the result is filled. A named field the rows do not have is added, filled with the value.

Example:

chart count over host by level | fillnull value=0

addtotals / addcoltotals

addtotals adds a column with each row's total; addcoltotals adds a row with each column's total, at the end.

Syntax:

addtotals [fieldname=Total] [col=<bool>] [labelfield=<field>] [label=Total] [<field> ...]
addcoltotals [labelfield=<field>] [label=Total] [<field> ...]

Without fields, the numeric columns are added up: the results of stats, chart, timechart and the like. addtotals col=true also adds the totals row. labelfield names the column that says Total in the totals row.

Examples:

chart count over host by level | addtotals
stats count, sum(bytes) as bytes by service | addcoltotals labelfield=service label=ALL

accum / delta / autoregress

Values from earlier rows, in the current order.

Syntax:

accum <field> [as <new>]
delta <field> [as <new>] [p=<N>]
autoregress <field> [as <new>] [p=<N> | p=<M>-<N>]

• accum replaces the field with its running total.
• delta gives the difference from the row N back (default 1), in delta(<field>). The first N rows have none.
• autoregress copies the field from N rows back into <field>_p<N>; p=1-3 makes _p1, _p2 and _p3.

Examples:

timechart span=1h count | accum count as total_so_far
timechart span=1h count | delta count as change
timechart span=1d count | autoregress count p=7 | eval week_over_week = count - count_p7

trendline

Moving averages, in the current order.

Syntax:

trendline <type><N>(<field>) [as <new>] ...

Without as, the column is named like sma5(count). The first N−1 rows have no value.

Example:

timechart span=1h count | trendline sma6(count) as smooth ema12(count) as ema

reverse / uniq

reverse reverses the order of the rows. uniq removes a row identical to the one before it.

stats count by host | reverse
table host level | uniq

regex

Keeps (or, with !=, removes) the rows where a field matches a regular expression. The field defaults to _raw, the message body.

Syntax:

regex [<field>=|<field>!=]"<regex>"

Examples:

regex _raw="status=5\d\d"
regex url!="^/health"

replace

Replaces values in fields. A * in the old value matches anything, and a * in the new value puts back what it matched.

Syntax:

replace <old> WITH <new>[, <old> WITH <new> ...] IN <field> ...

Examples:

replace "127.0.0.1" WITH localhost IN client_ip
replace *.internal WITH * IN host

convert

Converts fields. Each converted field is replaced, or written to a new one with as.

Syntax:

convert [timeformat="<format>"] <function>(<field>) [as <new>] ...

Example:

convert ctime(_time) as time dur2sec(uptime)

strcat

Joins fields and quoted text into a new field.

Syntax:

strcat [allrequired=<bool>] <source> ... <new-field>

With allrequired=true, the new field is empty when a source field is.

Example:

strcat namespace "/" pod_name pod_path

rangemap

Sets a range field from the range a number falls in.

Syntax:

rangemap field=<field> <name>=<low>-<high> ... [default=<name>]

The default name is None.

Example:

rangemap field=duration fast=0-200 ok=201-1000 slow=1001-100000 default=unknown
| stats count by range

xyseries and untable

xyseries turns rows into a table: one row per value of x, one column per value of y, holding value. untable turns such a table back into rows.

Syntax:

xyseries <x> <y> <value>
untable <x> <name> <value>

Examples:

stats count by host, level | xyseries host level count
chart count over host by level | untable host level count

xyseries makes up to 1000 columns, and, like chart, looks up the column values before running. It takes one value field, so grouped=, sep= and format= are not supported.

transpose

Turns the first rows into columns: each original column becomes a row.

Syntax:

transpose [<N>] [column_name=<name>] [header_field=<field>] [include_empty=<bool>]

• The first N rows (default 5; 0 for every row, up to 1000) become columns row 1 … row N, one per row there is.
• include_empty=false leaves out the fields that are empty in every row.
• column_name names the column holding the original column names (default column).
• header_field names the new columns after that field's values instead.

Example:

stats count, avg(duration) as avg_dur by level | transpose header_field=level

makeresults

Makes rows with only a _time (now), for trying out expressions. It must be the first command.

Syntax:

makeresults [count=<N>]

Example:

makeresults | eval t = strftime(relative_time(now(), "-1d@d"), "%Y-%m-%d")

extract / kv

Makes key=value pairs in the log message readable as fields: after extract, a field name that is not an attribute reads the pair from the message.

Syntax:

extract [pairdelim="<characters>"] [kvdelim="<characters>"]

Pairs are separated by spaces, commas or semicolons and written key=value unless the options say otherwise. Quoted values may contain spaces. Every pair in the whole message is read, and a key that appears twice keeps its first value, so limit=, maxchars= and mv_add= are not supported.

Example:

"payment failed"
| extract
| stats count by reason, gateway

spath

Extracts a value from JSON into a field.

Syntax:

spath [input=<field>] [output=<field>] [path=]<path>

• input defaults to _raw, the message body. The fields of JSON log messages are already readable by name, so spath is for JSON held in another field.
• output defaults to the path.
• A path is keys separated by dots. {N} picks an array element (from 0), and {} returns every element as a multivalue field.

Example:

spath input=payload path=order.items{0}.sku output=first_sku

Multivalue Fields

A multivalue field holds a list of values. split(), rex max_match=, values(), list() and makemv make one, and the multivalue functions work with them.

Syntax:

makemv [delim="<text>" | tokenizer="<regex>"] [allowempty=<bool>] <field>
mvexpand <field> [limit=<N>]
mvcombine <field>
nomv <field>

• makemv splits a field into values, at delim (default a space) or at each match of tokenizer. Empty values are dropped unless allowempty=true.
• mvexpand makes one row per value; a row whose field is empty is kept, with the field empty. limit caps the values per row.
• mvcombine does the opposite of mvexpand: rows that are the same in every other column become one row, with the field's values as a multivalue field. It needs known columns, so use it after stats, table or fields. To join the values into text, use mvjoin() or nomv.
• nomv joins the values into one, separated by newlines.

Examples:

makemv delim="," tags | mvexpand tags | stats count by tags
stats values(user) as users by service | eval n = mvcount(users)
stats count by host, level | table host, level | mvcombine level

fieldformat / setfields / noop

fieldformat and setfields work like eval: fieldformat size = tostring(bytes, "commas"). noop does nothing.