Splunk Search
Highlighted

Find call duration

Explorer

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
CallStartTime,CallEndTime
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="SDCallData2016.csv" sourcetype="csv"
| eval StartTime=strptime(Call
StartTime,"%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

0 Karma
Highlighted

Re: Find call duration

Champion

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.

View solution in original post

Highlighted

Re: Find call duration

Explorer

thanks for the fast response

I'm still getting nothing back

the search I'm using now is

source="SDCallData2016.csv" host="DRN" index="drn" sourcetype="csv"
| eval StartTime=strptime(Call
StartTime,"%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

0 Karma
Highlighted

Re: Find call duration

Champion

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

0 Karma
Highlighted

Re: Find call duration

Explorer

the data i can send out is below 🙂

CallStartTime CallEndTime 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

0 Karma
Highlighted

Re: Find call duration

Explorer

got it to work

source="SDCallData2016.csv" host="DRN" index="drn" sourcetype="csv"
| eval start= strptime(Call
StartTime,"%d/%m/%Y %H:%M:%S")
| eval end= strptime(Call
EndTime,"%d/%m/%Y %H:%M:%S")
| eval diff= end-start
| table Call
StartTime CallEnd_Time diff

I was putting in the wrong date string

thanks for you help

Highlighted

Re: Find call duration

SplunkTrust
SplunkTrust

@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") 
Highlighted

Re: Find call duration

Champion

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.

0 Karma
Highlighted

Re: Find call duration

SplunkTrust
SplunkTrust

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")
0 Karma