join ↗

Use the join stage to combine the results of two queries into a single result set based on a specified condition.

Syntax

join conflict_strategy = both|left|right type = inner|left|right ((<xql query>) as <execution_name> <boolean_expr>)

Parameters

Name Type Required Description
conflict_strategy string No Identifies how column name conflicts are resolved if a field name exists in both the parent (left) query's result set and the joined (right) query's result set. Valid values are both, left, or right. The default is right.
type string No Defines the type of join, dictating which records are included in the final result set based on the join condition. Valid values are inner, left, or right. The default is inner.
xql query query block Yes The XQL query whose results you want to combine with the parent query. This sub-query must be enclosed in parentheses.
execution_name string Yes Provides an alias for the joined query's result set using the as clause. This alias is used to refer to fields from the joined query (for example, alias.field_name).
boolean_expr expression Yes Identifies the conditions (join keys) that must be met to place a record in the join result set.

Returns

The join stage returns a unified result set containing rows from the parent query combined with rows from the sub-query, based on the specified join type and condition.

Usage notes

  • The join stage can combine results, but it does not preserve sort order. If sorting is needed, specify the sort stage after the join stage.
  • join operations, especially on large datasets like xdr_data, can be resource-intensive. It is a Best practice to pare down datasets before joining them.
  • Always use the fields stage early in your queries, including within the sub-queries of a join, to select only necessary columns. This minimizes the data processed and improves performance.
  • The config case_sensitive stage can be used at the beginning of the query or when adding a join stage to control case sensitivity for field value evaluation.
  • By default, forensic datasets are not included in XQL query results unless explicitly defined. Queries for forensic data require specific enabling steps.

Examples

Example 1: Inner join

Goal: Perform an inner join to return only the records where there is a match in event_id from both the left and right datasets.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| filter event_id in (101, 102, 103, 104) // Simulates left side
| fields event_id, event_description
| join type = inner (
    dataset = sample_xql_raw
    | filter event_id in (103, 104, 105, 106) // Simulates right side
    | fields event_id as joined_event_id, is_successful as success_status
) as right_data right_data.joined_event_id = event_id // Join condition

Explanation: This query joins records from sample_xql_raw where event_id is 101, 102, 103, or 104, with records where event_id is 103, 104, 105, or 106. Only event_ids present in both sets will be returned. The joined_event_id from right_data is matched with event_id from the main query.

Output:

event_id event_description joined_event_id success_status
103 Network connection established 103 true
104 System heartbeat 104 true

Example 2: Left join

Goal: Perform a left join to return all records from the left dataset, and the matching records from the right.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| filter event_id in (101, 102, 103, 104) // Simulates left side
| fields event_id, event_description
| join type = left (
    dataset = sample_xql_raw
    | filter event_id in (103, 104, 105, 106) // Simulates right side
    | fields event_id as joined_event_id, is_successful as success_status
) as right_data right_data.joined_event_id = event_id // Join condition

Explanation: This query returns all event_ids 101, 102, 103, 104 from the left side. For event_ids 101 and 102, since there are no matching records on the right side, NULL is populated for joined_event_id and success_status.

Output:

event_id event_description joined_event_id success_status
101 User login successful NULL NULL
102 File access attempt NULL NULL
103 Network connection established 103 true
104 System heartbeat 104 true

Example 3: Right join

Goal: Perform a right join to return all records from the right dataset, and the matching records from the left.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| filter event_id in (101, 102, 103, 104) // Simulates left side
| fields event_id, event_description
| join type = right (
    dataset = sample_xql_raw
    | filter event_id in (103, 104, 105, 106) // Simulates right side
    | fields event_id as joined_event_id, is_successful as success_status
) as right_data right_data.joined_event_id = event_id // Join condition

Explanation: This query returns all event_ids 103, 104, 105, 106 from the right side. For event_ids 105 and 106, since there are no matching records on the left side, NULL is populated for event_description.

Output:

event_id event_description joined_event_id success_status
103 Network connection established 103 true
104 System heartbeat 104 true
NULL NULL 105 true
NULL NULL 106 false

Example 4: Conflict strategy left

Goal: Perform an inner join while prioritizing the column from the original (parent) query's result set when a conflict exists.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| filter event_id in (101, 102, 103, 104) // Simulates left side with original event_description
| fields event_id, event_description
| join conflict_strategy = left // Prioritize left side event_description
type = inner (
    dataset = sample_xql_raw
    | filter event_id in (103, 104, 105, 106) // Simulates right side data for join
    | fields event_id as joined_event_id, is_successful as event_description // This event_description will be discarded due to conflict_strategy = left
) as right_data right_data.joined_event_id = event_id // Join condition
| fields event_id, event_description, joined_event_id // Select fields to show output

Explanation: The sub-query renames is_successful to event_description to create a conflict. Since conflict_strategy = left is specified, the event_description from the primary (left) query, which contains string values (for example, "Network connection established"), is preserved.

Output:

event_id event_description joined_event_id
103 Network connection established 103
104 System heartbeat 104

Example 5: Conflict strategy right

Goal: Perform an inner join while prioritizing the column from the inner (joined) query's result set when a conflict exists.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| filter event_id in (101, 102, 103, 104) // Simulates left side with original event_description
| fields event_id, event_description
| join conflict_strategy = right // Prioritize right side event_description
type = inner (
    dataset = sample_xql_raw
    | filter event_id in (103, 104, 105, 106) // Simulates right side data for join
    | fields event_id as joined_event_id, is_successful as event_description // This event_description will be kept
) as right_data right_data.joined_event_id = event_id // Join condition
| fields event_id, event_description, joined_event_id // Select fields to show output

Explanation: With conflict_strategy = right, the event_description column from the inner (right) query is kept. This results in the event_description column containing the boolean values from is_successful (for example, true), while the original string event_description from the left query is discarded for the joined records.

Output:

event_id event_description joined_event_id
103 true 103
104 true 104