Splunk Search

## Subtracting two epoch times after within stats table

Path Finder

Hello - we are trying to calculate the possible_duration between the first event and last event in the following base search:

<base_search>
| stats count as "visits" values(docid) as url list(_time) as time_of_events earliest(_time) as first_event latest(_time) as last_event by user
| eval duration = last_event - first_event
| eval possible_duration = strftime(duration,"%H:%M:%S")
| eval time_of_events = strftime(time_of_events,"%H:%M:%S")
| eval first_event = strftime(first_event,"%H:%M:%S")
| eval last_event = strftime(last_event,"%H:%M:%S")
| table user visits url time_of_events first_event last_event possible_duration

Result:

 Scoobie_Doo 3 https://www.youtube.com/embed/scoobie_snacks 16:12:3716:12:3716:12:34 16:12:34 16:12:37 19:00:03

The possible_duration field seems to get the minutes and seconds right.  But not the hour.  Looking for a suggestion one what I am missing.
Labels (2)

• ### stats

1 Solution
SplunkTrust

Using strftime on a duration type field will always take account your local time, so if you added in a date to that strftime, you would see it's probably Jan 1 1970.

When dealing with duration there are two ways, either using tostring or doing the maths, like this example search

``````| makeresults
| eval duration=147
| eval t-UsingToString=tostring(duration,"duration")
| eval h=round(duration/3600), m=round((duration-(h*3600))/60), s=duration%60
| eval t-UsingHMS=printf("%02d:%02d:%02d", h, m, s)
| table duration t-UsingToString t-UsingHMS``````

SplunkTrust

Using strftime on a duration type field will always take account your local time, so if you added in a date to that strftime, you would see it's probably Jan 1 1970.

When dealing with duration there are two ways, either using tostring or doing the maths, like this example search

``````| makeresults
| eval duration=147
| eval t-UsingToString=tostring(duration,"duration")
| eval h=round(duration/3600), m=round((duration-(h*3600))/60), s=duration%60
| eval t-UsingHMS=printf("%02d:%02d:%02d", h, m, s)
| table duration t-UsingToString t-UsingHMS``````

Path Finder

I attempted your solution, however, I am unable to get it to work in my particular case.

SplunkTrust

Can you give more details on what you tried and the results you got.

Path Finder

It ended up being my fault.  I was able to use an element of your example to produce the results I was looking for:

|eval duration = last_event - first_event
|eval possible_duration=tostring(duration, "duration")

Did you miss .conf21 Virtual?

### Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE! Catch Up Now >>

Get Updates on the Splunk Community!