Streamstats and resetting a running total

[curtisb1024]

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?

[somesoni2 · Accepted solution]

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.

[curtisb1024]

That did the trick. Thanks!

[nmaiorana]

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.