join - Administrator Guide - Cortex XSIAM - Cortex

join - Administrator Guide - Cortex XSIAM - Cortex - Security Operations

Cortex XSIAM Documentation

Product

Cortex XSIAM

Creation date

2024-03-06

Last date published

2024-10-10

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