topic XQL query to find endpoints where X application is installed but not Y application in Cortex XDR Discussions

XQL query to find endpoints where X application is installed but not Y application

NathanBradley — Mon, 10 Oct 2022 20:10:39 GMT

Im needing to find endpoints that have a certain application (Application1) installed but then does not have (Application2) installed

The query below returns results that have either Application1 or Application2

Im downloading the results and then using excel to find non duplicates, any way for xql to give me the results i need?

config case_sensitive = false timeframe=1d
| dataset = host_inventory
| filter applications != null
| arrayexpand applications
| alter applicationname=json_extract(applications, "$.application_name")
| alter applicationversion=json_extract(applications, "$.version")
| alter appvendor=json_extract(applications, "$.vendor")
| alter installdate=json_extract(applications, "$.install_date")
| filter applicationname contains "Application1" or applicationname contains "Application2"
| fields host_name, applicationname, applicationversion, appvendor, installdate, system_type, product_type, ip_addresses

Re: XQL query to find endpoints where X application is installed but not Y application

bbarmanroy — Tue, 11 Oct 2022 05:48:41 GMT

Hi @NathanBradle, now that's a tricky one!
See if this helps:

dataset = host_inventory | filter applications != null | arrayexpand applications | alter applicationname=json_extract(applications, "$.application_name") | alter applicationversion=json_extract(applications, "$.version") | alter appvendor=json_extract(applications, "$.vendor") | alter installdate=json_extract(applications, "$.install_date") | alter applicationNameCount = "0" // create a new column called 'applicationnameC | alter applicationNameCount = if (applicationname contains "Chrome", replace (applicationNameCount, "0" , "1" ), applicationNameCount ) // if application 1 is installed | alter applicationNameCount = if (applicationname contains "Firefox", replace (applicationNameCount, "0", "2"), applicationNameCount ) // if application 2 is installed | comp sum (to_integer(applicationNameCount)) as appInstalled by host_name | filter appInstalled = 1

Re: XQL query to find endpoints where X application is installed but not Y application

NathanBradley — Tue, 11 Oct 2022 13:48:03 GMT

Thanks a ton I would not have gotten there, i was spot checking results at least 1 endpoint it didnt get

The endpoint below does have App1 but does not have App2

Could it be because the query got host_inventory data from 2 days, so there are 2 instances of App1 listed

Im not entirely sure what the last 2 lines of your query do though