lookup
Add columns from one of your lookup tables to each matching row: asset owners, user departments, allowlists, watchlists.
Use lookup to add context from your lookup tables to result rows. Match an event field against a table key to add details such as an asset owner, department, or watchlist label.
Use it for context that your logs don't carry: asset owner and criticality, a user's department, a known-good allowlist, or a watchlist of indicators. Lookup tables are reference data that you upload or schedule; see Lookup tables.
Syntax
... | lookup <table> <table_column> [AS <event_field>] [OUTPUT | OUTPUTNEW <column> [AS <new_name>], ...] [CASE_INSENSITIVE]| Argument | Description |
|---|---|
table | Lookup table name. Quote it if it contains spaces. |
table_column | The table's key column. |
AS <event_field> | The event field to match against the key column. Leave it out only when the event field has the same name as the key column. |
OUTPUT c1, c2 | Add only these table columns. Without OUTPUT, every column of the table is added. A matched value overwrites a field of the same name. |
OUTPUTNEW c1, c2 | Like OUTPUT, but only fills a field that is empty on the row. |
c AS new_name | Rename an output column as it is added. |
CASE_INSENSITIVE | Match the key without regard to case. Matching is case-sensitive by default. |
The order is the common SIEM one: table column first, then AS and the event field. For compatibility, lookup t src_endpoint.ip AS ip (dotted event field first, flat column second) is also read as event field src_endpoint.ip, column ip.
Rows with no match, or with an empty key field, pass through unchanged.
Run commands after enrichment
lookup is not compiled into the ClickHouse query. The search splits at the first lookup, prevalence, inputlookup, resolve_identity, ai or lateral stage:
- Everything before the split runs in ClickHouse as SQL.
- The rows that come back are enriched from your lookup tables.
- Everything after the split runs in post-processing on those rows.
Only these commands work after the split: where, head, tail, sort, table, stats, dedup, rename, fields, top, rare, fillnull, eval, timechart, rex, risk, reverse, tree. Some eval functions are also unavailable there (see functions).
Because only the returned rows are enriched, filter and narrow the search before lookup.
Lookup enrichment runs on the outer pipeline's rows. Put the lookup in the outer pipeline, not inside a subsearch (append, join, IN [ ... ]). A table that doesn't exist fails the search with Lookup table '<name>' does not exist, followed by the tables you have.
Examples
Add owner and criticality to endpoint process events, and keep only critical assets:
class_uid=1007 | lookup asset_inventory hostname AS device.hostname OUTPUT owner, criticality | where criticality="high" | table time, device.hostname, owner, process.name, process.cmd_lineAdd the user's department to failed logons, then count by department:
class_uid=3002 status_id=2 | lookup user_directory username AS user.name OUTPUT department CASE_INSENSITIVE | stats count by departmentDrop proxy traffic to allowlisted domains (rows that match get allowed=true):
metadata.log_source=conduit_proxy | lookup domain_allowlist domain AS http_request.url.hostname OUTPUT allowed | where isnull(allowed) | stats count by http_request.url.hostnameCheck both sides of a connection against a watchlist, renaming the output columns so they don't collide:
class_uid=4001 | lookup ip_watchlist ip AS src_endpoint.ip OUTPUT reason AS src_reason | lookup ip_watchlist ip AS dst_endpoint.ip OUTPUT reason AS dst_reason | where isnotnull(src_reason) OR isnotnull(dst_reason)Aggregate first, then enrich the much smaller result:
class_uid=4002 | stats count by http_request.url.hostname | lookup domain_categories domain AS http_request.url.hostname OUTPUT category | sort -count | head 20Notes
- To find any value of a lookup table anywhere in your logs, without naming the field, use the
ioc in lookup("<table>")search term. See retro. - To read a whole table as search results, use inputlookup.
What's next
- inputlookup: read a lookup table as results
- retro: sweep indicators, including a lookup table's values, across history
- where: filter on the added columns
- eval: compute from the added columns