Splunk Search

How to fill empty field values to 0 in Splunk ?

nilbak1
Communicator

I have data in below format in Splunk
where I extracted this as Brand,Files,Size.

Now at some places, where size is showing empty, I want to replace them with 0.
alt text

I have used | fillnull value 0.
| eval Size=if(isnull(Size), "0", Size)

But still values are showing empty.

I have used below query

index=*** host=*** source=****
| rex field=_raw max_match=0 "(?[a-z]+),(?\w+\-?\d?.*)\,(?\d?.*)"
| table Brand,Size,Files
| eval _counter = mvrange(0,mvcount(Brand))
| stats list(*) as * by _counter
| foreach * [ eval <> = mvindex('<>' , _counter)]
| xyseries Brand Files Size
| transpose 0 header_field=Brand column_name=Files | fillnull value=0

and the result i m getting as like belowalt text

After using | fillnull value 0,
I am getting 0 for those files which are not there for 2nd,3rd,4th brands.
But not where size is showing empty.

Can anyone help me with this ?

1 Solution

woodcock
Esteemed Legend

The problem is that there are 2 different nullish things in Splunk. One is where the field has no value and is truly null. The other is when it has a value, but the value is "" or empty and is unprintable and zero-length, but not null. What you need to use to cover all of your bases is this instead:

... | foreach Every Field That Might Be Empty Listed Here [ eval <<FIELD>> = if(isnull(<<FIELD>>) OR len(<<FIELD>>)==0, "0", <<FIELD>>) ]

You can test this like this:

|makeresults
| eval notNULL=""
| fillnull value=0 notNULL

Versus this:

|makeresults
| eval notNULL=""
| foreach notNULL [ eval <<FIELD>> = if(isnull(<<FIELD>>) OR len(<<FIELD>>)==0, "0", <<FIELD>>) ]

Of course, this only works if it is empty; very occasionally you may find a field with only whitespace in it. For those cases, you can do this:

... | foreach Every Field That Might Be Empty or Have Only Whitespace Listed Here [ rex field=<<FIELD>> mode=sed "s/^[\r\n\s]+$//" | eval <<FIELD>> = if(isnull(<<FIELD>>) OR len(<<FIELD>>)==0, "0", <<FIELD>>) ]

View solution in original post

woodcock
Esteemed Legend

The problem is that there are 2 different nullish things in Splunk. One is where the field has no value and is truly null. The other is when it has a value, but the value is "" or empty and is unprintable and zero-length, but not null. What you need to use to cover all of your bases is this instead:

... | foreach Every Field That Might Be Empty Listed Here [ eval <<FIELD>> = if(isnull(<<FIELD>>) OR len(<<FIELD>>)==0, "0", <<FIELD>>) ]

You can test this like this:

|makeresults
| eval notNULL=""
| fillnull value=0 notNULL

Versus this:

|makeresults
| eval notNULL=""
| foreach notNULL [ eval <<FIELD>> = if(isnull(<<FIELD>>) OR len(<<FIELD>>)==0, "0", <<FIELD>>) ]

Of course, this only works if it is empty; very occasionally you may find a field with only whitespace in it. For those cases, you can do this:

... | foreach Every Field That Might Be Empty or Have Only Whitespace Listed Here [ rex field=<<FIELD>> mode=sed "s/^[\r\n\s]+$//" | eval <<FIELD>> = if(isnull(<<FIELD>>) OR len(<<FIELD>>)==0, "0", <<FIELD>>) ]

mschiesser
Observer

Thanks - that helped a lot. One note: At least since Splunk 7.x you can use `trim` instead of `rex`:

... | foreach Every Field That Might Be Empty or Have Only Whitespace Listed Here [ eval <<FIELD>> = if(isnull(<<FIELD>>) OR len(trim(<<FIELD>>))==0, "0", <<FIELD>>) ]

Also, in case you want to apply it for all fields, you can use `*`:

... | foreach * [ eval <<FIELD>> = if(isnull(<<FIELD>>) OR len(trim(<<FIELD>>))==0, "0", <<FIELD>>) ]

 

0 Karma

nilbak1
Communicator

Hi @woodcock

Thanks for your suggestions.
I tried both of them, but they are not working.

0 Karma

woodcock
Esteemed Legend

What are you field names?

0 Karma

nilbak1
Communicator

Brand, Files and Size

0 Karma

nilbak1
Communicator

@woodcock

I used below
| foreach * [ eval <<FIELD>> = if(isnull(<<FIELD>>) OR len(<<FIELD>>)==0, "0", <<FIELD>>) ]

And finally it worked
Earlier I used Size which didn't worked instead of *

Thanks once again

0 Karma

woodcock
Esteemed Legend

Be sure to come back and click Accept to close the question and also UpVote any helpful comments or answers.

0 Karma

nilbak1
Communicator
0 Karma

woodcock
Esteemed Legend

You posted the wrong URL here (it is a link to THIS post).

0 Karma

nilbak1
Communicator
0 Karma

dindu
Contributor

Hi,

You probably have the fields as not null.
It usually will be a white space.Check whether its whitespace using the following command

   |eval fieldLength=len(Size)

If you have white space, replace the if clause as below or use replace command to replace white space to null

  | eval Size=if(isnull(Size),"0",if(Size="  ","0",Size))

nilbak1
Communicator

Hi @dindu
I tried above suggestions but it did not worked .
Might be because field is string.

I tried to convert it into number using convert but that also not worked.

Attached screenshot.

0 Karma

nilbak1
Communicator

@dindu
Since there are multiple values for "Size" field.
Is there a way to check length here ?

0 Karma

dindu
Contributor

Hi,

The below command will work.

|eval fieldLength=len(your_field_name)
0 Karma

thuhuongle
Explorer

,Hi,
I think you just missing to tell it what is the field you want fillnull to work on.

try|fillnull value=1 [] []
Your dataset dont not have any column name test_field, so they are all null value. After execute this command, your test_field will be filled with 1.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Using the Splunk Threat Research Team’s Latest Security Content

REGISTER HERE Tech Talk | Security Edition Did you know the Splunk Threat Research Team regularly releases ...

SplunkTrust | 2024 SplunkTrust Application Period is Open!

It's that time again, folks! That's right, the application/nomination period for the 2024 SplunkTrust is ...