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]

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!

[LeeZeeYuen]

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

[493669]

your image is broken ...not able to see

[LeeZeeYuen]

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

[493669]

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

[LeeZeeYuen]

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

[493669]

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?

[LeeZeeYuen]

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

[493669]

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.

[LeeZeeYuen]

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

[493669]

ok ..so your lookup contains two entry one with ipexist containing value and one with null ....from where you are populating this lookup ?

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

here it matches with lookup containing value for ipexist field but one with null value in ipexist has correct severity_level but its not matched...so either you need to remove this duplicate entry .....so how are you populating this lookup?

[LeeZeeYuen]

Yes ipexist have value of "source_IP" and null. The lookup is a csv file. If the lookup command for ipexist as ipexist is not used, there will be duplicate entry. But when that command is used, it would not display the value of "severity" and "severity_level" for those event that do not have "ipexist"

[493669]

so does your index also has null values ofipexist field in data ?
is it not possible to edit your csv lookup?

[LeeZeeYuen]

"ipexist" field in the index do not have null in its value (as I do not know how to use regex to extract empty value)

It is possible to edit the csv lookup to certain extents

[493669]

why you have keep null value of ipexist field in lookup? how this lookup has been created and how the values are populated in that?
if you try
index="printerlinuxlog"|stats count(ipexist)
what is the count does it match with your event count?

[LeeZeeYuen]

I was trying to see if I could match the data to the events to get more accurate "severity_level" and "severity" but it seems like Splunk take empty value as a blank statement. I was hoping that if the events do not contain the string "source_IP" it would be null when referencing to the dataset

To answer the count question, it doesn't match as it only count the events with the string "source_IP". Sorry I am kinda new to the field extraction and lookup

[493669]

if you try first to fill your null ipexist values with some data and store it in lookup:

| inputlookup hp_message |eval ipexist=if(isnull(ipexist),"source_IP1",ipexist)|outputlookup hp_message 
it will make null values assigned to "source_IP1"

now run this:

 index="printerlinuxlog"|eval ipexist=if(isnull(ipexist),"source_IP1",ipexist)| 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 what output you are receiving.

[LeeZeeYuen]

These are the output I receive for CCC Logging events
https://imgur.com/a/im0aD

The ipexist field for events without source_IP string are filled with source_IP1

[493669]

now if you want you can make source_IP1 as source_IP by running below :

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

[LeeZeeYuen]

If I were to understand the final pipe, that would mean the command will turn source_IP1 into source_IP right? But wouldn't it provide wrong type of severity level of certain events due to it "containing" source_IP rather than a null value?

But this is the output I receive after using the command you provided.
https://imgur.com/a/weGhO