Splunk Search

How can I find the time duration between two fields?

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.

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 STARTTS="2017-08-16 04:07:00.0" | eval ENDTS="2017-08-16 04:12:00.0" | eval st = strptime(STARTTS, "%Y-%m-%d %H:%M:%S.%N") | eval et = strptime(ENDTS, "%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

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

SplunkTrust
SplunkTrust

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

0 Karma

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.

Path Finder

didnt work. Tried

| eval startheretime=strptime(STARTTS,"%y-%m-%d %H:%M:%S:%N")
| eval endheretime=strptime(ENDTS,"%y-%m-%d %H:%M:%S:%N")
|eval duration = endheretime - startheretime

0 Karma

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.

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