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

How to fill empty field values to 0 in Splunk ?

nilbak1 — Fri, 20 Mar 2020 09:52:23 GMT

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

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 ?

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

thuhuongle — Wed, 30 Sep 2020 04:40:15 GMT

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

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

dindu — Fri, 20 Mar 2020 11:41:53 GMT

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

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

woodcock — Fri, 20 Mar 2020 13:00:18 GMT

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

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

nilbak1 — Mon, 23 Mar 2020 10:49:21 GMT

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.

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

nilbak1 — Tue, 24 Mar 2020 03:28:31 GMT

Hi @woodcock

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

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

woodcock — Tue, 24 Mar 2020 03:47:01 GMT

What are you field names?

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

nilbak1 — Tue, 24 Mar 2020 04:35:35 GMT

Brand, Files and Size

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

nilbak1 — Tue, 24 Mar 2020 09:56:17 GMT

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

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

nilbak1 — Tue, 24 Mar 2020 10:22:36 GMT

@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

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

dindu — Tue, 24 Mar 2020 11:34:49 GMT

Hi,

The below command will work.

|eval fieldLength=len(your_field_name)

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

woodcock — Tue, 24 Mar 2020 14:14:25 GMT

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

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

nilbak1 — Wed, 25 Mar 2020 10:09:14 GMT

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=DwMCaQ&c=eIGjsITfXP_y-DLLX0uEHXJvU8nOHrUK8IrwNKOtkVU&r=d1I-PtwBnEn5KCQBE4yXGX3olOomGKItrlhcKT_6d7c&m=oum9sTGS9sYRLXd_wGXOvpKjBif-9uMnxOWlgcelcy8&s=ec4M31qf6JglmGTGvwU5DsAvNZ3FaYoznZcwgWdlIg4&e=

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

woodcock — Wed, 25 Mar 2020 14:16:38 GMT

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

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

nilbak1 — Thu, 26 Mar 2020 02:55:03 GMT

oops, my bad.
This is the correct one.

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

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

mschiesser — Thu, 14 Apr 2022 03:29:02 GMT

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