I need to calculate a running total, which uses two values from the previous row (one being this calculated total), and adds on a value from this row to create the total. It's slightly hard to explain, but fairly straightforward.
Imagine I have 3 fields, StartCount (never changes), fieldA (always >=0) and fieldB (always <= 0). I need to calculate RunningTotal , where
RunningTotal = <previousRow>RunningTotal - <previousRow>fieldA - <currentRow>fieldB
On the first row, RunningTotal should be set to StartCount
So this is my desired output:
| StartCount | RunningTotal | fieldA | fieldB |
+------------+--------------+--------+--------+
| 63 | 63 | 8 | -3 | <- RunningTotal = StartCount
| 63 | 57 | 6 | -2 | <- RunningTotal = 63 - 8 - -2
| 63 | 59 | 2 | -8 | <- RunningTotal = 57 - 6 - -8
| 63 | 64 | 6 | -7 | <- RunningTotal = 59 - 2 - -7
So this is the SPL I've tried using:
| makeresults count=10
| fields- _time
| eval StartCount = 63
| eval fieldA = random() % 10
| eval fieldB = 0-(random() % 10)
| streamstats current=f window=1 last(fieldA) as prev_fieldA last(RunningTotal) as prev_RunningTotal
| eval prev_RunningTotal = if( isnull(prev_RunningTotal ), StartCount, prev_RunningTotal )
| eval RunningTotal = if( isnull(prev_fieldA), StartCount, prev_RunningTotal - prev_fieldA - fieldB )
The problem I have is that prev_RunningTotal never changes, it's always equal to StartCount.
Can I not access previous streamstats outputs within the streamstats ? Or is there another way to solve this, when I don't know how many events/rows will be returned to carry out the total operation for?
... View more