How to use two datasets in a query

[RiveraMarco]

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

config case_sensitive = false
| dataset = host_inventory
| fields host_name, applications
| arrayexpand applications | alter readable_application_name = json_extract(applications, "$.application_name")
| fields readable_application_name, host_name

[jmazzeo]

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 )

[RiveraMarco]

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

config case_sensitive = false
| dataset in (host_inventory, xdr_data)
| fields host_name, applications
| arrayexpand applications | alter readable_application_name = json_extract(applications, "$.application_name")
| fields readable_application_name, host_name

[RiveraMarco]

@jmazzeo 

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

config case_sensitive = false
| dataset in (host_inventory, xdr_data)
| fields host_name, applications
| arrayexpand applications | alter readable_application_name = json_extract(applications, "$.application_name")
| fields readable_application_name, host_name

[PiyushKohli · Accepted solution]

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.

[RiveraMarco]

Thank you!