Splunk Search

Find Avg Diff two Date fields

I-Man
Communicator

Splunkers,

I have events from our Helpdesk ticketing system that have two date fields, DateOpen and DateClosed, both with the following format:
2013-02-25 12:50
2013-02-26 12:58

I am trying to write a report that shows average time from when the ticket was opened and when it was closed. Based on research, i think i need to convert these to epoch time using mktime and then do the subtraction, then convert back to ctime. Unfortunately, i cant get mktime to return any values.

convert timeformat="%y/%m/%d %H-%M" mktime(DateClosed) AS closedon_epoch | table DateClosed, closedon_epoch

Am i missing something here or am i going about this the wrong way? Thanks in advance for any help.

Tags (2)
0 Karma
1 Solution

jonuwz
Influencer

strptime is the way i'd do it ...

... | eval open_epoch=strptime(DateOpen,"%Y-%m-%d %H:%M") 
    | eval close_epoch=strptime(DateClosed,"%Y-%m-%d %H:%M")
    | eval duration=tostring(close_epoch-open_epoch,"duration")
    | ...

View solution in original post

jonuwz
Influencer

strptime is the way i'd do it ...

... | eval open_epoch=strptime(DateOpen,"%Y-%m-%d %H:%M") 
    | eval close_epoch=strptime(DateClosed,"%Y-%m-%d %H:%M")
    | eval duration=tostring(close_epoch-open_epoch,"duration")
    | ...

I-Man
Communicator

Perfect, thanks!!!

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...