How to use two datasets in a query

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Announcements
Please sign in to see details of an important advisory in our Customer Advisories area.

How to use two datasets in a query

L1 Bithead

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

 

 

1 accepted solution

Accepted Solutions

L4 Transporter

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

PiyushKohli_0-1700132520171.png

 

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.

View solution in original post

5 REPLIES 5

L4 Transporter

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 )

 

JM

L1 Bithead

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

L1 Bithead

@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

L4 Transporter

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

PiyushKohli_0-1700132520171.png

 

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.

L1 Bithead

Thank you!

  • 1 accepted solution
  • 876 Views
  • 5 replies
  • 0 Likes
Like what you see?

Show your appreciation!

Click Like if a post is helpful to you or if you just want to show your support.

Click Accept as Solution to acknowledge that the answer to your question has been provided.

The button appears next to the replies on topics you’ve started. The member who gave the solution and all future visitors to this topic will appreciate it!

These simple actions take just seconds of your time, but go a long way in showing appreciation for community members and the LIVEcommunity as a whole!

The LIVEcommunity thanks you for your participation!