How can I use the output of streamstats in current row, and have it feed back into streamstats for next row?

philtanner — Wed, 08 Aug 2018 10:35:19 GMT

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?

Re: How can I use the output of streamstats in current row, and have it feed back into streamstats for next row?

DalJeanis — Tue, 18 Sep 2018 18:06:51 GMT

Okay, you are thinking about this the wrong way. You are "committing spreadsheet".

The value you want is the running total, right? Which is the net total from EVERYTHING that has gone before.

You will have to figure out what is appropriate to get the initial running balance (Startcount), but the rest is trivial. Calculate the net-addition onto the record, and then use streamstats to calculate the total net running balance for each record.

Here's a run-anywhere example...

| makeresults count=10 

| rename COMMENT as "Put StartCount only on the first record, put updates on all other records"
| streamstats count as recno
| eval  StartCount = case(recno==1,63)
| eval fieldA = case(recno!=1,random() % 10)
| eval fieldB = case(recno!=1,0-(random() % 10))
| fields- _time recno

| rename COMMENT as "Calculate net change"
| eval RunningNet=coalesce(StartCount, (fieldA+fieldB) ,0)

| rename COMMENT as "Calculate running totals"
| streamstats sum(RunningNet) as RunningTotal

When you find yourself stuck in a paradigm like this, it's best to step back a few feet and look at what you started with, rather than what you think you need to use from where your code happens to be. Often, the correct approach in Splunk starts with viewing the incoming events in a different light.

topic Re: How can I use the output of streamstats in current row, and have it feed back into streamstats for next row? in Splunk Search

How can I use the output of streamstats in current row, and have it feed back into streamstats for next row?

Re: How can I use the output of streamstats in current row, and have it feed back into streamstats for next row?