topic Re: How to use two datasets in a query in Cortex XDR Discussions

How to use two datasets in a query

RiveraMarco — Wed, 15 Nov 2023 17:43:50 GMT

Hi,

I hope you can shed some light. I am attempting to run a query to find out what system is running what applications including the username.

I have this query which gives me what I need except the "user" which is a field in another dataset xdr_data. How can add this new dataset to get the "user" field? Thank you

Re: How to use two datasets in a query

jmazzeo — Wed, 15 Nov 2023 17:47:42 GMT

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

You can call two datasets using the "in" operator.

Example:

dataset in (xdr_data, panw_ngfw_traffic_raw )

Re: How to use two datasets in a query

RiveraMarco — Wed, 15 Nov 2023 17:56:17 GMT

So how to add the user field from xdr_data into my query? Thanks for your help

Re: How to use two datasets in a query

RiveraMarco — Wed, 15 Nov 2023 17:59:26 GMT

@jmazzeo

So how to add the user field from xdr_data into my query? Thanks for your help

Re: How to use two datasets in a query

PiyushKohli — Thu, 16 Nov 2023 11:02:42 GMT

Hi @RiveraMarco

You may try this and edit this query based on your requirements:

config case_sensitive = false
| dataset = host_inventory
| fields host_name, applications
| arrayexpand applications
| alter readable_application_name = json_extract(applications, "$.application_name")
| alter app_name = trim (readable_application_name ,"\"")
| fields app_name
| join type = inner (dataset= xdr_data | fields actor_process_image_name , os_actor_process_image_name , action_file_name , actor_primary_username , causality_actor_process_image_name ) as aip aip.actor_process_image_name contains app_name
| filter (actor_primary_username != """NT AUTHORITY\\SYSTEM""")
| dedup app_name , actor_primary_username , actor_process_image_name

Feel free to write back if you have further query.

Hope this helps!

Please mark the response as "Accept as Solution" if it answers your query.

Re: How to use two datasets in a query

RiveraMarco — Thu, 16 Nov 2023 16:54:42 GMT

Thank you!