Splunk Search

Subtracting two epoch times after within stats table

jason_hotchkiss
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>
| 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_Doo3https://www.youtube.com/embed/scoobie_snacks16:12:37
16:12:37
16:12:34
16:12:3416:12:3719: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)
0 Karma
1 Solution

bowesmana
SplunkTrust
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

 

View solution in original post

bowesmana
SplunkTrust
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

 

View solution in original post

jason_hotchkiss
Path Finder

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

0 Karma

bowesmana
SplunkTrust
SplunkTrust

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

 

0 Karma

jason_hotchkiss
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!