Find call duration

[DanielASG]

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

[micahkemp · Accepted solution]

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.

[DalJeanis]

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

[micahkemp]

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.

[DalJeanis]

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

[DanielASG]

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

[micahkemp]

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

[DanielASG]

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

[DanielASG]

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