Splunk Search

Splunk Lookup compare with index

Krish14
Explorer

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!


Labels (1)
0 Karma
1 Solution

Thulasinathan_M
Communicator

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

 

View solution in original post

Thulasinathan_M
Communicator

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

 

0 Karma

Krish14
Explorer

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

 

0 Karma

Thulasinathan_M
Communicator

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
Explorer

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

0 Karma

Thulasinathan_M
Communicator

Happy that worked for you!!  🙂

Get Updates on the Splunk Community!

Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...

Welcome to the Splunk Community!

(view in My Videos) We're so glad you're here! The Splunk Community is place to connect, learn, give back, and ...

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...