Splunk Search

Subtracting two epoch times after within stats table

jason_hotchkiss
Communicator

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

 

jason_hotchkiss
Communicator

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
Communicator

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

Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...