topic Re: How can I find the time duration between two fields? in Splunk Search

How can I find the time duration between two fields?

locose — Thu, 17 Aug 2017 14:07:30 GMT

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.

Re: How can I find the time duration between two fields?

3no — Thu, 17 Aug 2017 14:19:18 GMT

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.

Re: How can I find the time duration between two fields?

DalJeanis — Thu, 17 Aug 2017 14:36:45 GMT

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

Re: How can I find the time duration between two fields?

locose — Tue, 29 Sep 2020 15:23:21 GMT

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

Re: How can I find the time duration between two fields?

DalJeanis — Thu, 17 Aug 2017 14:51:24 GMT

@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.

Re: How can I find the time duration between two fields?

3no — Thu, 17 Aug 2017 14:57:08 GMT

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

Re: How can I find the time duration between two fields?

myriadic — Tue, 29 Sep 2020 15:23:24 GMT

I found the problem(s):

"Year" has to be a capital "Y", instead of lowercase.
Before "%N", you have a colon, instead of a period.

Here's my working search:

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

Re: How can I find the time duration between two fields?

DalJeanis — Thu, 17 Aug 2017 15:43:28 GMT

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