Are there any known problems regarding missing values in big lookup-files and is there a solution for it?

[christian_l]

Hi all,

I got a problem while performing a lookup at a csv-file.
In general the lookup works fine, but its missing some of the looked-up values.
Here is my lookup-relevant search-string:

  | lookup my_lookup number as lookup-number OUTPUT description | eval description = if(description="","no description",description)

I pipe the results into a table with number and description. In this table some of the description columns are empty, unless the value exists inside the lookup-file.
There are no special characters in the missing value. I moved the regarding row at the top and end of the file which doesn't impact the results. Other values above and below the problem value work good in the lookup. Also the problem-row works fine, if its the only row inside the lookup-file.
There are several of this "problem-rows" spread over the lookup-file. As in my eval defined the values don't seem to be read as empty values ( "" ), because there are not replaced with "no description".
The lookup-file itself has a 531K size and 12925 rows in 2 columns.

I read about the change of processing lookup-files here http://splunk-base.splunk.com/answers/8228/lookup-table-limits but I don't think this is relevant for my problem.

Are there any known problems regarding missing values in big lookup-files and is there a solution for it?

Thanks all
Christian

[christian_l · Accepted solution]

Problem solved!
I found some " in the lookup file. Looks like the splunk lookup gets in trouble if only one " is appearing per lookup-file line.
It results in strange behaviour as described above. The lines between a first single-" and the next " getting an null content.
After letting sed delete all the " in the file, the lookup works fine!
As I use a script to fill the lookup files it was easy to add the following sed command:

sed -i 's/"//g' $LOOKUP_FILE

Now every future lookup-file should be compatible.

[christian_l]

Here are two examples from the lookup-file:

Working rows:

P 012035,WLAN Problems
P 012049,PJ: APC Smart UPS 2200

Problem rows:

P 011678,Testing
P 011115,PJ: Checkpoint Appliance Upgrade

Hint: The file is sorted by the P-number. My tests showed me there is no relevance if the problem row is at the top or bottom of the file.

[Strype]

Can you show an example of a working row vs. the problem row?

[Strype]

Okay well in my experience (which is short) the data would be missing from ALL rows below a missing comma or a space after a row. So my guess is search logic.