Hello,
I would like to compare two dates:
log_time 08/Dec/2014:15:36:34 +1100
_time 2014-12-08 15:36:34
It is my expectation that I can use the below query to compare these dates and identify any log entries where they do not match. This is part of my QA process over indexed log data.
I have been trying the following query but get no results:
index="sec_ldap" | eval compare=strftime(log_time,"%Y-%m-%d %H:%M:%S") | where compare!=strftime(_time,"%Y-%m-%d %H:%M:%S")
I have followed this up by changing the Boolean operator from "!=" to ">" to "<" and to "=" just to confirm it's not the Boolean operater that's yielding unexpected results. I still get no results.
I then ran the following query:
index="sec_ldap" | eval compare1=strftime(log_time,"%Y-%m-%d %H:%M:%S") | eval compare2=strftime(_time,"%Y-%m-%d %H:%M:%S") |table compare1 compare2 _raw
This is how I realised the formats, even after running the strftime() function, don't match (see above).
Any pointers? I'm wondering if it's a matter of the "log_time" value being considered a "String" whereas _time is considered a "Date" or "Time" data type?
Any help would be greatly appreciated.
Cheers,
P
Thanks very much for your response, I tried your suggestion but you used a %d instead of a %b for the format (no problem though easily resolved!!)
I also revised the query to compare a sub-string removing the "+1100" from log_time:
index="sec_ldap"
| eval log_time_sub=substr(log_time,1,20)
| eval compare1=strptime(log_time_sub,"%d/%b/%Y:%H:%M:%S")
| where compare1!=_time
Thanks again, much appreciated!
P
Thanks very much for your response, I tried your suggestion but you used a %d instead of a %b for the format (no problem though easily resolved!!)
I also revised the query to compare a sub-string removing the "+1100" from log_time:
index="sec_ldap"
| eval log_time_sub=substr(log_time,1,20)
| eval compare1=strptime(log_time_sub,"%d/%b/%Y:%H:%M:%S")
| where compare1!=_time
Thanks again, much appreciated!
P
Yes, it is because log_time is a string. Try it this way
index="sec_ldap"
| eval compare1=strptime(log_time,"%m/%d/%Y:%H:%M:%S")
| where compare1!=_time
This compares both variables in time format.