How to compare close date with start date?

[vishalduttauk]

Hi there,

I am new to this kind of analysis within Splunk but i've been asked to create a filter on events where the closed date is before the start date.

This is the search I have created but can't get it working:

index=main sourcetype="CRA_Consumer_Txt_data" | eval close_date=strftime(strptime(close_date,"%d%m%Y"),"%d/%m/%Y") | eval start_date=strftime(strptime(start_date,"%d%m%Y"),"%d/%m/%Y") | search close_date < start_date | table start_date, close_date

This is an example of what even is shown when i run that search

start_date        close_date

30/04/2021        23/05/2021

[isoutamo · Accepted solution]

Hi

You are quite close. When you are comparing dates it should always convert to epoch and then do the comparison.

index=main sourcetype="CRA_Consumer_Txt_data" 
| eval close_date_epoch = strptime(close_date,"%d%m%Y") 
| eval start_date_epoch = strptime(start_date,"%d%m%Y")
| where close_date_epoch < start_date_epoch
| eval close_date = strftime(close_date_epoch, "%d/%m/%Y"),
       start_date = strftime(start_date_epoch, "%d/%m/%Y")
| table start_date, close_date

[vishalduttauk]

Thanks @isoutamo that has worked a treat and I now know to use epoch for this type of comparison! 😀