Archive

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

Communicator

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

Tags (1)
0 Karma
1 Solution

Super Champion

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

View solution in original post

0 Karma

Champion

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).

0 Karma

Super Champion

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

View solution in original post

0 Karma

Communicator

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/" .

0 Karma

Super Champion

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?

0 Karma

Communicator

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.

0 Karma

Communicator

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

0 Karma

Communicator

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

0 Karma