Find all fileds by file list

[jokovitch]

I have a data in Splunk like

Fname	Lname	Country
fname1	lname1	USA
fname2	lname2	USA
fname3	lname3	USA

And I have file in Splunk server that contains in each line  a name:

MyFile.csv:

Name

fname1

lname3

fname123

I want to present only the lines that in the Name into CSV if equal to Fname or Lname in my index

In my example the result need to be

Fname	Lname	Country
fname1	lname1	USA
fname3	lname3	USA

How can I do that?

[manjunathmeti]

hi @jokovitch,

You can export data in Splunk to a CSV file. This will write CSV file in directory $SPLUNK_HOME/var/run/splunk/csv.

index=index | fields Name | dedup Name | outputcsv MyFile.csv

Check this for more info: https://docs.splunk.com/Documentation/Splunk/8.2.1/SearchReference/Outputcsv

[jokovitch]

I want to show the results of this compare in Splunk, not export it to file.

Note the I want to import data from file and compare it to data in Splunk

[manjunathmeti]

Then you can save MyFile.txt as csv file is in the $SPLUNK_HOME/etc/system/lookups or $SPLUNK_HOME/etc/apps/APP_NAME/lookups directory.

MyFile.csv:
Name
Bill
Tramp
BONE

And run the below search:

index=index [| inputlookup MyFile.csv | format] 

If this reply helps you, a like would be appreciated.

[jokovitch]

index=MyIndex      work

index=MyIndex [| inputlookup MyFile.csv | format] 

 return my no results (blank)

And after  I will load it how can I show only lines that contains Name into MyFile.csv?

thanks!

[manjunathmeti]

Make sure the field Name is there in the index.
Also, run sub-search query to see if you are getting output.

| inputlookup MyFile.csv | format

[jokovitch]

my csv looks like:

Name

name1

name2

name3

I use this query

index=MyIndex [| inputlookup MyFile.csv | format] 

I see that Splunk is think (If I write a filename that not exist that return immediately) .

How do I need to fix that query ?

index=MyIndex [| inputlookup MyFile.csv | IP] 

I didn't success

[manjunathmeti]

Can you post some samples of data wiith fields in both CSV file and index?

[jokovitch]

Name	Address	Country
Jhone	ADDR1	USA
Bill	ADDR2	USA
Tramp	ADDR13	USA

For example  :

index="main" Name="Bill"

Name	Address	Country
Bill	ADDR2	USA

MyFile.csv:

Name

name1

name2

Tramp

The result need to be

Name	Address	Country
Tramp	ADDR13	USA

[manjunathmeti]

First, you need to copy/create MyFile.csv file is in the /opt/splunk/bin/etc/system/lookups or /opt/splunk/bin/etc/apps/<APP_NAME>/lookups directory.

Then, check if the CSV data is accessible via search.

| inputlookup MyFile.csv

And run the below search:

index=main [| inputlookup MyFile.csv | fields Name | format | fields search]

[jokovitch]

The csv file is in

/opt/splunk/bin/etc/system/lookups

Then I run

| inputlookup MyFile.csv | fields Name 

I got all the name in csv

Then  I tried

index=main [| inputlookup MyFile.csv | fields Name | format | fields search]

it's take few seconds and them I got "no results"

Where in that query should my choose to field for compare into my index?

[manjunathmeti]

What is the output of below search:

| inputlookup MyFile.csv | fields Name | format | fields search

[jokovitch]

((Name="name1") OR (Name="name2") ) ........

I want to search in my index where Name in CSV equal to Fname or Lname in my index

[manjunathmeti]

Then try this:

index=main [| inputlookup MyFile.csv | eval Fname=Name, Lname=Name | fields Fname, Lname | format | eval search=replace(search, "AND", "OR") | fields search]

[jokovitch]

I tried your query but I  got no results...

 When I run

index= main Fname="fname1"

I got good result

[manjunathmeti · Accepted solution]

Try:

index=main Fname IN([| inputlookup MyFile.csv | stats values(Name) as Name | return $Name])) OR Lname IN([| inputlookup MyFile.csv | stats values(Name) as Name | return $Name]))

If this reply helps you, a like would be appreciated.

[jokovitch]

Work, thanks!

Just drop your double ")" after

return $Name]))