topic Re: How can I do sum of a time field? in Splunk Search

How can I do sum of a time field?

tamduong16 — Wed, 02 Aug 2017 20:43:17 GMT

Hi I'm new to Splunk and currently trying to understand how the search function work. How could I get Splunk to display the sum of call duration in total from all of my events? Thanks!

Re: How can I do sum of a time field?

DalJeanis — Wed, 02 Aug 2017 22:39:03 GMT

Lots of ways, depending on what you want.

If you just want to know the sum of all those, and don't need the details, then...

| stats sum("Call Duration") as "Call Duration"

If you want to keep the details and just add a totals line at the bottom for only the Call Duration field...

| addtotals row=f col=t "Call Duration"

If you want to append an additional totals line after all the prior lines with the total call duration, the maximum max latency, and the average call rate

| appendpipe [| stats sum("Call Duration") as "Call Duration" max("Max Latency Rx") as "Max Latency Rx" avg("Call Rate") as "Call Rate"]

All of those depend on the assumption that the duration is a value in seconds, that has just been told to format itself as you have shown. If it is actually a character value, then you are going to have to strptime it first in order to be able to do the calculations.

| eval "Call Duration"= strptime("Call Duration","%H:%M:%S")
... one of the above things ...
| eval "Call Duration"= if("Call Duration">86400,floor("Call Duration"/86400)." Days ".round(("Call Duration"/86400)/3600,2)." Hours" ,strftime("Call Duration","%H:%M:%S"))

Re: How can I do sum of a time field?

tamduong16 — Thu, 03 Aug 2017 14:07:21 GMT

For some reason, the first stats command didn't show up anything.
The eval "Call Duration"= strptime("Call Duration","%H:%M:%S") give me nothing under Call Duration. It doesn't have a zero or anything display.

Re: How can I do sum of a time field?

sbbadri — Thu, 03 Aug 2017 16:12:25 GMT

your base search | eval tp = "Call Duration" | rename "Max Latency Rx" as lrx, "Call Rate" as cr | convert dur2sec(tp) as tp | stats sum(tp) as tp by lrx cr | eval tp= if(tp>86400,floor(tp/86400)." Days ".round((tp/86400)/3600,2)." Hours" ,strftime(tp,"%H:%M:%S")) | rename lrx as "Max Latency Rx", cr as "Call Rate" | table "Call Duration", "Max Latency Rx", "Call Rate" , tp

Re: How can I do sum of a time field?

tamduong16 — Thu, 03 Aug 2017 19:14:41 GMT

I were able to figure out how to get the answer here:

....| convert num("Call Duration") | stats sum("Call Duration") as "TotalCD" | eval "TotalCD"=tostring($TotalCD$,"duration")

Re: How can I do sum of a time field?

DalJeanis — Thu, 03 Aug 2017 19:57:21 GMT

hmmm. You have a single-digit hour there.

| eval "Call Duration"= if(len("Call Duration")=7,"0","")."Call Duration" 
| eval "Call Duration"= strptime("Call Duration","%H:%M:%S")

Re: How can I do sum of a time field?

DalJeanis — Thu, 03 Aug 2017 19:59:17 GMT

Yeah, those renames help the code be less ugly a lot, don't they?

Re: How can I do sum of a time field?

DalJeanis — Thu, 03 Aug 2017 20:41:46 GMT

You'd better verify against a couple of events that you are getting the correct result. If the "Call Duration" field was already in duration, then that would work.

Re: How can I do sum of a time field?

sjbriggs — Fri, 20 Oct 2023 18:05:54 GMT

This worked fine for me to get to seconds, then I just did /60/60 to get to hours which is what I wanted to sum up.

Re: How can I do sum of a time field?

isoutamo — Fri, 20 Oct 2023 21:31:38 GMT

This

eval durations = tostring(durAsSec, "duration")

gives to you also days, hours and minutes. Just select those from that string.