Correlating a file path to application inventory

[K.Murphy009035]

Hello, 

I am gathering an application inventory for endpoints in our environment. As part of this inventory, I'd like to include the install path for these applications. Currently Host Inventory XQL dataset only showcases uninstall strings in the applications field. Assistance in correlating an install path via joining datasets or something similar would be greatly appreciated.

Thanks

[susekar · Accepted solution]

Hello @K.Murphy009035 ,

Greetings for the day.

To correlate an installation path with your application inventory, you can perform a join between the Host Inventory data (which contains the static list of installed software) and the XDR Data dataset (which captures real-time process execution telemetry including the file path).

While the host_inventory dataset primarily focuses on application metadata like names, versions, and uninstall strings, the xdr_data dataset records the actual action_process_image_path when an application's process is executed on an endpoint.

Recommended XQL Query:

The following query filters for process execution events in xdr_data to retrieve the file path and then joins that data with the host_inventory_applications preset to enrich it with inventory details:

config case_sensitive = false
| dataset = xdr_data
// 1. Filter for process events to identify the execution path
| filter event_type = ENUM.PROCESS
| fields agent_hostname, action_process_image_name, action_process_image_path, action_process_signature_product
// 2. Reduce the results to unique host/application pairs
| dedup agent_hostname, action_process_image_name
// 3. Join with Host Inventory to correlate with the installed application list
| join type = left (
    preset = host_inventory_applications
    | fields endpoint_name, application_name, version, vendor
) as inv inv.endpoint_name = agent_hostname and inv.application_name contains action_process_image_name
// 4. Display the inventory name along with the identified install path
| fields agent_hostname, inv.application_name, inv.version, action_process_image_path, inv.vendor

Key Components of the Solution:

action_process_image_path: This field from the xdr_data dataset provides the full directory path to the executable.

host_inventory_applications: This is the recommended preset for retrieving a comprehensive list of installed programs, including version and vendor information.

join Stage: Correlates the hostname and application name between the two datasets to map the execution path to the inventory record.

Important Considerations:

Host Insights Licensing: Accessing the host_inventory dataset and the host_inventory_applications preset requires an active Host Insights add-on license and must be enabled in your Agent Settings profile.

Data Population: The xdr_data portion of the query only returns paths for applications that have actually run on the endpoint. If an application is installed but has never been executed, the path will not appear in xdr_data.

JSON Expansion: If you wish to manually inspect all fields within the host_inventory application array (including the uninstall string you mentioned), you can use the arrayexpand and json_extract functions:

dataset = host_inventory
| arrayexpand applications
| alter 
    app_name = json_extract(applications, "$.application_name"),
    uninstall_string = json_extract(applications, "$.uninstall_string")
| fields host_name, app_name, uninstall_string

If you feel this has answered your query, please let us know by clicking like and on "mark this as a Solution".

Thanks & Regards,
S. Subashkar Sekar

[K.Murphy009035]

Hi @susekar ,

The query required some altering to fit my needs. But the join logic was extremely insightful. Thank you for the well-crafted response.