Splunk Search

How can I find the time duration between two fields?

locose
Path Finder

Trying to find the time duration between 2 fields

Field name : START_TS
2017-08-16 04:07:00.0

Field name : END_TS
2017-08-16 04:12:00.0

I tried something like....

my search query  | eval Starttime=strftime(START_TS,"%y-%m-%d %H:%M:%S:%N")
 | eval Endtime=strftime(END_TS,"%y-%m-%d %H:%M:%S:%N")
 |eval duration = Endtime - Starttime

But it didn't work.

myriadic
Path Finder

I found the problem(s):

  1. "Year" has to be a capital "Y", instead of lowercase.

  2. Before "%N", you have a colon, instead of a period.

Here's my working search:

| makeresults | eval START_TS="2017-08-16 04:07:00.0" | eval END_TS="2017-08-16 04:12:00.0" | eval st = strptime(START_TS, "%Y-%m-%d %H:%M:%S.%N") | eval et = strptime(END_TS, "%Y-%m-%d %H:%M:%S.%N") | eval diff = et - st | eval dur = tostring(diff, "duration")

More info on the date variables can be found here: https://docs.splunk.com/Documentation/Splunk/6.6.1/SearchReference/Commontimeformatvariables

0 Karma

3no
Communicator

Ok,

let's give this a try, then :

my search query 
| convert timeformat="%Y-%m-%d %H:%M:%S" mktime("START_TS")
| convert timeformat="%Y-%m-%d %H:%M:%S" mktime("END_TS")
| eval duration = END_TS - START_TS

3no

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

@3no - this should work, but you need to account for the ".%1Q" in the original field.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

@locose -

First, the difference between strftime and strptime is f for FORMAT, p for PULL.

strftime takes data that is in epoch form, and formats it forward to human-readable form.

strptime takes time data that is formatted for display, and strips ( strps) it back into epoch time, perfect for performing productive calculations. In this case, you want strptime, as @3no said.

Second, whichever direction you are going, each piece of the display format needs to be exactly right.

%y is 2-digit year, %Y is 4-digit year.

Also, both %N and %Q are for sub-second components, and one defaults to 3 digits, the other to 6 digits. Since you have exactly one digit, neither default will work and you must specify the 1 - %1Q or %1N are fine. Also, there is a period . between seconds and sub-seconds in your fields, not a colon :.

So, to properly extract your times...

| eval Starttime=strftime(START_TS,"%y-%m-%d %H:%M:%S:%N")

...should be...

| eval Starttime=strptime(START_TS,"%Y-%m-%d %H:%M:%S.%1N")

... and then when you subtract the two, your difference in epoch time will read out as the number of seconds between the two times.

locose
Path Finder

didnt work. Tried

| eval starthere_time=strptime(START_TS,"%y-%m-%d %H:%M:%S:%N")
| eval endhere_time=strptime(END_TS,"%y-%m-%d %H:%M:%S:%N")
|eval duration = endhere_time - starthere_time

0 Karma

3no
Communicator

Hey locose,

Try with strptime instead of strftime.

If it doesn't work try to change the name of your variable because starttime and endtime are already used by splunk (and I'm not sure about how it react about that):

https://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/SearchTimeModifiers

3no.

DalJeanis
SplunkTrust
SplunkTrust

@3no - splunk is case-sensitive in field names, so your comment about starttime and endtime is not part of the issue.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...