topic Re: Is there a streamstats function that will show a running result of each value in a row being multiplied by the result above? in Splunk Search

Is there a streamstats function that will show a running result of each value in a row being multiplied by the result above?

Amohlmann — Tue, 21 Jul 2015 04:03:32 GMT

I have a search that returns the survival rate over time. For instance:

Time                SurvivalRate     
1                        0.98                    
2                        0.96                     
3                        0.65
4                        1
.                          .
.                          .
.                          .

I would like to show a running survival rate that is like streamstats sum(survivalRate), but instead of adding the numbers in each new line, it multiplies it. So it would return something like this:

Time                SurvivalRate     RunningSurvivalRate
1                        0.98                    0.98
2                        0.96                    0.9408 (0.98 * 0.96)
3                        0.65                    0.61152 (0.9408 * 0.65)
4                        1                       0.61125 (0.61152 * 1)
.                          .
.                          .
.                          .

Am I using the wrong tool for this job? Is there a streamstats function that I am ignorant of?
Thanks for the assistance.

Re: Is there a streamstats function that will show a running result of each value in a row being multiplied by the result above?

Richfez — Tue, 21 Jul 2015 16:31:41 GMT

I believe you'll want to use the "window=2" parameter to streamstats, then calculate using first(SurvivalRate) and last(RunningSurvivalRate).

Something like this:

(search that returns the above results) 
| streamstats window=2 last(SurvivalRate) as old_value first(RunningSurvivalRate) as new_value 
| eval RunningSurvivalRate=old_value*new_value

Obviously you'll probably have to tweak that, especially with regards to which item is first and which is last because I always get those mixed up.

Re: Is there a streamstats function that will show a running result of each value in a row being multiplied by the result above?

Amohlmann — Tue, 21 Jul 2015 22:47:01 GMT

Thanks, it is almost there, I now have access to the previous result, but not the previous result of the runningSurvivalRate.

I will have a play using this when I get a chance. You really helped. Thanks

Re: Is there a streamstats function that will show a running result of each value in a row being multiplied by the result above?

ppablo — Wed, 22 Jul 2015 00:30:13 GMT

Hi @Amohlmann

Once you come up with a final search, would you be able to share that here? I think other users would find the solution useful.

Re: Is there a streamstats function that will show a running result of each value in a row being multiplied by the result above?

Amohlmann — Wed, 22 Jul 2015 00:41:01 GMT

I absolutely will. I marked this answer as correct as it gave me what I was looking for which was a new way to use streamstats.

I should be able to do a few tweaks later on today.

Re: Is there a streamstats function that will show a running result of each value in a row being multiplied by the result above?

Amohlmann — Wed, 22 Jul 2015 07:05:35 GMT

That is a really good start but not quite perfect.
I think a slightly better start is to use:
| streamstats list(survivalRate)

Is there a way to multiply all the different values in a multivalue field?