How to generate a search to calculate new column value?

[praveerg]

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

[somesoni2]

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

[DalJeanis]

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.

[DalJeanis]

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

[somesoni2]

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

[DalJeanis]

crud. They've finished the upgrades to my boxes to 6.4 ... updating answer...

[niketn]

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

[praveerg]

niketnilay - This works fine for 6.5.x but I m running on 6.3.3
Any alternate solution for it.