Sample data below.
I need to compute the col_3 based on col_1. It should give me the running sum of col_2 but should reset to 0 if col_2 is zero for a given col_1 value.
col_1 col_2 col_3
A 1 1
A 0 0
A 2 2
A 3 5
A 0 0
B 2 2
B 0 0
B 0 0
B 1 1
B 1 2
Another alternative (works on 6.2.12 so should work on 6.3.3
| gentimes start=-1 | eval mydata="A,1 A,0 A,2 A,3 A,0 A,1 B,2 B,10 B,0 B,1 B,1" | makemv mydata| mvexpand mydata
| rex field=mydata "(?<col_1>[^,]*),(?<col_2>.*)" | table col_1 col_2
| eval temp=if(col_2=0,1,0) | accum temp | streamstats sum(col_2) as col_3 by temp
Caveat Non-streamstats
code assumes the data is in col_1
order ... and the records within any given value in col_1
are in some determinate order ... like the data in the example.
When data is in that order, reset_on_change=true
has no net effect on the streamstats
command. On the other hand, when data is NOT in col_1
order, reset_on_change=true
causes a reset of the stats whenever any of the keys change.
I'm on 6.4, so this is not a solution for you.
Note, the reset_before
command needs to use a numeric compare, or it will match 10
as well as 0
| makeresults | eval mydata="A,1 A,0 A,2 A,3 A,0 A,1 B,2 B,10 B,0 B,1 B,1" | makemv mydata| mvexpand mydata
| rex field=mydata "(?<col_1>[^,]*),(?<col_2>.*)" | table col_1 col_2
| streamstats sum(col_2) as col_3 by col_1 reset_before="col_2==0"
I added one final A,1
record to prove that the first B
would reset to 0
before adding its value, receiving the following output...
col_1 col_2 col_3
A 1 1
A 0 0
A 2 2
A 3 5
A 0 0
A 1 1
B 2 2
B 10 12
B 0 0
B 1 1
B 1 2
I don't see the option reset_before option in the streamstats document for 6.3.3. May be it's available but un-documented.
https://docs.splunk.com/Documentation/Splunk/6.3.3/SearchReference/Streamstats
crud. They've finished the upgrades to my boxes to 6.4 ... updating answer...
Try the following streamstats command with your base search
<YourBaseSearch>
| streamstats sum(col_2) as col_3 by col_1 reset_before="("match(col_2,\"0\")")" reset_on_change=true
Refer to Splunk documentation for reset_before and reset_on_change
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Streamstats
niketnilay - This works fine for 6.5.x but I m running on 6.3.3
Any alternate solution for it.