topic Re: XQL Using Host Inventory Dataset and joining endpoint DS for Group Names in Cortex XDR Discussions

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

michaelsysec242 — Wed, 19 Apr 2023 12:08:58 GMT

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 ?

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

michaelsysec242 — Wed, 19 Apr 2023 12:09:41 GMT

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

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

timurphy — Wed, 19 Apr 2023 16:27:51 GMT

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

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

michaelsysec242 — Thu, 20 Apr 2023 08:35:47 GMT

@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