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?
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.
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.
That did the trick. Thanks!
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.