AddTotals for time

[gnovak]

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.

[yannK · Accepted solution]

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]

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

[gnovak]

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!

[yannK]

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 🙂

[gnovak]

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