How do I convert epoch time to HH:MM:SS?

[mjd555]

Background

So I have two date fields - Date_Created & Acknowledge_Date both in the format YYYY-MM-DD HH:MM:SS. I wish to work out the difference of these two times and then create an average of all the results - essentially this -> Average(Acknowledge_Date-Date_Created)

Search
I have created the following search. This has converted the times to epoch, subtracted them, and provided an average of the epoch time.

 index="Cyber" sourcetype=Response queue = "Incident" status ="resolved"  | dedup ticket

    | table Date_Created, Acknowledge_Date 
    | eval epoch1=strptime(Date_Created,"%Y-%m-%d %H:%M:%S") 
    | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(Date_Created) as epochDateCreated
    | eval epoch2=strptime(Acknowledge_Date,"%Y-%m-%d %H:%M:%S")
    | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(Acknowledge_Date) as epochAck
    | eval Diff=(epochAck-epochDateCreated)

    |stats avg(Diff)

Problem
I now have an average number in epoch format - 5848.333333 - is there a way to convert this into HH:MM:SS

Any help will be much appreciated

[tmccamant]

If you wanted to look at the average time in hours that an incident ticket was open you could use this search

index="Cyber" sourcetype=Response queue="Incident" status="resolved" 
| dedup ticket  
| eval Diff=floor(((strptime(Acknowledge_Date,"%Y-%m-%d %H:%M:%S"))-(strptime(Date_Created,"%Y-%m-%d %H:%M:%S")))/(3600)) 
| stats avg(Diff)

Let me know if this works for you.

[mjd555]

Hi tmccamant,

Afraid this hasn't worked as it has left me with the result of -15.208333

Any other ideas will be greatly appreciated

[DMohn]

Your command would be

 | eval DiffTime=strftime(Diff, "%H:%M:%S")

[mjd555]

Hi DMohn. | eval DiffTime=strftime(Diff, "%H:%M:%S") helps me convert the epoch time to the format I want. However I cannot appear to get | stats avg(DiffTime) when it is in this format.

I also cannot use |stats avg(Diff) before making the conversion.

Is there a solution to this?

[gyslainlatsa]

hi mjd555,

try following this link http://docs.splunk.com/Documentation/Splunk/6.2.0/SearchReference/CommonEvalFunctions
and look the function strftime(X,Y)

[mjd555]

Hi Gyslainlatsa, thanks for your help. However once I have added strftime to the code, I cannot average my results.

I also cannot use | stats avg(Diff) before making my conversion.

Is there a solution to this?

[mjd555]

Hi gyslainlatsa, so I strftime has helped me see the time difference in HH:MM:SS - however I cannot avg these results. Is there a way to do this? Thanks again!

 index="Cyber" sourcetype=Response queue = "Incident" status ="resolved"  | dedup ticket

         | table Date_Created, Acknowledge_Date 
         | eval epoch1=strptime(Date_Created,"%Y-%m-%d %H:%M:%S") 
         | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(Date_Created) as epochDateCreated
         | eval epoch2=strptime(Acknowledge_Date,"%Y-%m-%d %H:%M:%S")
         | convert timeformat="%Y-%m-%d %H:%M:%S" mktime(Acknowledge_Date) as epochAck
         | eval epochDiff=(epochAck-epochDateCreated)
         | eval normalDiff=strftime(epochDiff, "%H:%M:%S")