Splunk Search

can we fill the null values in search results?

rakesh_498115
Motivator

Hi..

can we fill the null values with our desired values in the search query .

Actually i tried the fillnull command but it didnt work .. I have used my query like this..

mysearch | eval MYVALUE=5 | fillnull value=MYVALUE

in this case .. all the null values are replaced with MYVALUE but not with 5 ..I need the value 5 in place of null values ..How can i do this ??

Please Help..

Tags (2)
0 Karma
1 Solution

kristian_kolb
Ultra Champion

Hi,

either of these should do, but... maybe the first will fail (i.e. insert "my_value" instead of "5").

mysearch | eval my_value = 5 | eval value = case(isnull(value), my_value)

or

mysearch | eval my_value = 5 | eval value = coalesce(value, my_value) 

Check out http://docs.splunk.com/Documentation/Splunk/4.3.3/SearchReference/CommonEvalFunctions

EDIT: typo

Hope this helps,

Kristian

View solution in original post

kristian_kolb
Ultra Champion

I think that maybe the "5" was a simplification, and the 'real' MYVALUE was more dynamic in nature.

/k

kristian_kolb
Ultra Champion

Hi,

either of these should do, but... maybe the first will fail (i.e. insert "my_value" instead of "5").

mysearch | eval my_value = 5 | eval value = case(isnull(value), my_value)

or

mysearch | eval my_value = 5 | eval value = coalesce(value, my_value) 

Check out http://docs.splunk.com/Documentation/Splunk/4.3.3/SearchReference/CommonEvalFunctions

EDIT: typo

Hope this helps,

Kristian

rakesh_498115
Motivator

thanks kristain..I Figured out the problem..Actually I think splunk is not defining the fields names starting with numbers..

Actually i used the previous like this

my_search | eval myval=5 | fillnull 95Best=myval | table 95Best, myval

So it didnt work...now changed it Best95 and now its working fyn..:)

0 Karma

rakesh_498115
Motivator

Let me clearly tell one more time..Consider the set Best95 from the table above.for the set i need to calucalte the average and this average value should be replaced in the null value of the same set i.e Best95.So My Expected output should be something like this..

Best95
0.035

-0.016
0.010
0.032

0.047
0.113
-0.027

Here 0.035 value is average of all the values
-0.016,0.010,0.032,0.047,0.113 and -0.027 .

Is ter any possible way for achieving this ??

0 Karma

rakesh_498115
Motivator

Yeah fillnull is working kristian..but why i mentioned eval myval=5 is. i need to calucate the avg of the set Best95 and that avg i need to replace in the first null value of Best95 set..hence the reason i have eval myval=5 to check whether we can use this in null value or not ? . if this works na..i thought of calucate the avg value as i mentioned and thought of replacing it with my actual query..can you please help on this..

0 Karma

kristian_kolb
Ultra Champion

strange that is. If we rewind a little bit, what does the output look like if you use;

your_search | eval myval=5 | fillnull Best95=myval | table Best95, myval

or if that gets weird, i.e. Best95 gets the string "myval", try fillnull Best95="5".

Output?

0 Karma

rakesh_498115
Motivator

where in Best95 the first value is null..i want tat value to be replaced by 5 . ..

0 Karma

rakesh_498115
Motivator

Best95 Worst95 myyval
1.393 5
-0.016 1.377 5
0.010 1.387 5
0.032 1.419 5
0.047 1.466 5
0.113 1.579 5
-0.027 1.552 5

These are values i got kristan..when i use the table command alone..

0 Karma

kristian_kolb
Ultra Champion

Sorry, but it works fine for me. Since I don't have your input data, I've used ordinary access_combined logs, and gotten the desired results...

is the input data OK? try to do a table Best95, Worst95 my_value instead of the last eval. What does the result look like?

0 Karma

rakesh_498115
Motivator

same error kristan...:(

0 Karma

kristian_kolb
Ultra Champion

Do you really want to use 5 as a value. Try to put it inside double quotes.

eval my_value = "5"

/k

0 Karma

rakesh_498115
Motivator

Hi Kristain..

When use eval command shown above .it is throwing error for me..

  Error in 'eval' command: The expression is malformed. Expected 

i have used like this..

|inputlookup="data.csv" | fields Best95,Worst95 | eval my_value = 5 | eval Best95= coalesce(Best95, my_value)

please help..

0 Karma

sdaniels
Splunk Employee
Splunk Employee

If you do ...mysearch | fillnull. Does it replace all empty fields with zeros? Or if you do ..| fillnull value="5" does it replace those empty fields? fillnul seems to be the right command for what you want to do. You should be able to do this and substitute in whatever value that you want.

MuS
SplunkTrust
SplunkTrust

Hi rakesh_498115

why don't you use the replace instead of fillnull?

cheers,

MuS

0 Karma

kristian_kolb
Ultra Champion

I sort of thought that 'value' should only be altered if it was null, which it may not always be.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...