topic Re: Subtracting two epoch times after within stats table in Splunk Search

Subtracting two epoch times after within stats table

jason_hotchkiss — Wed, 16 Jun 2021 20:28:34 GMT

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

<base_search>
| eval docid="https://www.youtube.com/embed/".docid
| 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

https://www.youtube.com/embed/scoobie_snacks

16:12:37
16:12:37
16: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.

Re: Subtracting two epoch times after within stats table

bowesmana — Wed, 16 Jun 2021 23:13:54 GMT

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

Re: Subtracting two epoch times after within stats table

jason_hotchkiss — Thu, 17 Jun 2021 14:41:25 GMT

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

Re: Subtracting two epoch times after within stats table

bowesmana — Thu, 17 Jun 2021 22:45:42 GMT

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

Re: Subtracting two epoch times after within stats table

jason_hotchkiss — Mon, 21 Jun 2021 13:38:13 GMT

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")