topic Find call duration in Splunk Search

Find call duration

DanielASG — Tue, 29 Sep 2020 16:57:10 GMT

I am trying to find the amount of time people are on the phone to the help desk but are not getting any results back

the input data CSV is below
Call_Start_Time,Call_End_Time
31/01/2016 23:32:13,31/01/2016 23:32:19

what i would like to see is a table with call start time and call end time with how long the person was on the phone for

the search i have been trying is
source="SD_CallData2016.csv" sourcetype="csv"
| eval StartTime=strptime(Call_Start_Time,"%Y-%m-%d %H:%M:%S")

| eval EndTime=strptime(Call_End_Time,"%Y-%m-%d %H:%M:%S")
| eval Duration=strftime(EndTime-StartTime,"%M:%S")
| table StartTime EndTime Duration

If anyone can help it would be appreciated
Thanks
Dan

Re: Find call duration

micahkemp — Mon, 27 Nov 2017 03:57:12 GMT

I think your Duration calculation is where your issue lies. Try | eval Duration=EndTime-StartTime. I don't believe you can use strftime to translate seconds into hour/minute/second, etc.

Re: Find call duration

DanielASG — Tue, 29 Sep 2020 16:57:13 GMT

thanks for the fast response

I'm still getting nothing back

the search I'm using now is

source="SD_CallData2016.csv" host="DRN" index="drn" sourcetype="csv"
| eval StartTime=strptime(Call_Start_Time,"%Y-%m-%d %H:%M:%S")

| eval EndTime=strptime(Call_End_Time,"%Y-%m-%d %H:%M:%S")
| eval Duration=EndTime-StartTime
| table StartTime EndTime Duration

it looks like Splunk is not converting the time at all
I'm starting to think my eval StartTime is wrong as well
Thanks
Dan

Re: Find call duration

micahkemp — Mon, 27 Nov 2017 04:16:29 GMT

Can you include an event (or more) from source="SD_CallData2016.csv" host="DRN" index="drn" sourcetype="csv"?

Re: Find call duration

DanielASG — Tue, 29 Sep 2020 16:57:15 GMT

the data i can send out is below 🙂

Call_Start_Time Call_End_Time Call_Group Location Disposition

1/01/2016 0:39:14 1/01/2016 0:44:31
1/01/2016 0:46:02 1/01/2016 0:46:10
1/01/2016 5:39:47 1/01/2016 5:45:08
1/01/2016 6:22:31 1/01/2016 6:27:00
1/01/2016 7:20:36 1/01/2016 7:24:14
1/01/2016 8:22:51 1/01/2016 8:29:29
1/01/2016 8:29:13 1/01/2016 8:29:48
1/01/2016 8:33:13 1/01/2016 8:39:40
1/01/2016 8:36:31 1/01/2016 8:40:28
1/01/2016 8:48:35 1/01/2016 8:57:18

Re: Find call duration

DanielASG — Tue, 29 Sep 2020 16:57:18 GMT

got it to work

source="SD_CallData2016.csv" host="DRN" index="drn" sourcetype="csv"
| eval start= strptime(Call_Start_Time,"%d/%m/%Y %H:%M:%S")
| eval end= strptime(Call_End_Time,"%d/%m/%Y %H:%M:%S")
| eval diff= end-start
| table Call_Start_Time Call_End_Time diff

I was putting in the wrong date string

thanks for you help

Re: Find call duration

DalJeanis — Mon, 27 Nov 2017 06:00:43 GMT

@micahkemp - Yes, you can use strftime() like that. The problem, that the OP has correctly fixed, is that the translations of time format to epoch via strptime() needed to occur correctly before the mathematical calculation of duration. Please correct your post so that it won't confuse folks.

Run anywhere sample code -

| makeresults | eval duration=123 | eval myMin = strftime(duration,"%M:%S")

Re: Find call duration

micahkemp — Mon, 27 Nov 2017 13:22:46 GMT

Actually, I'm not sure that's working as you expect. Try this:

| makeresults | eval duration=3601 | eval myMin = strftime(duration,"%M:%S")

Considering the format string one would probably expect (or at least hope) that the result would be 60:01, yet it is 00:01.

I believe that strftime is just handling duration as if it were an epoch time, and therefore will only work if your duration isn't longer than your format string can handle without overflow.

Re: Find call duration

DalJeanis — Mon, 27 Nov 2017 17:13:39 GMT

Yes, if you expect times over an hour then you'd have to do one of these...

| makeresults | eval duration=3601 
| eval myMin1 = strftime(duration,"%H:%M:%S")
| eval myMin2 = floor(duration/60,0).strftime(duration,":%S")

But then you could make the same argument at the next level for hours or days.

| eval duration2=90001 
| eval myMin3 = floor(duration2/3600).strftime(duration,":%M:%S")
| eval myMin4 = floor(duration2/86400)." ".strftime(duration,"%H:%M:%S")