topic Date comparison in Splunk Search

Date comparison

pjb2160 — Mon, 08 Dec 2014 05:38:44 GMT

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

Re: Date comparison

lguinn2 — Mon, 08 Dec 2014 07:50:40 GMT

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.

Re: Date comparison

pjb2160 — Mon, 08 Dec 2014 23:15:06 GMT

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