Trying to find the time duration between 2 fields
Field name : START_TS
Field name : END_TS
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.
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:
| 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
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
First, the difference between
strftime takes data that is in epoch form, and
forward to human-readable
strptime takes time data that is formatted for display, and strips (
strps) it back into epoch time,
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.
%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 -
%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")
| 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.
strptime instead of
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):