Splunk Search
Highlighted

How to fill empty field values to 0 in Splunk ?

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 ?

Highlighted

Re: How to fill empty field values to 0 in Splunk ?

New Member

,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 testfield, so they are all null value. After execute this command, your testfield will be filled with 1.

0 Karma
Highlighted

Re: How to fill empty field values to 0 in Splunk ?

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

Re: How to fill empty field values to 0 in Splunk ?

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
Highlighted

Re: How to fill empty field values to 0 in Splunk ?

Communicator

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

0 Karma
Highlighted

Re: How to fill empty field values to 0 in Splunk ?

Contributor

Hi,

The below command will work.

|eval fieldLength=len(your_field_name)
0 Karma
Highlighted

Re: How to fill empty field values to 0 in Splunk ?

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

Highlighted

Re: How to fill empty field values to 0 in Splunk ?

Communicator

Hi @woodcock

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

0 Karma
Highlighted

Re: How to fill empty field values to 0 in Splunk ?

Esteemed Legend

What are you field names?

0 Karma
Highlighted

Re: How to fill empty field values to 0 in Splunk ?

Communicator

Brand, Files and Size

0 Karma