topic Re: How to calculate only specific fields in Splunk Search

How to calculate only specific fields

tomaszwrona — Fri, 28 Apr 2017 11:24:39 GMT

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

Re: How to calculate only specific fields

niketn — Sat, 29 Apr 2017 06:10:28 GMT

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.

Re: How to calculate only specific fields

cmerriman — Mon, 01 May 2017 12:12:31 GMT

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

Re: How to calculate only specific fields

DalJeanis — Mon, 01 May 2017 16:20:40 GMT

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

Re: How to calculate only specific fields

tomaszwrona — Tue, 02 May 2017 06:52:47 GMT

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