Getting Data In

Need help to compare a CSV file with an index

jip31
Motivator

Hi,
I need to compare the field host of my CSV file with the field host of my index.
I used the search below but I have no results.
What is wrong, please!

| inputlookup test.csv 
| lookup test.csv HOSTNAME as host output SITE STATUS 
| join host 
    [ search index=tutu] 
| stats values(SITE) as SITE, values(STATUS) as STATUS by host
0 Karma
1 Solution

arjunpkishore5
Motivator

This is doing a join on an entire index which is unnecessary. Here is how you want to do it.

index=tutu 
    [| inputlookup test.csv 
    | rename HOSTNAME as host 
    | fields host] 
| lookup test.csv HOSTNAME as host output SITE STATUS 
| stats values(SITE) as SITE, values(STATUS) as STATUS by host

line 1 is fetching data from your index tutu
line 2-4 is a subquery which filters your data to only those hosts present in the file
line 5 is then doing a lookup and fetching the site and status and line 6 is your stats which is summarizing these

Hope this helps.
Cheers

View solution in original post

0 Karma

harsmarvania57
SplunkTrust
SplunkTrust

Hi,

Please try below query.

index=tutu
| stats count by host
| fields - count
| inputlookup test2.csv append=t
| eval host=lower(host)
| stats count, values(SITE) as SITE, values(STATUS) as STATUS by host

If you want to see only those host which are matching with lookup only then you can try below query.

index=tutu
| stats count by host
| fields - count
| lookup test2.csv host OUTPUT
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@jip31

Can you please share your expected output?
Meanwhile try by replacing lookup command with below one.

| lookup test.csv HOSTNAME as host OUTPUTNEW HOSTNAME as host,SITE, STATUS

0 Karma

arjunpkishore5
Motivator

This is doing a join on an entire index which is unnecessary. Here is how you want to do it.

index=tutu 
    [| inputlookup test.csv 
    | rename HOSTNAME as host 
    | fields host] 
| lookup test.csv HOSTNAME as host output SITE STATUS 
| stats values(SITE) as SITE, values(STATUS) as STATUS by host

line 1 is fetching data from your index tutu
line 2-4 is a subquery which filters your data to only those hosts present in the file
line 5 is then doing a lookup and fetching the site and status and line 6 is your stats which is summarizing these

Hope this helps.
Cheers

0 Karma

jip31
Motivator

thanks even if the search is very long

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...