Lookups and tstats

Lookups

Lookups add data from a reference table to your results: a team for each user, an owner for each service, a description for each error code. Reference tables are the CSV tables under Settings → Reference Tables, the same ones log pipeline Lookup rules use (see Reference Tables). A table is named when you upload it; use that name in SPL. A Splunk-style name with .csv also works: users.csv finds the table users.

lookup

Adds a table's columns to each row whose fields match a table row.

Syntax:

lookup <table> <column> [AS <field>] [, ...] [OUTPUT | OUTPUTNEW <column> [AS <field>], ...]

• <column> AS <field> matches the table column against a field with another name. Without AS, the field has the column's name.
• OUTPUT lists the columns to add, and replaces fields of the same name. Without it, every other column of the table is added.
• OUTPUTNEW only fills fields that are empty or that the rows do not have yet.
• Matching is exact and case-sensitive. When several table rows match, the first is used. A row with no match gets empty values.

Examples:

stats count by user
| lookup users user OUTPUT team, manager
| stats sum(count) as logs by team

level=error
| lookup services service_name AS service OUTPUT owner AS service_owner
| table _time, service, service_owner, body

inputlookup

Returns a table's rows. It must be the first command of a query or a subsearch.

Syntax:

inputlookup [start=<N>] [max=<N>] <table> [where <condition>]

start= skips the table's first N rows and max= reads at most N rows, both before where.

Examples:

inputlookup users where team = "payments"

Search the logs of the users a table lists:

search [inputlookup vip_users | fields user]
| stats count by user

Access and Limits

• Table values are text. Compare them with quoted values, or convert with tonumber().
• A lookup reads at most 8 MiB of table data.
• Lookups read reference tables; they cannot change them. outputlookup is not supported: edit tables under Settings → Reference Tables.
• A query that uses lookup or inputlookup needs Log Pipeline access, the same access that shows reference tables under Settings → Reference Tables. Without it, the query fails with a message saying so. For a Query Job, the access of the user creating the job is checked when it is created, and the access of the user retrying it when it is retried.
• A Query Job runs later, with no user, so its access is checked on the query as written. lookup and inputlookup inside a map or foreach search, whose text comes from the data, are therefore not available in a Query Job; run that query from the Log Explorer, or move the lookup outside the map or foreach.
• Reference tables are not time-based: lookup event_time_field= is not supported.

tstats

tstats counts logs from the pre-aggregated log tables, the same ones the log dashboards read, instead of reading every log line. Over days or weeks it answers in a fraction of the time stats takes.

Syntax:

tstats <aggregation> [as <alias>] ... [where <search>] [by <field> ...] [span=<span>]

Examples:

tstats count by workload
tstats count where level=error namespace=prod by service, _time span=1h
tstats count, sum(log_size_bytes) as bytes by namespace | sort -bytes
tstats dc(pod_name) as pods, values(level) as levels by workload
tstats count by pattern | sort 20 -count

What tstats can read

The pre-aggregated tables keep a count and a size for every combination of the logs' dimension fields, so tstats works only with those:

• Fields (in where and by): cluster, namespace, workload, pod_name, container_name, service, level, source, format, env_type, env, instance, host / node_name, region, app_version, customer_identifier, pattern, and _time in by.
• Aggregations: count, sum(log_size_bytes), avg(log_size_bytes), dc(<field>) and values(<field>). log_size_bytes is the length of the log messages.
• where takes search syntax on those fields: level=error host=web-*, OR, NOT, IN (…). Values match as typed, in lower case, in upper case or capitalized, as in search.

Attributes, text search and other aggregations need stats, and tstats says so when a query asks for them.

pattern is the log's message template, so tstats count by pattern shows which kinds of messages are most common.

Time buckets

by _time buckets by span= (seconds to weeks, in whole multiples of 5 seconds), or, without one, by a span that gives at most 100 buckets over the time range. tstats reads the coarsest pre-aggregated table that fits the span: 5-second, 1-minute, 1-hour or 1-day.

The pre-aggregated tables are kept longer than raw logs, so for older time ranges tstats can return counts after the logs themselves have expired, and its counts can then differ from stats.

tstats returns its results like stats, so prestats=true, append=true and local=true are not supported; use fillnull after it instead of fillnull_value=. Options that tune how Splunk reads its summaries (summariesonly=, allow_old_summaries=, chunk_size=) are accepted and change nothing.

tstats is a generating command: it must come first, or first in a subsearch. Anything can follow it:

tstats count by _time span=1h
| predict count future_timespan=24