XQL Using Host Inventory Dataset and joining endpoint DS for Group Names

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.

XQL Using Host Inventory Dataset and joining endpoint DS for Group Names

L3 Networker

Hello, I am attempting to write a query in which I display the host inventory applications and the Group Names field from the endpoint dataset. I have used in separate occasions Union and Join On but without success. 

What can I do without affecting the datasets with Target etc ?

Cortex XDR 

1 accepted solution

Accepted Solutions

L3 Networker

Hi @michaelsysec242,

 

Here is a simple example to get you started:

 

 

preset = host_inventory_applications 
| fields install_date, vendor, application_name, version, endpoint_name, endpoint_id
| join type = left (dataset = endpoints | fields group_names, endpoint_id) as endpoints endpoints.endpoint_id = endpoint_id

 

 

 

In the join stage, you define the query (wrapped in parenthesis) targeting another dataset. That query is given a name (execution name), which in this example I simply called endpoints. You can then refer to fields within the secondary query using dot-notation (endpoints.endpoint_id is referring to the endpoint_id field within the query defined in the join stage).

 

The expression at the end of the line explains how the join is performed, in this case we are joining when there is an exact match of the endpoint_id field from the parent query with the endpoint_id field in the endpoints dataset.

 

I hope this helps!

 

Regards,

Tim

View solution in original post

3 REPLIES 3

L3 Networker

I would much appreciate if someone can provide an example, cheers!

L3 Networker

Hi @michaelsysec242,

 

Here is a simple example to get you started:

 

 

preset = host_inventory_applications 
| fields install_date, vendor, application_name, version, endpoint_name, endpoint_id
| join type = left (dataset = endpoints | fields group_names, endpoint_id) as endpoints endpoints.endpoint_id = endpoint_id

 

 

 

In the join stage, you define the query (wrapped in parenthesis) targeting another dataset. That query is given a name (execution name), which in this example I simply called endpoints. You can then refer to fields within the secondary query using dot-notation (endpoints.endpoint_id is referring to the endpoint_id field within the query defined in the join stage).

 

The expression at the end of the line explains how the join is performed, in this case we are joining when there is an exact match of the endpoint_id field from the parent query with the endpoint_id field in the endpoints dataset.

 

I hope this helps!

 

Regards,

Tim

@timurphy Thanks a lot that worked well. I went with a similar approach and realised the issue I had was in the join to keep the original field names when I should have aliased them as 'As'. 

Thanks

  • 1 accepted solution
  • 1605 Views
  • 3 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!