How to convert epoch time to HH:MM:SS AFTER using stats AVG?

[mjd555]

So I have the following search:

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)

I now have an average time it takes to acknowledge an incident in epoch format. However, I cannot use Strftime once the figure has already been averaged. Is there a way around this?

[richgalloway · Accepted solution]

Try this

... | stats avg(Diff) AS avgDiff | fieldformat avgDiff=tostring(avgDiff, "duration") | ...

[ppablo]

confetti @richgalloway 🙂 I just noticed my upvote put you over the 10,000 karma milestone. Congrats and well deserved!

[richgalloway]

Thanks, ppablo!

[MuS]

Added more :confetti: to the party !!!!! 🙂

[mjd555]

Afraid this didn't work as it just returned a blank value

[richgalloway]

I just noticed a typo in the fieldformat command. Try the new answer. If it still doesn't work, please post your new search.

[mjd555]

Perfect, that has given me an answer of: 03:25:04.487179

Is there a way to round this?

[richgalloway]

Try substr. Adjust the second number to get the length you want.

... | eval avgDiff=substr(avgDiff, 1, 8) | ...

[mjd555]

Amazing, thanks for your help!!

[richgalloway]

Please accept the answer.