topic Re: Incomplete LOOKUP results in Splunk Search

Incomplete LOOKUP results

cormieja — Thu, 12 Sep 2013 19:48:11 GMT

Hi,

I read about many similar issues here, but I was not able to get a satisfying answer.
I am trying to use a lookup table, lut.csv, to add information to some events. That LUT is written over daily with an outputlookup. Some days, usually in streak of 2-3 days, the lookup will fail for most events.

My search looks like this:

(...) | table ___time, ID,  fieldA | lookup lut.csv ID OUTPUT fieldB

With inputlookup, I validated that for ID="banana", fieldB="yellow" in lut.csv. However, whenever I use lookup, fieldB will be empty.

Here is some information that may be relevant:

I'm using version 4.3.6
When it "fails", about 5-10% of ID will still be succesfully joined to the appropriate fieldB.
I tried the same search, specifying only one ID, it still couldn't join fieldB, but this time generated the following error: Empty csv lookup file (contains only a header) for table 'lut.csv': /opt/splunk/etc/apps/search/lookups/lut.csv (I confirm it is not empty)

Any idea what is the issue (and how to solve it)?

Thanks!

EDIT: This issue is exactly the same, but no answer 😞
http://answers.splunk.com/answers/78891/lookup-does-not-return-results-for-all-fields

Re: Incomplete LOOKUP results

yannK — Thu, 12 Sep 2013 20:52:22 GMT

Are you using search-head pooling, using a bad NFS mount ?
Is your lookup file path (/opt/splunk/etc/apps/search/lookups/lut.csv) is using a symlink ?

Re: Incomplete LOOKUP results

rgonzale6 — Thu, 12 Sep 2013 22:07:39 GMT

Is ID extracting properly in 100% of your events?

Re: Incomplete LOOKUP results

cormieja — Fri, 13 Sep 2013 03:15:00 GMT

Yes. So when the lookup fails, my result looks like this, with an extracted value under ID:

_time ID fieldA fieldB
Sunday Banana Yellow [NULL]

Re: Incomplete LOOKUP results

iKate — Wed, 12 Mar 2014 12:21:40 GMT

@cormieja how did you solve the issue? I've faced the same problem.

What has helped me this time is recreation of lookup table. But I didn't realized the reason of the problem and cannot be sure it wouldn't repeat.

What I've also done is eliminated table command in the query that generates lookup table.
The search looked like:
| dbquery dbname " select * ...."
| table field1 field2 field3
| outputlookup file.csv

And now like:
| dbquery dbname " select field1, field2, field3 ...."
| outputlookup file.csv

Not sure this affected the lookup table format but I've read about some problems of dbquery and table command so..

Re: Incomplete LOOKUP results

slr — Tue, 27 Oct 2015 17:13:31 GMT

I had a lot of problems with the file's codification, and my issues seems to be the same that @cormieja had. Make sure that your file is UTF8 and the characters inside are properly written. Some times, when we save data inside the files, if you don't have a properly codification some characters could be "bad represented" and then, when Splunk try to read it we have issues like yours.

I hope this clue will be useful.

Regards.