calculate time difference between 2 fields | sum and group by month

[andyk]

I have a log that looks like this:

start_time="2011-11-19T13:32:59" end_time="2011-11-19T13:34:59"

How do I create a report that shows the total duration by month?

I would like to do something like this:

search | eval xduration=(end_time-start_time) | stats sum(xduration) by date_month

But the above, of course, does not work. How do I get Splunk to recognize the vaules in the start_time and end_time fields as timestamps?

[wimvdheijkant]

Dear all,

When I do what is says above the duration I get return is off by 2 hours...?
I'm using the following:
Search |
convert timeformat="%d-%m-%Y %H:%M:%S" mktime(ADCreatedTime) |
convert timeformat="%d-%m-%Y %H:%M:%S" mktime(FIMCreatedTime) |
where ADCreatedTime > FIMCreatedTime |
Eval Duration = ADCreatedTime - FIMCreatedTime |

convert timeformat="%H:%M:%S" ctime(Duration) as D |
convert timeformat="%d-%m-%Y %H:%M:%S" ctime(ADCreatedTime) as ADC |
convert timeformat="%d-%m-%Y %H:%M:%S" ctime(FIMCreatedTime) as FDC |
Table user, FDC, ADC, D

The results are:
user FDC ADC D
abiggelaar 25-03-2011 11:02:34 25-03-2011 12:05:49 03:03:15
ahayward 16-02-2011 12:54:59 16-02-2011 13:57:49 03:02:50
beheer.danaher 04-03-2011 09:49:39 04-03-2011 10:53:39 03:04:00

I can't find any time zone stuff going wrong.
Can anyone point me in the right direction? Thanks!
Wim

[imrago · Accepted solution]

You should first convert to epoch time, something like this:

| convert timeformat="%y-%m-%dT%H:%M:%S" mktime(end_time) mktime(start_time) | eval duration=end_time-start_time