join
join
Combine results from main search with a subsearch based on common field values.
Description
The join command performs SQL-style joins between your main search and a subsearch. It matches events based on one or more common fields, enriching main search results with data from the subsearch.
Syntax
... | join [type=<join_type>] <field> [, <field> ...] [max=<int>] [maxout=<int>] [overwrite=<bool>] [<subsearch>]Required Arguments
field
One or more fields to join on. Values must match between main search and subsearch.
subsearch
A complete search query in square brackets that provides the join data.
Optional Arguments
type
Syntax: type=inner|left|outer
Description: Type of join to perform
inner- Only matching events (default)left- All main search events, matched subsearch data where availableouter- All events from both searches
Default:inner
max
Syntax: max=<int>
Description: Maximum results from subsearch per join key
Default: 1
maxout
Syntax: maxout=<int>
Description: Maximum total rows returned by the subsearch. Increase this if your subsearch needs to return more than the default 10,000 rows.
Default: 10000
Maximum: 100000
overwrite
Syntax: overwrite=true|false
Description: Whether subsearch fields overwrite main search fields
Default: true
dataset (inside the subsearch)
Syntax: [dataset=logs|spans|metrics <search> ...]
Description: Run the subsearch against a different dataset than the main search — logs, OpenTelemetry traces (spans), or metrics. Lead with dataset= as the first token inside the brackets. Enables cross-telemetry correlation on a shared key (see below).
Default: the main search's dataset
Cross-dataset correlation (logs · traces · metrics)
A subsearch can target a different dataset from the main search, so you can correlate across telemetry types on a shared key — e.g. a span (trace) joined to its security log on trace_id, or a service's spans joined to its CPU metrics on service_name. Lead the subsearch with dataset=:
<main search> | join <key> [dataset=<logs|spans|metrics> <subsearch>]The main search picks its dataset the usual way (the Dataset selector / a detection rule's dataset); the subsearch's dataset= overrides only the subsearch. The join key must exist on both sides — trace_id is the surgical one (it ties a span to the exact request's logs), while service_name / user / src_ip correlate at the service or entity level.
Filter vs. merge.
| joinbrings the matched side's columns onto your rows (a merge). When you only need to filter — "main-search rows that have a match" — the lighter<key> IN [dataset=… <subsearch> | return <key>]semi-join is cheaper and the recommended default; reach for| joinwhen you want the other side's fields on the result.
Examples
Enrich with user data
event_type=login
| join type=left user [search source_type="user_directory" | table user, department, manager]Add asset information
* | join type=left src_host [search source_type="asset_inventory" | table src_host, owner, criticality]Correlate with threat intel
* | join type=inner file_hash [search source_type="threat_feed" | table file_hash, threat_level, malware_family]Multiple join fields
* | join type=left src_ip, dest_port [search source_type="service_catalog" | table src_ip, dest_port, service_name]Left join with defaults
* | join type=left user [search source_type="users" | table user, priority]
| fillnull value="normal" priorityInner join for filtering
* | join type=inner src_ip [search event_type=suspicious | table src_ip]Max results per key
* | join type=left user max=5 [search event_type=login | stats count() by user, src_ip | table user, src_ip, count]Large subsearch with maxout
* | join maxout=50000 src_ip [search source_type="firewall" | stats count() by src_ip]Cross-dataset: enrich spans with their security log (on trace_id)
Main search runs on traces; the subsearch pulls the matching log so each span row carries the log's user / src_ip:
status_code="OK" span_name=/.*(admin|password|token).*/
| join trace_id [dataset=logs auth_result="failure"]This surfaces a request that succeeded at a privileged operation despite failing authentication — a broken-access-control / auth-bypass signal that neither traces nor logs can see alone (the trace knows the operation succeeded; the log knows auth failed; the trace_id join reveals the contradiction).
Cross-dataset: spans correlated with high-CPU metrics (on service_name)
status_code="ERROR"
| join service_name [dataset=metrics metric_name="system.cpu.utilization" value>0.9]Cross-dataset filter (semi-join — cheaper than a merge)
"Spans whose request also produced an auth-failure log" — filters, doesn't merge:
trace_id IN [dataset=logs auth_result="failure" | return trace_id]Usage Notes
Performance: Joins can be expensive. Filter subsearch results to minimize data.
Field conflicts: By default, subsearch fields overwrite main search fields with same names.
Join types:
inner- Only events with matches in both searchesleft- All main search events, nulls where no matchouter- All events from both searches
Max parameter: Limits how many subsearch results join to each main search event.
Maxout parameter: Subsearch results are capped at 10,000 rows by default. If your subsearch matches more rows, use maxout=N to increase the limit (up to 100,000). A warning is shown when the default cap is in effect.
Alternative: Consider lookup for static reference data instead of subsearches.
Cross-dataset joins: a dataset= subsearch requires a join key — a keyless cross-dataset join is rejected (it would be an unbounded scan). The key should be selective: a small, specific correlation set (e.g. one incident's trace ids) is fast; a broad one (millions of keys) is slow. The subsearch stays capped (maxout), and a cost advisory is shown when a cross-dataset correlation runs. For "filter only" use the IN [dataset=… | return <key>] semi-join — it uses less memory than a merge.
Detections: cross-dataset correlation works inside scheduled detection rules (set the rule's dataset, then join/IN into another) — e.g. alert on the auth-bypass example above. Real-time (materialized-view) rules are logs-only.