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!

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...