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>] [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 available
• outer - All events from both searches
  Default: inner

max
Syntax: max=<int>
Description: Maximum results from subsearch per join key
Default: 1

overwrite
Syntax: overwrite=true|false
Description: Whether subsearch fields overwrite main search fields
Default: true

Examples

Enrich with user data

action=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" priority

Inner join for filtering

* | join type=inner src_ip [search action=suspicious | table src_ip]

Max results per key

* | join type=left user max=5 [search action=login | stats count() by user, src_ip | table user, src_ip, count]

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 searches
• left - All main search events, nulls where no match
• outer - All events from both searches

Max parameter: Limits how many subsearch results join to each main search event.

Alternative: Consider lookup for static reference data instead of subsearches.

Related Commands

• lookup - Join with lookup tables (more efficient)
• append - Combine results without matching
• stats - Aggregate before joining