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

[NathanBradley]

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

[bbarmanroy]

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

[NathanBradley]

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