topic Re: How to generate a search to calculate new column value? in Splunk Search

How to generate a search to calculate new column value?

praveerg — Tue, 29 Sep 2020 13:22:36 GMT

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

Re: How to generate a search to calculate new column value?

niketn — Tue, 29 Sep 2020 13:22:58 GMT

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

Re: How to generate a search to calculate new column value?

praveerg — Wed, 22 Mar 2017 20:12:27 GMT

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

Re: How to generate a search to calculate new column value?

DalJeanis — Thu, 23 Mar 2017 14:44:36 GMT

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

Re: How to generate a search to calculate new column value?

somesoni2 — Thu, 23 Mar 2017 15:01:11 GMT

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

Re: How to generate a search to calculate new column value?

somesoni2 — Thu, 23 Mar 2017 15:02:07 GMT

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

Re: How to generate a search to calculate new column value?

DalJeanis — Thu, 23 Mar 2017 15:44:07 GMT

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

Re: How to generate a search to calculate new column value?

DalJeanis — Thu, 23 Mar 2017 16:02:05 GMT

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.