Use 2 datasets in the same XQL query

[CyrilBondenetVC]

Hello, I would like to be able to use the dataset “xdr_data & endpoint” in the same query.

Here's my query : 

dataset = xdr_data
| filter event_type = ENUM.FILE and event_sub_type = ENUM.FILE_OPEN
| alter Drive_Type = json_extract(to_json_string(action_file_device_info),"$.storage_device_drive_type"), Filesystem = json_extract_scalar(to_json_string(action_file_device_info),"$.storage_device_filesystem"), Drive_Letter = json_extract_scalar(to_json_string(action_file_device_info),"$.storage_device_mount_point"), Device_Serial_Number = json_extract_scalar(to_json_string(action_file_device_info),"$.storage_device_serial_number") // Getting details about the device a file was created on
| filter drive_type = "2" // Filtering by drive type 2 which is 'Removable Media'
| fields action_file_path as File_Path, actor_effective_username as Username, Filesystem, Drive_Letter, Device_Serial_Number

This query displays all the files that have been opened from a USB stick. I'd like to be able to display the tags and endpoint groups of the endpoints in the fields, so that I can sort as I wish and get exactly what I'm looking for.

if anyone has a solution I'm interested 🙂

Cortex XDR 

[jmazzeo · Accepted solution]

Hi @CyrilBondenetVC, thanks for reaching us using the Live Community.

You need to use the Join stage in the XQL to look for the agent hostname between both datasets.

Try this query:

dataset = xdr_data
| filter event_type = ENUM.FILE and event_sub_type = ENUM.FILE_OPEN
| alter Drive_Type = json_extract(to_json_string(action_file_device_info),"$.storage_device_drive_type"), Filesystem = json_extract_scalar(to_json_string(action_file_device_info),"$.storage_device_filesystem"), Drive_Letter = json_extract_scalar(to_json_string(action_file_device_info),"$.storage_device_mount_point"), Device_Serial_Number = json_extract_scalar(to_json_string(action_file_device_info),"$.storage_device_serial_number") // Getting details about the device a file was created on
| filter drive_type = "2" // Filtering by drive type 2 which is 'Removable Media'
| join (dataset = endpoints | fields group_names as GroupNames, tags as Tags, endpoint_name as EndpointName ) as endpoints endpoints.EndpointName = agent_hostname
| fields action_file_path as File_Path, actor_effective_username as Username, Filesystem, Drive_Letter, Device_Serial_Number, agent_hostname, GroupNames, Tags

If this post answers your question, please mark it as the solution.

[CyrilBondenetVC]

Thank you very much. That's exactly what I needed. 

In order to understand better, could you explain to me what this part corresponds to?

as endpoints endpoints.EndpointName = agent_hostname

 Regards,

[jmazzeo · Accepted solution]

Take a look at this, hope it helps:

You have more details in the linked doc in my previous post.

[CyrilBondenetVC]

Hello,

I understand better now, thank you for taking the time to answer my questions.

I think it will help a lot of people 🙂

Regards,