Join - Reference Guide - Cortex XSIAM - Cortex - Security Operations

Cortex XSIAM XQL Language Reference
Product
Cortex XSIAM
Creation date
2024-02-26
Last date published
2024-04-16
Category
Reference Guide
Abstract

Learn more about the Cortex Query Language join stage that combines the results of two queries into a single result set.

Syntax

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

Description

The join() stage combines the results of two queries into a single result set. This stage is conceptually identical to a SQL join.

Parameter/Clause

Description

conflict_strategy

Identifies the join conflict strategy when there is a conflict in the column names between the 2 result sets which one should be chosen, either:

  • right: The column from the inner join query is used (default), which implements a right outer join.

  • left: The column from the orignal result set in the dataset is used, which implements a left outer join.

  • both: Both columns are used. The original result set column from the dataset keeps the current name, while the inner join query result set column name includes the following suffix added to the current name _joined_10, such as <original column name>_joined_10, and depending on the number of conflicted fields the suffix increases to _joined_11, _joined_12....

type

Identifies the join type.

  • inner

    Returns all the records in common between the queries that are being joined. This is the default join type.

  • right

    Returns all records from the join result set, plus any records from the parent result set that intersect with the join result set.

  • left

    Returns all records from the parent result set, plus any records from the join result set that intersect with the parent result set.

<xql query>

Provides the XQL query to be joined with the parent query.

as <execution_name>

Provides an alias for the join query's result set. For example, if you specify an execution name of join1, and in the join query you return field agent_id, then you can subsequently refer to that field as join1.agent_id.

<boolean_expr>

Identifies the conditions that must be met in order to place a record in the join result set.

Note

This stage does not preserve sort order. If you are combing this stage with a sort stage, specify the sort stage after the join.

Examples

Return microsoft_windows_raw records, which are combined with the xdr_data records to include a new column called edr. For the event_type set to EVENT_LOG, the actor_process_image_name and event_id fields are returned from all xdr_data records, which are then compared to the fields inside the microsoft_windows_raw dataset, where edr.event_id = edr_event_id, and the results are added to the new edr column.

dataset  = microsoft_windows_raw 
| join (dataset = xdr_data | filter event_type = EVENT_LOG | fields actor_process_image_name, event_id ) 
as edr edr.event_id = edr_event_id

Return a maximum of 100 xdr_data records with the events of the agent_id, event_id , and _product fields, where the _product field is displayed as product. The agent_id, event_id, and _product fields are returned from all xdr_data records and are then compared to the fields inside the panw_ngfw_filedata_raw dataset, where _time = panw.time, and the results are added to the new panw column. When there is a conflict in the column names between the 2 result sets both columns are used.

dataset = xdr_data 
| fields agent_id, event_id, _product as product
| join conflict_strategy = both (dataset = panw_ngfw_filedata_raw | fields _product as product)
as panw _time = panw._time
| limit 100