https://community.splunk.com/t5/Splunk-Search/Splunk-Lookup-compare-with-index/m-p/659111#M227624 <P>Try this, it works for me</P><LI-CODE lang="markup">index=index | stats count by country_name | join type=left country_name [| inputlookup tests.csv | stats count as Exists by country_name] | fillnull Exists value=0 | where Exists=0</LI-CODE><P>&nbsp;</P> Fri, 29 Sep 2023 18:05:23 GMT Thulasinathan_M 2023-09-29T18:05:23Z https://community.splunk.com/t5/Splunk-Search/Splunk-Lookup-compare-with-index/m-p/659103#M227618 <P>Query to output missing data in lookup file.<BR /><BR /><BR />I have a lookup file with below data<BR /><BR />country_name<BR />--------------------<BR /><BR />Brazil<BR />Norway<BR /><BR /><BR />My index search returns below data for field(country_name)<BR /><BR />Brazil<BR />Norway<BR />Spain<BR /><BR />------------------------------------------------------------------</P><P><BR />How do I write a query (using join or append)- to output&nbsp; only "Spain" in the results.<BR /><BR />Thanks!<BR /><BR /><BR /></P> Fri, 29 Sep 2023 17:26:52 GMT https://community.splunk.com/t5/Splunk-Search/Splunk-Lookup-compare-with-index/m-p/659103#M227618 Krish14 2023-09-29T17:26:52Z https://community.splunk.com/t5/Splunk-Search/Splunk-Lookup-compare-with-index/m-p/659106#M227621 <P>Couldn't able to test this, but should work. Please let me know if it doesn't work.</P><LI-CODE lang="markup">index=index country_name | table country_name | join type=left country_name [search | inputlookup | stats count as Exist by country_name] | fillnull Exist value=0 | where Exist=0</LI-CODE><P>&nbsp;</P> Fri, 29 Sep 2023 17:39:41 GMT https://community.splunk.com/t5/Splunk-Search/Splunk-Lookup-compare-with-index/m-p/659106#M227621 Thulasinathan_M 2023-09-29T17:39:41Z https://community.splunk.com/t5/Splunk-Search/Splunk-Lookup-compare-with-index/m-p/659108#M227622 <P>Looks like there is a syntax error "search" keyword&nbsp;<BR />I removed it and tried with below, however,&nbsp; the output has all the data from index.<BR /><BR />We are only interested to output data from index - that is not present in lookup.</P><LI-CODE lang="markup">index=index country_name | table country_name | join type=left country_name [ | inputlookup | stats count as Exist by country_name] | fillnull Exist value=0 | where Exist=0</LI-CODE><P>&nbsp;</P> Fri, 29 Sep 2023 17:55:49 GMT https://community.splunk.com/t5/Splunk-Search/Splunk-Lookup-compare-with-index/m-p/659108#M227622 Krish14 2023-09-29T17:55:49Z https://community.splunk.com/t5/Splunk-Search/Splunk-Lookup-compare-with-index/m-p/659111#M227624 <P>Try this, it works for me</P><LI-CODE lang="markup">index=index | stats count by country_name | join type=left country_name [| inputlookup tests.csv | stats count as Exists by country_name] | fillnull Exists value=0 | where Exists=0</LI-CODE><P>&nbsp;</P> Fri, 29 Sep 2023 18:05:23 GMT https://community.splunk.com/t5/Splunk-Search/Splunk-Lookup-compare-with-index/m-p/659111#M227624 Thulasinathan_M 2023-09-29T18:05:23Z https://community.splunk.com/t5/Splunk-Search/Splunk-Lookup-compare-with-index/m-p/659112#M227625 <P>Excellent, Works fine for me too. Thank you for prompt response! Much appreciated!</P> Fri, 29 Sep 2023 18:11:57 GMT https://community.splunk.com/t5/Splunk-Search/Splunk-Lookup-compare-with-index/m-p/659112#M227625 Krish14 2023-09-29T18:11:57Z https://community.splunk.com/t5/Splunk-Search/Splunk-Lookup-compare-with-index/m-p/659113#M227626 <P>Happy that worked for you!!&nbsp; <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Fri, 29 Sep 2023 18:13:32 GMT https://community.splunk.com/t5/Splunk-Search/Splunk-Lookup-compare-with-index/m-p/659113#M227626 Thulasinathan_M 2023-09-29T18:13:32Z