topic Re: Why is the lookup command not giving results that exist in the lookup table? in Splunk Search

Why is the lookup command not giving results that exist in the lookup table?

patelaa — Tue, 25 Jul 2017 21:18:11 GMT

I have a lookup table with user data called id_lookup.csv

username,hostname,ip
user1,computer1,1.1.1.1
user2,computer2,2.2.2.2
user3,computer3,3.3.3.3

I use that lookup table to help populate identity data for a search like this

index=myindex sourcetype=mysourcetype
| search username=*
| lookup local=true id_lookup.csv username OUTPUT hostname,ip
| table hostname, ip, username

The issue is that the table of results doesn't get populated with the 2 fields I'm searching in the lookup for all the results when the users I'm searching the lookup table for are definitely in there.

So my results will look like this:

hostname,ip,username
 , ,user1
computer2,2.2.2.2,user2
 , ,user3

But if I search the lookup table using inputlookup

| inputlookup id_lookup.csv
| search username="user1"

I get the results

username,hostname,ip
user1,computer1,1.1.1.1

And when running a 1 off search using the lookup command for further testing I get the same inconsistent results

| makeresults
| eval username="user1"
| lookup local=true id_lookup.csv username OUTPUT hostname, ip
| table hostname, ip, username

Which gives

hostname,ip,username
 , ,user1

All the fields match up, permissions check out, transforms.conf looks right for that particular lookup stanza.

Does anyone know what else I can do to troubleshoot or know if this is a possible bug? The only thing I can think of is the csv file is fairly large but it still doesn't make sense why it would return the results for some entries and not others.

Re: Why is the lookup command not giving results that exist in the lookup table?

woodcock — Tue, 25 Jul 2017 22:12:19 GMT

You probably have a malformed CSV file; once the lookup hits a bad row, it bails out completely. You can use this as a starting point:
https://answers.splunk.com/answers/306569/corrupt-csv-header-how-to-find-the-corrupted-csv.html#answer-306586

Re: Why is the lookup command not giving results that exist in the lookup table?

DalJeanis — Wed, 26 Jul 2017 01:01:55 GMT

@patelaa - you can test @woodcock's idea by just creating another lookup table and seeing if your results are consistent. I'd suggest using the username from the first table as your input (but nothing else), and a single fixed output column. If the problem disappears, then he nailed it. If the problem persists, then it may be something wonky about the keys, or it may be something else that isn't obvious (like one of the letters is in a cyrillic alphabet or something...)

Re: Why is the lookup command not giving results that exist in the lookup table?

patelaa — Wed, 02 Aug 2017 19:04:34 GMT

Well that CLI command didn't seem to find anything but creating a new lookup with just a single row with the user in question seems to work.

I did try removing the lookup table and rebuilding it with fresh data which brought the size down to around 80mb from the several hundred it was before but I still have the same issue.