Splunk Search

AddTotals for time

gnovak
Builder

I have a column called LoadTime that displays the amount of time it took for a transaction to take place. I'd like to add this column up and display it as a field called Total.

Example:

00:10:53
00:15:01
00:15:37
02:59:33

I'd like to take all these time values and have a Total amount of time it took in a field called Total. I've been playing around with some commands and still haven't quite figured out how to do this yet.

So far adding this to the end of my search makes a duration field, but it's a large number such as 212117.747682.

| transaction maxspan=24h | eventstats sum(LoadTime) as "Total"

I've searched the forums but haven't found anything similar to what I am trying to do. Any ideas? I tried addtotals but this appears to be for a numerical value, not a time value.

Tags (1)
0 Karma
1 Solution

yannK
Splunk Employee
Splunk Employee

Convert your LoadTime to seconds, then do the sum, then convert back to hour:min:sec if needed.

see http://docs.splunk.com/Documentation/Splunk/5.0/SearchReference/convert

| transaction maxspan=24h | convert dur2sec(LoadTime) AS LoadTimeSec | eventstats sum(LoadTimeSec) as "TotalSec"

View solution in original post

yannK
Splunk Employee
Splunk Employee

Convert your LoadTime to seconds, then do the sum, then convert back to hour:min:sec if needed.

see http://docs.splunk.com/Documentation/Splunk/5.0/SearchReference/convert

| transaction maxspan=24h | convert dur2sec(LoadTime) AS LoadTimeSec | eventstats sum(LoadTimeSec) as "TotalSec"

yannK
Splunk Employee
Splunk Employee

only one convert at a time, and they can have different formats.

try something like | convert timeformat="$h:$m"$s" dur2sec(fieldA) AS Asec | convert timeformat="$d $h:$m"$s" dur2sec(fieldB) AS Bsec

0 Karma

gnovak
Builder

I wonder, can you convert another field as well and add it to this total? I tried | convert dur2sec(LoadTime) dur2sec(IndexEnd) AS LoadTimeSec but this didn't seem to work. Was going to try stretching the command out a bit more. I'm playing with it now. I have the general idea I just have to get the syntax correct. I appreciate your assistance. There's so many commands I always forget one!

0 Karma

yannK
Splunk Employee
Splunk Employee

My first method was to extract hh mm ss with the rex command, then use eval to normalize to seconds... But then I saw the dur2sec command 🙂

0 Karma

gnovak
Builder

Ah, convert command. I tried using that one too but must have just used the wrong syntax. This eventually gave me what i was looking for: | transaction maxspan=24h | convert dur2sec(LoadTime) AS LoadTimeSec | eventstats sum(LoadTimeSec) as "TotalSec" | convert timeformat="%H:%M:%S" ctime(TotalSec) AS Total_Time. Thanks for sending me in the right direction...

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...