topic Re: After setting up and defining a CSV lookup in Splunk, why is lookup returning a field with blank values in search results? in Splunk Search

After setting up and defining a CSV lookup in Splunk, why is lookup returning a field with blank values in search results?

alexandermunce — Tue, 29 Sep 2020 12:06:59 GMT

I have set up a lookup CSV which looks something like:

product, meaningful_product
product_1, "Meaningful Name 1"
product_2, "Meaningful Name 2"

etc..
I have added the lookup file to the Splunk Lookup Table files as below;

/opt/splunk/etc/apps/search/lookups/product_names.csv

I have added a Lookup Definition for the CSV as below:

Name                  Type      Supported Fields
vgate_prod_names      file      product,meaningful_product

Now that I have completed all of the above steps, I am testing this in one of my searches in a similar method to the below:

search query | lookup vgate_prod_names product AS product OUTPUT meaningful_product AS product | more search

I have also tried this syntax:

search query | lookup vgate_prod_names product OUTPUT meaningful_product AS product | more search

However in both instances, the search returns all of the results it should, however the product field contains only blank values.

Side note - if I am to run the following query:

 search query | lookup vgate_prod_names product OUTPUTNEW meaningful_product AS product_new | more search

Which in the above, product_new is a new field name - my search returns a new field named product_new with all of the values which I was expecting to be set for the product field.

Does there need to be a lookup value in my CSV lookup table for EVERY possible value that can be returned in the product field?

To generate my product field value list I generated a count of all product field values for the year to date - I suppose there may be some fields which were not returned in this original search. Any way around this? The search took some time obviously.

What am I doing wrong??

Re: After setting up and defining a CSV lookup in Splunk, why is lookup returning a field with blank values in search results?

somesoni2 — Tue, 13 Dec 2016 22:33:45 GMT

Yes, if you're overriding existing field name, and there is no match, it'll overwrite that field with null. A better approach will be like this

search query | lookup vgate_prod_names product AS product OUTPUT meaningful_product AS product_new | eval product=coalesce(product_new,product) | fields - product_new | more search

Re: After setting up and defining a CSV lookup in Splunk, why is lookup returning a field with blank values in search results?

amielke — Wed, 14 Dec 2016 10:17:38 GMT

check with " | inputlookup name_lookupfile " is the file correct readed.

Re: After setting up and defining a CSV lookup in Splunk, why is lookup returning a field with blank values in search results?

alexandermunce — Tue, 29 Sep 2020 12:07:30 GMT

With some guidance from the comments above I have been able to resolve my original issue and implement a working solution.

The reason that all lookup OUTPUT values for the product field were being returned as NULL is due to the fact that the CSV lookup table did not contain ALL possible values for the product field that were returned by my search - and also did not cater for any NULL values.

By implementing the following commands into my search query I have been able to successfully return the replacement lookup values into the correct field (product).

lookup vgate_prod_names product as product OUTPUT meaningful_product | eval product=coalesce(meaningful_product,product) | fields - meaningful_product

Above I am looking up the product field values against the list of values in the product column of my CSV file, and for any match, I am returning the value from the meaningful_product column of the CSV as a new field named meaningful_product.

Then to address the original issue of simplifying the value names in the product field, I am using the eval command against the product field combined with the coalesce function which will replace any value of the product field with associated meaningful_product value, or if there is no match/null, then replacing with the original product value.

The fact that some values of the product field remain unchanged is a non issue in my case as this will equate to <1% of the result data.

Happy Splunking!

Re: After setting up and defining a CSV lookup in Splunk, why is lookup returning a field with blank values in search results?

alexandermunce — Mon, 19 Dec 2016 23:54:34 GMT

All were correct - turns out it was an issue with the lookup table data as summarised in the answer below 🙂