topic Re: How do I get Automatic Lookup to handle null value lookups? in Splunk Search

How do I get Automatic Lookup to handle null value lookups?

khudson3 — Fri, 20 Sep 2019 10:33:48 GMT

My automatic lookup csv file is using say 2 columns; Col1 & Col2. Row entries are 'Success' & 'Failure' in Col1. Col 2 has the value / char '1' & null / no value entry in the opposite cells. I want searches to lookup and replace null with value=Failure. I'm seeing the mapping for 'Success' working but nothing for null / no cell value entry. Is there a csv file value / character entry that maps to null search value?

Re: How do I get Automatic Lookup to handle null value lookups?

richgalloway — Fri, 20 Sep 2019 14:04:58 GMT

The easiest thing to do is change the lookup csv file to not have an empty Col2.

Re: How do I get Automatic Lookup to handle null value lookups?

khudson3 — Fri, 20 Sep 2019 14:09:28 GMT

Doesn't have an empty Col2 it has 1 & null cells. 1 is working for Success whereas null is not being mapped.

Re: How do I get Automatic Lookup to handle null value lookups?

khudson3 — Fri, 20 Sep 2019 14:14:06 GMT

Col1 Col2
Success 1
Failure

Re: How do I get Automatic Lookup to handle null value lookups?

richgalloway — Fri, 20 Sep 2019 14:41:07 GMT

null==empty. If you can change the CSV file to have

Col1,Col2
Success,1
Failure,0

then you'll avoid convoluted SPL.

Re: How do I get Automatic Lookup to handle null value lookups?

HiroshiSatoh — Fri, 20 Sep 2019 15:27:34 GMT

Isn't it possible to use calculated fields instead of "automatic lookup"?

Re: How do I get Automatic Lookup to handle null value lookups?

khudson3 — Mon, 23 Sep 2019 06:46:20 GMT

Null is being returned for the search that the lookup is running on, so 0 cannot be used and the fullnull command can only be executed after the search. Need to know whether there is a csv mapping for null char.

Re: How do I get Automatic Lookup to handle null value lookups?

DavidHourani — Mon, 23 Sep 2019 07:11:02 GMT

yeah that's a better idea if it's just success/failure 🙂

Re: How do I get Automatic Lookup to handle null value lookups?

khudson3 — Mon, 23 Sep 2019 07:36:01 GMT

The real life scenario is mapping to lists from a specification, where there will be more than just two outcomes:

Col1,Col2
No failure,null
Source application or not sent,0
System,1

Re: How do I get Automatic Lookup to handle null value lookups?

richgalloway — Mon, 23 Sep 2019 12:17:46 GMT

Have you tried using isnull to test the output of the lookup? Share your query and I may be able to be more specific?

Re: How do I get Automatic Lookup to handle null value lookups?

khudson3 — Mon, 23 Sep 2019 14:16:23 GMT

Yes I've tried issull. My search events are returning true null values, but the auto lookup is not applying / handling the transformation and the values remain null. In the auto lookup csv I've used entries ="",empty etc.

If I use | fillnull value=- or equiv then I see the null automatic lookup new fields but the data is still null.

The search query does not really have a bearing on the question as it contains no transformations.

Re: How do I get Automatic Lookup to handle null value lookups?

DavidHourani — Mon, 23 Sep 2019 14:46:05 GMT

why not just use fillnull first and follow that up with a lookup ?

Re: How do I get Automatic Lookup to handle null value lookups?

khudson3 — Tue, 24 Sep 2019 06:47:34 GMT

Yes I could easily workaround the issue with fullnull or eval etc. But I wanted to understand whether there was a direct csv mapping that could work, as it seems quite poor if automatic lookup cannot handle null values appropriately.

Re: How do I get Automatic Lookup to handle null value lookups?

woodcock — Mon, 14 Oct 2019 23:38:27 GMT

You can add | fillnull value="Failure" after the lookup or you can create a lookup definition that sets a default value and use the lookup definition with the | lookup call (not the lookup file).