Splunk Search
Highlighted

Why is my search unable to match csv data against indexed events?

Contributor

Hi,

I am trying to search a list of IP's against the data being sent by the firewall. Since the number of IP's is large, I thought of using a CSV lookup to make things easy but the search is not going as expected.

CSV file has only one column for "src", e.g.

src
1.1.1.1
2.2.2.2
3.3.3.3
4.4.4.4

Created a new lookup table file and imported this CSV. Gave the app context as "Search" and made sure "search" app has full permissions. CSV is saved at /opt/splunk/etc/apps/search/lookups/firewall_whitelist.csv

The field in the indexed data which holds the source ip is "src" and that matches the column name given in the CSV file. To return indexed events where any of the IP's listed in the file are seen I used the following search but it does not produce any output.

sourcetype="firewall:syslogs" [|inputlookup /opt/splunk/etc/apps/search/lookups/firewall_whitelist.csv |table src ]

Also tried using | fields srcbut same result. search just ends with "No results found."

When I inspect the job, I do see two messages. one is info : The specified search will not match any events and another is warn : [subsearch]: The lookup table '/opt/splunk/etc/apps/search/lookups/firewall_whitelist.csv' is invalid.

Is the search incorrect or something wrong with the CSV itself?

Thanks in advance for any ideas/suggestions you might have.

~ Abhi

0 Karma
Highlighted

Re: Why is my search unable to match csv data against indexed events?

SplunkTrust
SplunkTrust

Try this

sourcetype="firewall:syslogs" [| inputlookup firewall_whitelist.csv |fields src | format]

View solution in original post

Highlighted

Re: Why is my search unable to match csv data against indexed events?

Contributor

Thanks jkat54.

This works great. Getting the results now. one more follow up question: Is it also possible to specify a particular splunk field to be matched against this data? Because I believe the above search is going to match the IP against all the fields from the indexed data correct? If I want to get the events only if these IP's match the splunk field named "src", is that possible?

~ Abhi

Highlighted

Re: Why is my search unable to match csv data against indexed events?

SplunkTrust
SplunkTrust

Given your example data, the search I shared above should "unpack" to this:

sourcetype="firewall:syslogs" ( (src=1.1.1.1) OR (src=2.2.2.2) OR (src=3.3.3.3) OR (src=4.4.4.4))

If you had more than one column coming from your lookup like this:

usr,src
joe,1.1.1.1
bob,2.2.2.2
susan,3.3.3.3
larry,4.4.4.4

And you used a search like this:

sourcetype="firewall:syslogs" [| inputlookup firewall_whitelist.csv |fields usr src | format]

That would unpack like this:

sourcetype="firewall:syslogs" ( (usr=joe AND src=1.1.1.1) OR (usr=bob AND src=2.2.2.2) OR (usr=susan AND src=3.3.3.3) OR (usr=larry AND src=4.4.4.4))
0 Karma
Highlighted

Re: Why is my search unable to match csv data against indexed events?

Contributor

Thank you. That helps.

Highlighted

Re: Why is my search unable to match csv data against indexed events?

SplunkTrust
SplunkTrust

You need to refer your lookup table file just by it's name, not it's path.