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

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Announcements

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

L3 Networker

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

2 REPLIES 2

L5 Sessionator

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

 

 

L3 Networker

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

 

NathanBradley_0-1665495995108.png

 

  • 2528 Views
  • 2 replies
  • 0 Likes
Like what you see?

Show your appreciation!

Click Like if a post is helpful to you or if you just want to show your support.

Click Accept as Solution to acknowledge that the answer to your question has been provided.

The button appears next to the replies on topics you’ve started. The member who gave the solution and all future visitors to this topic will appreciate it!

These simple actions take just seconds of your time, but go a long way in showing appreciation for community members and the LIVEcommunity as a whole!

The LIVEcommunity thanks you for your participation!