topic Re: Use 2 datasets in the same XQL query in Cortex XDR Discussions

Use 2 datasets in the same XQL query

CyrilBondenetVC — Wed, 13 Nov 2024 21:54:33 GMT

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 🙂

Re: Use 2 datasets in the same XQL query

jmazzeo — Thu, 14 Nov 2024 17:41:46 GMT

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.

Re: Use 2 datasets in the same XQL query

CyrilBondenetVC — Thu, 14 Nov 2024 18:34:36 GMT

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,

Re: Use 2 datasets in the same XQL query

jmazzeo — Thu, 14 Nov 2024 18:50:23 GMT

Take a look at this, hope it helps:

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

Re: Use 2 datasets in the same XQL query

CyrilBondenetVC — Fri, 15 Nov 2024 11:14:57 GMT

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,