In a table how to calculate sum of the values of one column between two specific value of another column ?

[zacksoft]

The table output of my splunk query gives me an output like this.

uri | transaction_time
/se/al/st/o1 | 97 sec
/post/em/sec/w | 16 sec
/ret/jim/st/sem | 20 sec
/got/em/sec/w/2 | 33 sec
/gt/ey/sec/d/3 | 21 sec
/st/em/sec/65 | 12 sec

I want an add-total of my coulmn "transaction_time" , but it shouldn't add all the values there.
It should only add the values between the uri column value "/post/em/sec/w" and "/gt/ey/sec/d/" .
i.e. 16 + 20+ 33+ 21

[micahkemp]

What do you see in the fields shown that indicates the start and end of the values that should be included in your sum? Based on your question it's unclear, as there appears to be no commonality between the rows /post/em/sec/w and /gt/ey/sec/d/3 (which is what I assume you meant instead of /gt/ey/sec/d, which doesn't exist).

[cmerriman · Accepted solution]

try adding something like this to the end of your query:

|eval trans_time=if(uri="/post/em/sec/w" OR uri="/gt/ey/sec/d/",transaction_time,null())|rex field=trans_time mode=sed "s/ sec//g"|addcoltotals trans_time

[zacksoft]

Hi @cmerriman
I tried this. It creates another column and gives the value of /post/em/sec/w only. (i.e. 16 sec).
It is not adding the sum from "/post/em/sec/w" and "/gt/ey/sec/d/" .

[cmerriman]

What it should do is create a new column with the values for both of those uris and then the Rex command should remove the sec label and addcoltotals should sum up the new column with the value on a new row at the bottom of the table.

You’re not seeing the eval command bring both uris into the new field or the rex command Work?

[zacksoft]

It should keep adding the values from "/post/em/sec/w" until it encounters "/gt/ey/sec/d/" . Currently it is only adding an showing the value of /post/em/sec/w i.e. 16.

[zacksoft]

And it shouldn't add both those uri. It should add all the values present between those 2 uris.

[zacksoft]

I have removed the sec from the base search so the sec is no more present but just the numeric value.