Combining Searches

Commands that use the results of another search, written in square brackets: [search …]. A search in brackets runs over the same time range and clusters as the query around it, and is a complete query of its own: it can start with a search, a generating command or any command.

Subsearches

A subsearch inside a search keeps the logs whose field values match a row of the subsearch's result:

level=error [search status=503 | dedup user | fields user]

This finds the errors of every user who got a 503. The subsearch returned user values, so the outer search keeps logs whose user is one of them.

• With several fields, a log must match all of a row's values: [search … | fields host, level].
• End the subsearch with fields, table, stats or return so it returns the fields to match on.
• Values compare as text.
• A subsearch returns at most 10,000 rows.

NOT [ … ] keeps the logs that do not match:

level=error NOT [search level=error | top limit=5 workload | fields workload]

return

Ends a subsearch with the fields it hands back, from its first N rows (default 1).

Syntax:

return [<N>] <field> | <alias>=<field> | $<field> ...

• return user returns user values to match on.
• return src=client_ip returns client_ip values, matched against the outer search's src.
• return $user returns the bare values, searched for as text in the log message.

Examples:

search [search level=error | stats count by host | sort -count | return host]
search [inputlookup blocked_users | return 100 $user]

format is accepted at the end of a subsearch and has no effect; its options are not supported, since a subsearch's rows are matched directly.

append

Adds the subsearch's rows after the current rows. Columns are matched by name; a column only one side has is empty on the other. When both sides are log events, their attributes can still be read afterwards: search status=500 | append [search status=503] | stats count by status. The same holds for union and multisearch.

Syntax:

append [maxout=<N>] [<subsearch>]

Example:

stats count by host
| append [search level=error | stats count as errors by host]

The current rows need known columns: run append after stats, table or fields. The subsearch returns at most 50,000 rows, or fewer with maxout=. A column that holds numbers on one side and text on the other comes back as text. maxtime= and timeout= are accepted; the query's own time limit applies.

union [a] [b] … appends several subsearches at once. At the start of a query, | union [a] [b] returns only the subsearches' rows.

appendcols

Puts the subsearch's columns beside the current ones: its first row beside the first row, its second beside the second, and so on.

Syntax:

appendcols [override=<bool>] [maxout=<N>] [<subsearch>]

A column both sides have keeps the current value, unless override=true. maxout= caps the subsearch's rows (at most 50,000).

Example:

stats count by host | sort host
| appendcols [search level=error | stats count as errors by host | sort host]

appendpipe

Runs commands on the current results and appends what they return, for example a totals row.

Syntax:

appendpipe [<commands>]

Example:

stats count by host
| appendpipe [stats sum(count) as count | eval host = "TOTAL"]

join

Joins each row with a subsearch row that has the same values of the join fields.

Syntax:

join [type=inner|left|outer] [max=<N>] [overwrite=<bool>] <field> ... [<subsearch>]

The subsearch must return the join fields. Values compare as text, and the subsearch returns at most 50,000 rows. Rows join by field values only: usetime=true and earlier=false are not supported.

Examples:

stats count by workload
| join workload [search level=error | stats count as errors by workload]

level=error
| join type=left user [inputlookup users | fields user, team]

For a lookup table, lookup is simpler.

selfjoin

Gives each row the other fields of the next row, in the current order, that has the same values of the fields. Rows with no such row are dropped unless keepsingle=true.

table session_id, step | selfjoin session_id

transaction

Groups related events into transactions: the events with the same values of the fields, split where the options say.

Syntax:

transaction <field> ... [maxspan=<span>] [maxpause=<span>] [maxevents=<N>] [startswith=<condition>] [endswith=<condition>] [keepevicted=<bool>]

startswith and endswith take a quoted search (startswith="action=login") or eval(<expression>) (endswith=eval(status >= 400)).

A transaction closes when, with startswith, its first event matches it and, with endswith, its last event does. Transactions that do not close are dropped unless keepevicted=true.

A transaction's fields hold their distinct values, so mvlist=true, mvraw=true, connected=false, nullstr= and delim= are not supported.

Each transaction is one row with the fields, _time (its first event), duration (seconds from the first to the last event), eventcount, and body (the messages, oldest first, one per line). Transactions are returned newest first.

Examples:

transaction session_id maxpause=10m
| stats avg(duration) as avg_session, avg(eventcount) as avg_events

transaction user startswith="login" endswith="logout"
| where duration > 3600

foreach

Runs commands once for each of several fields. In the commands, <<FIELD>> is the field's name and <<MATCHSTR>> (or <<MATCHSEG1>>) the part a * matched.

Syntax:

foreach <field-or-wildcard> ... [fieldstr=<text>] [matchstr=<text>] [matchseg1=<text>] [<commands>]

Wildcards match the result's columns. fieldstr=, matchstr= and matchseg1= replace the placeholders, for example foreach count_* fieldstr=FLD [eval FLD = FLD * 2] (not #, which starts a comment). Only mode=multifield (the default) is supported.

Example:

stats sum(bytes_in) as in_total, sum(bytes_out) as out_total by host
| foreach *_total [eval <<FIELD>> = round(<<FIELD>> / 1048576, 1)]

map

Runs a search once for each result row, and returns the rows of all the searches. In the search, $field$ is replaced by the row's value of the field, and $_serial_id$ by the row's number, from 1.

Syntax:

map search="<search>" [maxsearches=<N>]
map [<search>] [maxsearches=<N>]

• maxsearches= is how many rows run the search (default 10, at most 100).
• The values are put into the search as written, so quote a $field$ whose values may contain spaces: host="$host$". A value may only fill in values: a row whose value would add or change a command or subsearch (| stats …, [ … ], or a value that is itself a command, such as inputlookup users) fails the query. The same holds for foreach when its fields come from the data, such as a chart's columns.
• Explaining or validating a query shows the search once, with the field names in place of values.

Examples:

stats count by host | sort -count | head 3
| map search="search host=$host$ level=error | stats count as errors | eval host=\"$host$\""

inputlookup services
| map [search service="$service$" | stats p95(duration) as p95 | eval service = "$service$"] maxsearches=20

Generating Commands

These commands make the rows a query starts from, instead of reading logs. They must come first.

Examples:

set diff [search level=error | dedup host | fields host] [search level=error earliest=-1d | dedup host | fields host]

gentimes start=-7 | eval day = strftime(starttime, "%Y-%m-%d")