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

[michaelsysec242]

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 

[michaelsysec242]

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

[timurphy · Accepted solution]

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

[michaelsysec242]

@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