topic Re: Lookup could not display field value that is null in Splunk Search

Lookup could not display field value that is null

LeeZeeYuen — Tue, 29 Sep 2020 17:49:46 GMT

I have a field called "ipexist" in the dataset that have two values; empty(Which is defaulted as null in Splunk) and a string value.

I want to use lookup command to obtain two other fields but strangely some events that have null value for ipexist could not display the said two other fields. Below is the sample event with the said fields.

Above the image you can see the top event does not have "severity" and "severity_level" field but the two below have it. I would like to know how to still display the fields despite having a null value for "ipexist"

Edit;
Updated image since the first screenshot had some issues

Update;
I forgot to mention that some events do not have the value "source_IP". The field "ipexist" uses "source_IP" as its value,

Re: Lookup could not display field value that is null

p_gurav — Wed, 24 Jan 2018 13:28:58 GMT

Hi LeeZeeYuen,

Are you using ipexist field for mapping in lookup?

Re: Lookup could not display field value that is null

niketn — Wed, 24 Jan 2018 20:21:57 GMT

@LeeZeeYuen, I think you would need to post your screenshot again for the community to help. You can upload to image sharing site and then add the link using image button while posting your comment/update to question.

Re: Lookup could not display field value that is null

LeeZeeYuen — Tue, 29 Sep 2020 17:50:14 GMT

Yes the command used for lookup is

index="printerlinuxlog"
| lookup hp_message outcome as outcome message as message ipexist as ipexist OUTPUT siem_severity as severity_level syslog_severity as severity

Re: Lookup could not display field value that is null

LeeZeeYuen — Thu, 25 Jan 2018 00:41:06 GMT

Ah sorry I didn't know the screenshot wasn't working. Thanks for the heads up!

Re: Lookup could not display field value that is null

DalJeanis — Thu, 25 Jan 2018 01:19:57 GMT

probably the most direct way to deal with it would be to do something like this before your lookup...

| eval ipexist=coalesce(ipexist,"")

... and set up the lookup table itself to have a blank instead of a NULL.

Re: Lookup could not display field value that is null

nabeel652 — Thu, 25 Jan 2018 01:51:09 GMT

<snip> | fillnull ipexist value=unknown | lookup yourlookup ipexits output yourfields | </snip>

Hope this would work.

Re: Lookup could not display field value that is null

LeeZeeYuen — Thu, 25 Jan 2018 02:35:14 GMT

Thank you for the suggestion but I tried it and it didn't work. The lookup table have blank value which Splunk comprehend it as italic null. The event would not display the the two output-ed fields. However, it did declare the null value of "ipexist" as blank.

This is the command used

The results:

Image shown that it display "ipexist=" but no signs of "severity" and "severity_level".

Thanks for the help!

Re: Lookup could not display field value that is null

LeeZeeYuen — Thu, 25 Jan 2018 02:36:43 GMT

I tried using fillnull before on "ipexist" but it would not display the other two output-ed lookup field

Re: Lookup could not display field value that is null

493669 — Thu, 25 Jan 2018 03:22:31 GMT

try below:

index="printerlinuxlog"|eval ipexist=coalesce(ipexist,"source_IP")| lookup hp_message outcome as outcome message as message ipexist as ipexist OUTPUT siem_severity as severity_level syslog_severity as severity

Let me know if it works!

Re: Lookup could not display field value that is null

LeeZeeYuen — Tue, 29 Sep 2020 17:50:17 GMT

Hello 493669,
I tried and it did display the OUTPUT field but it didn't match with the dataset

In the image, it shows that the event without the string "source_IP" having a "severity_level" of Medium while in fact the dataset meant it to be Low. Thanks for the answer though! Really appreciates it

Re: Lookup could not display field value that is null

493669 — Thu, 25 Jan 2018 05:47:39 GMT

your image is broken ...not able to see

Re: Lookup could not display field value that is null

LeeZeeYuen — Thu, 25 Jan 2018 05:51:09 GMT

Ah sorry, adding image url seems weird on Splunk. Is this image better?
https://imgur.com/a/Mp37w

Re: Lookup could not display field value that is null

493669 — Thu, 25 Jan 2018 05:55:07 GMT

Thanks..could you please provide your lookup table also..

Re: Lookup could not display field value that is null

LeeZeeYuen — Thu, 25 Jan 2018 05:59:29 GMT

Okay I will provide the message of CCC Logging Started with and without the source_IP as a sample.

This is the one with source_IP
https://imgur.com/a/cKbhM

This is the one without source_IP
https://imgur.com/a/HTzBw

Both are in the same dataset

Re: Lookup could not display field value that is null

493669 — Thu, 25 Jan 2018 06:10:32 GMT

ok so it seems your lookup table has null value for 'ipexist' field and not in index. if you
try below:

index="printerlinuxlog"|eval ipexist=coalesce(ipexist,"source_IP")| lookup hp_message outcome as outcome message as message  OUTPUT siem_severity as severity_level syslog_severity as severity

are you receiving any output?

Re: Lookup could not display field value that is null

LeeZeeYuen — Tue, 29 Sep 2020 17:50:20 GMT

Yes I am receiving output but instead of having one value for "severity_level" and "severity" I got two due to not enough mapping for accuracy. This is because the message extracted from the index for certain events are the same in dataset.

For example, two same message will have different severity_level as there are other conditions. In this case, the message for CCC Logging Started without source_IP have severity_level of Low while CCC Logging Started with souce_IP will have Medium severity_level

Sample for message="CCC Logging Started" image
https://imgur.com/a/fEtLj

Commands used are
index="printerlinuxlog"
|eval ipexist=coalesce(ipexist,"source_IP")
| lookup hp_message outcome as outcome message as message OUTPUT siem_severity as severity_level syslog_severity as severity
| search CCC

Re: Lookup could not display field value that is null

493669 — Thu, 25 Jan 2018 06:29:48 GMT

if you have severity_level and severity in your index already then why you are trying to join it with lookup? correct me if I am wrong.

Re: Lookup could not display field value that is null

493669 — Thu, 25 Jan 2018 06:35:58 GMT

or do you want to replace severity_level and severity from lookup?

Re: Lookup could not display field value that is null

LeeZeeYuen — Tue, 29 Sep 2020 17:50:23 GMT

Haha its alright. I am sorry if I do not understand your question but I do not have severity_level and severity in the index. Value for severity_level are like "Low", "Medium", "High" and "Critical"

As for severity, it's the type of severity. Examples of the values are "Notice", "Info" and "Warning".

By default the index would not have said fields. This is the sample image of fields available without using the lookup command,
https://imgur.com/a/rgLmq