Splunk Search

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

zacksoft
Contributor

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

cmerriman
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

micahkemp
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

cmerriman
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
0 Karma

zacksoft
Contributor

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

cmerriman
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

zacksoft
Contributor

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

zacksoft
Contributor

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

0 Karma

zacksoft
Contributor

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...