Splunk Search

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

mjd555
Path Finder

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

0 Karma

tmccamant
Explorer

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.

0 Karma

mjd555
Path Finder

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

0 Karma

DMohn
Motivator

Your command would be

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

mjd555
Path Finder

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?

0 Karma

gyslainlatsa
Motivator

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
Path Finder

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?

0 Karma

mjd555
Path Finder

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")
0 Karma
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 ...