topic Re: can we fill the null values in search results? in Splunk Search

can we fill the null values in search results?

rakesh_498115 — Wed, 05 Sep 2012 11:54:52 GMT

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

Re: can we fill the null values in search results?

MuS — Wed, 05 Sep 2012 12:05:33 GMT

Hi rakesh_498115

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

cheers,

MuS

Re: can we fill the null values in search results?

sdaniels — Wed, 05 Sep 2012 12:06:50 GMT

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.

Re: can we fill the null values in search results?

kristian_kolb — Wed, 05 Sep 2012 12:07:19 GMT

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)

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

Re: can we fill the null values in search results?

kristian_kolb — Wed, 05 Sep 2012 12:10:09 GMT

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

Re: can we fill the null values in search results?

kristian_kolb — Wed, 05 Sep 2012 12:11:32 GMT

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

Re: can we fill the null values in search results?

rakesh_498115 — Mon, 28 Sep 2020 12:22:59 GMT

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

Re: can we fill the null values in search results?

kristian_kolb — Wed, 05 Sep 2012 13:22:52 GMT

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

eval my_value = "5"

Re: can we fill the null values in search results?

rakesh_498115 — Wed, 05 Sep 2012 13:33:17 GMT

same error kristan...:(

Re: can we fill the null values in search results?

kristian_kolb — Wed, 05 Sep 2012 13:56:37 GMT

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?

Re: can we fill the null values in search results?

rakesh_498115 — Wed, 05 Sep 2012 14:06:03 GMT

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

Re: can we fill the null values in search results?

rakesh_498115 — Wed, 05 Sep 2012 14:07:02 GMT

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

Re: can we fill the null values in search results?

kristian_kolb — Wed, 05 Sep 2012 14:16:55 GMT

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?

Re: can we fill the null values in search results?

rakesh_498115 — Wed, 05 Sep 2012 14:31:57 GMT

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

Re: can we fill the null values in search results?

rakesh_498115 — Wed, 05 Sep 2012 14:42:34 GMT

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

Re: can we fill the null values in search results?

rakesh_498115 — Wed, 05 Sep 2012 17:55:22 GMT

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