How to compare list with lookup?

[knalla]

Hi all, I'm trying to compare list of apps by server with a list of apps in lookup to find if its installed or not. I tried Join and append, its not working. Please advise.

|inputlookup app_list.csv| table app_name

index=test | table system app_name | stats values(app_name) by system| append [|inputlookup app_list.csv| table app_name

[bowesmana]

If I understand your question correctly, you are looking to see if your index data for any given server contains the apps in the lookup, so you are trying to check a negative state in your data, so if you have the lookup containing

app_name
app_1
app_2
app_3

and your test index events have rows like

system=sys_1, app_name=app_1
system=sys_2, app_name=app_1
system=sys_3, app_name=app_1
system=sys_2, app_name=app_2
system=sys_1, app_name=app_3
system=sys_3, app_name=app_3

Then you would want to see

system    Apps      Status
system_1  app1      installed
          app2      missing
          app3      installed
system_2  app1      installed
          app2      installed
          app3      missing
system_3  app1      installed
          app2      missing
          app3      installed

Then this should do the trick

index=test 
| stats count by system app_name 
| append [
  | inputlookup app_list.csv
  | eval system="__"
  | rename app_name as wanted_app_name
  | table system wanted_app_name
]
| stats list(wanted_app_name) as wanted_app_name list(app_name) as app_name by system
| filldown wanted_app_name
| where system!="__"
| mvexpand wanted_app_name
| eval installed=if(!isnull(mvfind(app_name, wanted_app_name)), "installed", "missing")
| stats list(wanted_app_name) as Apps list(installed) as Status by system

This

• collects the apps from the index data by system
• Appends all apps from file and makes a single field with all apps
• which is then copies to all data rows (filldown)
• expands that wanted_apps list (mvexpand)
• checks if each of those wanted apps is found in the apps from the data (mvfind)
• uses stats list to list the apps and their status

Note that the stats list operation only supports 100 items, so you cannot have more than 100 apps in this case.

stats values will not work unless you do some additional processing to stitch up the app/status

Hope this helps