Lookup could not display field value that is null

[LeeZeeYuen]

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,

[493669]

firstly we have made null 'ipexist' values into source_IP1 in both index as well as in lookup. and now we have join so it will join correctly and then we just change source_IP1 as source_IP while displaying....so i dont think it will give wrong output..

[LeeZeeYuen]

Ahhhh, I get it now. Haha sorry kinda slow had to googled what eval if statement does. Yeah the logic makes sense but apparently it is not displaying any of the field in the events

https://imgur.com/a/zctvw

[493669]

index="printerlinuxlog"|eval ipexist=if(isnull(ipexist),"source_IP1",ipexist)|stats count by ipexist

Does this gives all ipexist field contain either source_IP1 or source_IP value?

[LeeZeeYuen]

Yes it does, the count match up to the total events
https://imgur.com/a/5IfRM

[493669]

ok ..now check for lookup count :

| inputlookup hp_message |stats count by ipexist

[LeeZeeYuen]

Here you go! It could only count source_IP that is contained in the lookup table
https://imgur.com/a/Ql0Jq

[493669]

but lookup has null 'ipexist' present which we have converted into source_IP1 but I am not able to see there

[LeeZeeYuen]

If I were to use the eval command you suggested, it would replace the the lookup's ipexist field for all the null value
https://imgur.com/a/Khu7p

[493669]

Now if you run :
| inputlookup hp_message |eval ipexist=if(isnull(ipexist),"source_IP1",ipexist)|outputlookup hp_message
it will store back to lookup.
then you should be able to join with outcome message and ipexist to get the output..
also I am assuming there is no null value in outcome and message field

[493669]

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

[LeeZeeYuen]

The OUTPUT was just trying to rename them. The values for the two fields will remain as it is from the dataset.

[nabeel652]

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

Hope this would work.

[LeeZeeYuen]

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

[DalJeanis]

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.

[LeeZeeYuen]

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!

[niketn]

@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.

[LeeZeeYuen]

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

[p_gurav]

Hi LeeZeeYuen,

Are you using ipexist field for mapping in lookup?

[LeeZeeYuen]

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