topic Re: When I Compare a field that has dates, the results brings dates out of the comparison in Splunk Search

When I Compare a field that has dates, the results brings dates out of the comparison

asotorod — Wed, 05 Jul 2017 17:53:41 GMT

I'm trying to filter a field when date is greater than 07/05/2017

The date fild format is as follows : DD-MMM-YY Ex. 30-SEP-17

My search

index="eolr" sourcetype="FinDeVida.csv" "LDoS Date">"05-JUL-17" AND Slot=Chassis | stats count by "SNMP Name" "LDoS Date"

Brings dates from 2013 or 2012

Ex.
SNMP Name LDoS Date count
cisco12410xr 31-JAN-13 5
cisco2611XM 31-MAR-12 13

The less-equal does not bring any result as well as the graeater-equal even there are dates matching the criteria.

Any insight is appreciated.

Re: When I Compare a field that has dates, the results brings dates out of the comparison

DalJeanis — Wed, 05 Jul 2017 19:27:24 GMT

So, if you want to compare dates, you have to make them comparable. That means use strptime to pull it into epoch time, and compare using epoch time, and/or (optionally) use strftime to put it into a format that can be directly compared.

index="eolr" sourcetype="FinDeVida.csv"  Slot=Chassis 
| eval myLDoSdate = strptime('LDoS Date',"%d-%b-%y")
| search myLDoSdate > strptime("05-Jul-17","%d-%b-%y")
| eval myLDoSdate = strftime(myLDoSdate,"%Y-%m-%d")
| stats count by "SNMP Name" myLDoSdate

Updated to put 'LDoS Date' in single quotes instead of double quotes.

Re: When I Compare a field that has dates, the results brings dates out of the comparison

richgalloway — Wed, 05 Jul 2017 19:32:58 GMT

Dates and times cannot be compared as strings. They should be converted into epoch form for comparison. Try this query

index="eolr" sourcetype="FinDeVida.csv" "LDoS Date"=* AND Slot=Chassis | eval eLDosDate=strptime('LDos Date', "%d-%b-%y") | eval minDate=strptime("05-JUL-17", "%d-%b-%y") | where eLDosDate > minDate | stats count by "SNMP Name" "LDoS Date"

Re: When I Compare a field that has dates, the results brings dates out of the comparison

asotorod — Wed, 05 Jul 2017 20:35:40 GMT

Thank you Dal, for your answer, I tried the searches you set in different ways but no results at all. I add the images.alt text

Re: When I Compare a field that has dates, the results brings dates out of the comparison

somesoni2 — Wed, 05 Jul 2017 21:55:15 GMT

In the strptime for lDoS Date, @DalJeanis's answer has single quotes (very important), your screenshots have double quotes, causing it to fail. Change it to single quotes.

Re: When I Compare a field that has dates, the results brings dates out of the comparison

asotorod — Wed, 05 Jul 2017 23:22:38 GMT

Great it worked smooth, I Just made two adjustments in syntax

index="eolr" sourcetype="FinDeVida.csv" "LDoS Date"="*" AND Slot=Chassis | eval eLDosDate=strptime('LDoS Date', "%d-%b-%y") | eval minDate=strptime("05-JUL-17", "%d-%b-%y") | where eLDosDate > minDate | stats count by "SNMP Name" "LDoS Date"

"LDoS Date"="*" and strptime('LDoS Date'

Thank you very much for yor quick and prompt answer.

Re: When I Compare a field that has dates, the results brings dates out of the comparison

aaraneta_splunk — Wed, 05 Jul 2017 23:40:16 GMT

@asotorod - If the answer provided by richgalloway provided a working solution to your question, please don't forget to resolve this post by clicking "Accept". Thanks!