Splunk Lookup compare with index

[Krish14]

Query to output missing data in lookup file.


I have a lookup file with below data

country_name
--------------------

Brazil
Norway


My index search returns below data for field(country_name)

Brazil
Norway
Spain

------------------------------------------------------------------

How do I write a query (using join or append)- to output  only "Spain" in the results.

Thanks!

[Thulasinathan_M]

Couldn't able to test this, but should work. Please let me know if it doesn't work.

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

[Krish14]

Looks like there is a syntax error "search" keyword 
I removed it and tried with below, however,  the output has all the data from index.

We are only interested to output data from index - that is not present in lookup.

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

[Thulasinathan_M · Accepted solution]

Try this, it works for me

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

[Krish14]

Excellent, Works fine for me too. Thank you for prompt response! Much appreciated!

[Thulasinathan_M]

Happy that worked for you!!  🙂