How to calculate only specific fields

[tomaszwrona]

Hello,

from my raw data:

TIME                A   B   
2017-04-26 13:00:00     10         
2017-04-26 13:10:00 10   
2017-04-26 13:20:00 10      
2017-04-26 13:30:00     10
2017-04-26 13:40:00     10
2017-04-26 13:50:00     10
2017-04-26 14:00:00     10
2017-04-26 14:10:00     10
2017-04-26 14:20:00     10         
2017-04-26 14:30:00 10   
2017-04-26 14:40:00 10      
2017-04-26 14:50:00     10
2017-04-26 15:00:00     10
2017-04-26 15:10:00     10
2017-04-26 15:20:00     10
2017-04-26 15:30:00     10
2017-04-26 15:40:00     10
2017-04-26 15:50:00     10
2017-04-26 16:00:00     10
2017-04-26 16:10:00     10

i would like to achieve following output:

TIME                A   B   Asum   Bsum          
2017-04-26 13:00:00     10       10
2017-04-26 13:10:00 10   
2017-04-26 13:20:00 10   20
2017-04-26 13:30:00     10
2017-04-26 13:40:00     10
2017-04-26 13:50:00     10
2017-04-26 14:00:00     10
2017-04-26 14:10:00     10
2017-04-26 14:20:00     10         60
2017-04-26 14:30:00 10   
2017-04-26 14:40:00 10    20
2017-04-26 14:50:00     10
2017-04-26 15:00:00     10
2017-04-26 15:10:00     10
2017-04-26 15:20:00     10
2017-04-26 15:30:00     10
2017-04-26 15:40:00     10
2017-04-26 15:50:00     10
2017-04-26 16:00:00     10
2017-04-26 16:10:00     10         90

How can it be done?

Best regards
Tomasz

[DalJeanis · Accepted solution]

Here's some run-anywhere code to show you how to get what you want. It's only complicated because of your not wanting the accumulated totals to go past zero records, and not wanting it to appear except on the last record of each group.

| makeresults count=20 | streamstats count as recno | eval _time=relative_time(now(),"-2h@h")+600*recno 
| eval rand=((random()%7)+(random()%37))%2 | eval A=if(rand>0,10*rand,null()) | eval B=if(rand>0,null(),10*(1-rand))
| table _time A B
| rename COMMENT as "The above just makes some random test data."

| rename COMMENT as "Now we break the transactions up into groups, considering A and B separately because it's easier."
| rename COMMENT as "Each group of actual values for A (for example) gets the tranA for the null A record before it."
| streamstats count(eval(isnull(A))) as tranA, count(eval(isnull(B))) as tranB
| eventstats sum(A) as Asum by tranA
| eventstats sum(B) as Bsum by tranB

| rename COMMENT as "We sort them into reverse time order, to identify the last event in each group, and blank the sums for every other event."
| sort 0 - _time
| autoregress tranA as nextA P=1
| autoregress tranB as nextB P=1
| eval Asum=if(A=0 OR tranA=nextA,null(),Asum)
| eval Bsum=if(B=0 OR tranB=nextB,null(),Bsum)

| rename COMMENT as "We flip them back into _time order, and eliminate all the work fields."
| reverse
| table _time A B Asum Bsum

[tomaszwrona]

for my purpose this is the best way - thank you!

[cmerriman]

you could try streamstats. i think the below might work but one of the other options for streamstats might work better

https://docs.splunk.com/Documentation/Splunk/6.5.3/SearchReference/Streamstats

|streamstats reset_after="("isnull(A)") sum(A) as sumA|streamstats reset_after="("isnull(B)") sum(B) as sumB

[niketn]

How about the following?

| timechart span=10m sum(A) as  A sum(B) as B
| accum A as CumulativeA
| accum B as CumulativeB

You can either create a Chart overlay with CumulativeA and CumulativeB or show only Cumulative fields.