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,
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..
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
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?
Yes it does, the count match up to the total events
https://imgur.com/a/5IfRM
ok ..now check for lookup count :
| inputlookup hp_message |stats count by ipexist
Here you go! It could only count source_IP that is contained in the lookup table
https://imgur.com/a/Ql0Jq
but lookup has null 'ipexist' present which we have converted into source_IP1 but I am not able to see there
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
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
or do you want to replace severity_level
and severity
from lookup?
The OUTPUT was just trying to rename them. The values for the two fields will remain as it is from the dataset.
<snip> | fillnull ipexist value=unknown | lookup yourlookup ipexits output yourfields | </snip>
Hope this would work.
I tried using fillnull before on "ipexist" but it would not display the other two output-ed lookup field
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.
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!
@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.
Ah sorry I didn't know the screenshot wasn't working. Thanks for the heads up!
Hi LeeZeeYuen,
Are you using ipexist field for mapping in lookup?
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