How to fill empty field values to 0 in Splunk ?

[nilbak1]

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.

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 below

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 ?

[woodcock · Accepted solution]

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]

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>>) ]

[nilbak1]

Hi @woodcock

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

[woodcock]

What are you field names?

[nilbak1]

Brand, Files and Size

[nilbak1]

@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

[woodcock]

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

[nilbak1]

Done.
Can you also help me in answering

https://urldefense.proofpoint.com/v2/url?u=https-3A__answers.splunk.com_comments_813518_view.html&d=...

[woodcock]

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

[nilbak1]

oops, my bad.
This is the correct one.

https://answers.splunk.com/answers/812823/not-able-to-convert-field-from-string-to-number.html

[dindu]

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]

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.

[nilbak1]

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

[dindu]

Hi,

The below command will work.

|eval fieldLength=len(your_field_name)

[thuhuongle]

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