Splunk Search
Highlighted

How to calculate interval sum for repeated event pairs

Hi, I'm a Splunk newbie. Can anyone help me with this. Thanks.

For the following events, I need to calculate the sum of time interval used for stepA to stepB. So it should be (TimeStamp3 - TimeStamp2) + (TimeStamp5 - TimeStamp4) + (TimeStamp7-TimeStamp6).

TimeStamp1 Step=stepStart, Tid=1111
TimeStamp2 Step=stepA, Tid=1111
TimeStamp3 Step=stepB, Tid=1111
TimeStamp4 Step=stepA, Tid=1111
TimeStamp5 Step=stepB, Tid=1111
TimeStamp6 Step=stepA, Tid=1111
TimeStamp7 Step=stepB, Tid=1111
TimeStamp8 Step=stepEnd, Tid=1111

Tags (3)
0 Karma
Highlighted

Re: How to calculate interval sum for repeated event pairs

SplunkTrust
SplunkTrust

Will there be any other steps in between stepA and stepB?

0 Karma
Highlighted

Re: How to calculate interval sum for repeated event pairs

There will not be any other steps between stepA and stepB. But there may be other steps between stepStart and stepEnd. Thanks.

0 Karma
Highlighted

Re: How to calculate interval sum for repeated event pairs

SplunkTrust
SplunkTrust

Ok. One last thing, what would be the final output? mention all the fields that you want.

0 Karma
Highlighted

Re: How to calculate interval sum for repeated event pairs

Final output will include Tid, duration for stepStart to stepEnd, sum of all stepA to stepB. Thanks.

0 Karma
Highlighted

Re: How to calculate interval sum for repeated event pairs

SplunkTrust
SplunkTrust

Give this a try (change the field name as applicable)

your base search | sort 0 Tid Timestamp | streamstats current=f window=1 first(Timestamp) as prev | eval StepABDuration=if(Step="stepB", Timestamp-prev,0) | stats min(Timestamp) as start max(Timestamp) as max sum(StepABDuration) as StepABDuration by Tid | eval StepDuration=max-min | table Tid, StepDuration, StepABDuration
Highlighted

Re: How to calculate interval sum for repeated event pairs

Thanks so much for your help. I'm getting 'Error in 'streamstats' command: The argument ' ' is invalid. Is it because I tried to sum up two different event pairs?

TimeStamp1 Step=stepStart, Tid=1111
TimeStamp2 Step=stepA, Tid=1111
TimeStamp3 Step=stepB, Tid=1111
TimeStamp4 Step=stepC, Tid=1111
TimeStamp5 Step=stepD, Tid=1111
TimeStamp6 Step=stepA, Tid=1111
TimeStamp7 Step=stepB, Tid=1111
TimeStamp9 Step=stepA, Tid=1111
TimeStamp10 Step=stepB, Tid=1111
TimeStamp11 Step=stepC, Tid=1111
TimeStamp12 Step=stepD, Tid=1111
TimeStamp13 Step=stepEnd, Tid=1111

| eval TotalTime = duration
| sort 0 TimeStamp

| streamstats current=f window=1 first(TimeStamp) as prev 
| eval stepABDuration=if(Step="stepB", Timestamp-prev,0) 
| eval stepCDDuration=if(Step="stepD", TimeStamp-prev,0) 

| stats sum(stepABDuration) as stepABDuration by Tid 
| stats sum(stepCDDuration) as stepCDDuration by Tid 

| transaction TId 

| table Tid, TotalTime, stepABDuration, stepCDDuration
0 Karma