Splunk Search
Highlighted

Splunk is only reading some records from a lookup and leaving others out. Any suggestions why?

Path Finder

I have a CSV file uploaded as a lookup. I am using the userID from my search with the lookup, but for some reason, the lookup is not enriching all of the search data. It will work for some search results and not others. I have checked the UserID's of those that are not being found and those that are and they are all part of the CSV. Has anyone had a similar problem and know how to fix?

The CSV is comprised of the following fields:
UserID,Name,LastName,FirstName,City,Address,PostalCode,JobTitle,Center,Department

0 Karma
Highlighted

Re: Splunk is only reading some records from a lookup and leaving others out. Any suggestions why?

Legend

Lookups are case-sensitive by default. Could this be the problem?

0 Karma
Highlighted

Re: Splunk is only reading some records from a lookup and leaving others out. Any suggestions why?

Path Finder

Thanks Iguinn, I did not know that about the lookups.

My UserID is made up of a letter and 4 numbers eg: X1234 or x1234. In my events the letter can be uppercase or lowercase and in some cases it can have both.

I changed the lookup UserID letter to lowercase and this improved the results dramatically, but there is still some of the data not being looked up.

Could this be due to the events that return a UserID with both uppercase and lowercase letters? if so do you have any suggestions?

0 Karma
Highlighted

Re: Splunk is only reading some records from a lookup and leaving others out. Any suggestions why?

SplunkTrust
SplunkTrust

Since you already updated the lookup to use lowercase UserID, just update the user id field in the events to be in lowercase before the lookup. Kinda like this

your base search | eval UserID=lower(UserID) | lookup yourlookup.csv UserID ....
0 Karma
Highlighted

Re: Splunk is only reading some records from a lookup and leaving others out. Any suggestions why?

Path Finder

Hi, I found a solution thanks to Iguinn. The lookup is case sensitive, so I changed my lookup csv data to lowercase and added the following command to my search which set any uppercase UserID events to lowercase too. This allowed the lookup to return data for all events.

|eval UserID=lower(UserID)

Hope this helps.

View solution in original post

0 Karma
Highlighted

Re: Splunk is only reading some records from a lookup and leaving others out. Any suggestions why?

Legend

You can also do this for your lookup, to make the match case INsensitive. But you can't do it from the user interface, you have to edit the configuration file directly. Add this to the stanza in transforms.conf

case_sensitive_match=false

Then you don't need to make the keys lower-case, etc. It does add a small amount of overhead to your search (but then so does the eval command). You can also match your lookup CIDR-aware, etc. when it tries to match. Take a look at the documentation here.

Highlighted

Re: Splunk is only reading some records from a lookup and leaving others out. Any suggestions why?

Path Finder

Thanks again Iguinn

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.