https://community.splunk.com/t5/Splunk-Search/How-to-compare-list-with-lookup/m-p/521373#M146922 <P>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.</P><P>|inputlookup app_list.csv| table app_name</P><P>index=test | table system app_name | stats values(app_name) by system| append [|inputlookup app_list.csv| table app_name</P><P>&nbsp;</P> Thu, 24 Sep 2020 20:57:18 GMT knalla 2020-09-24T20:57:18Z https://community.splunk.com/t5/Splunk-Search/How-to-compare-list-with-lookup/m-p/521373#M146922 <P>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.</P><P>|inputlookup app_list.csv| table app_name</P><P>index=test | table system app_name | stats values(app_name) by system| append [|inputlookup app_list.csv| table app_name</P><P>&nbsp;</P> Thu, 24 Sep 2020 20:57:18 GMT https://community.splunk.com/t5/Splunk-Search/How-to-compare-list-with-lookup/m-p/521373#M146922 knalla 2020-09-24T20:57:18Z https://community.splunk.com/t5/Splunk-Search/How-to-compare-list-with-lookup/m-p/521382#M146924 <P>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</P><P>&nbsp;</P><LI-CODE lang="markup">app_name app_1 app_2 app_3</LI-CODE><P>&nbsp;</P><P>and your test index events have rows like</P><P>&nbsp;</P><LI-CODE lang="markup">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</LI-CODE><P>&nbsp;</P><P>Then you would want to see</P><P>&nbsp;</P><LI-CODE lang="markup">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</LI-CODE><P>&nbsp;</P><P>Then this should do the trick</P><P>&nbsp;</P><LI-CODE lang="markup">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</LI-CODE><P>&nbsp;</P><P>This</P><UL><LI>collects the apps from the index data by system</LI><LI>Appends all apps from file and makes a single field with all apps</LI><LI>which is then copies to all data rows (filldown)</LI><LI>expands that wanted_apps list (mvexpand)</LI><LI>checks if each of those wanted apps is found in the apps from the data (mvfind)</LI><LI>uses stats list to list the apps and their status</LI></UL><P>Note that the stats list operation only supports 100 items, so you cannot have more than 100 apps in this case.</P><P>stats values will not work unless you do some additional processing to stitch up the app/status</P><P>Hope this helps</P><P>&nbsp;</P> Thu, 24 Sep 2020 22:28:42 GMT https://community.splunk.com/t5/Splunk-Search/How-to-compare-list-with-lookup/m-p/521382#M146924 bowesmana 2020-09-24T22:28:42Z