topic Streamstats and resetting a running total in Splunk Search

Streamstats and resetting a running total

curtisb1024 — Fri, 06 Jun 2014 20:27:12 GMT

I'm using streamstats to calculate the running total for a value

... | streamstats sum(amount) as cumulativeAmount

But I need to reset the running total whenever a particular event is encountered and I can't seem to figure out how.

An example of the output I'm trying to achieve:

amount=10 cumulativeAmount=10
amount=12 cumulativeAmount=22
amount=4 cumulativeAmount=26
reset cumulativeAmount=0
amount=5 cumulativeAmount=5
reset cumulativeAmount=0
amount=8 cumulativeAmount=8
amount=11 cumulativeAmount=19

I've tried this:

... | eval cumulativeAmount=case(someCriteria, 0) | streamstats sum(amount) as cumulativeAmount

But streamstats overwrites the cumulativeAmount set by the eval. I've also tried something like:

... | streamstats sum(eval(case(someCriteria1, amount, someCriteria2, latest(cumulativeAmount)*-1))) as cumulativeAmount

But Splunk complains that latest isn't a function you can use inside a case. Any other way of doing this would be fine, doesn't need to be with streamstats. Any ideas?

Re: Streamstats and resetting a running total

somesoni2 — Fri, 06 Jun 2014 21:05:25 GMT

Try something like this

your base search | eval reset=if(someCriteria,1,0) | accum reset  | streamstats sum(amount) as cumulativeAmount by reset

I am adding a field reset value of which will be 1 for the event where reset is happening and 0 otherwise. After that, accum command will do the commulative addition of reset and create a series like 0,0,0...until first reset, then 1,1,1,...until second reset, then 3,3,3... and so on. Then the streamstats can create commulative sum based on the reset as the events are now categorized for each reset.

Re: Streamstats and resetting a running total

curtisb1024 — Sat, 07 Jun 2014 01:09:46 GMT

That did the trick. Thanks!

Re: Streamstats and resetting a running total

nmaiorana — Thu, 20 Aug 2015 13:36:46 GMT

I tried this, but the criteria I need to check on has not been established and the variable from streamstats (cumulativeAmount) which could be used to establish the criteria, does not appear to be available in the eval statement. It passes the isnull(cumulativeAmount) check every time.