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.
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 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
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
@3no - this should work, but you need to account for the ".%1Q" in the original field.
@locose -
First, the difference between strftime
and strptime
is f
for F
ORMAT, p
for P
ULL.
strftime
takes data that is in epoch form, and f
ormats it f
orward to human-readable f
orm.
strptime
takes time data that is formatted for display, and strips ( strp
s) it back into epoch time, p
erfect for p
erforming p
roductive 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.
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
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.
@3no - splunk is case-sensitive in field names, so your comment about starttime and endtime is not part of the issue.