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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...