Splunk Search

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

andyk
Path Finder

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?

Tags (2)
1 Solution

imrago
Contributor

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

View solution in original post

wimvdheijkant
New Member

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

0 Karma

imrago
Contributor

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
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...