Minimise return records by filtering date field

[Kaylenn]

Hi,

I am running the below script successfully. However, I would like to now minimise the return results by only collecting records that have a submit_date greater than "01 June 17" for example. I have tried a few options from threads found here but no success. The records either come back ignoring the date filter or no records come back at all.

I have tried the following after all other evals in my script:
|eval mylimit=strptime("01/06/2017 00:00:00", "%d/%m/%y %H:%M:%S")| where submit_date > mylimit
|eval mylimit=strftime("01/06/2017 00:00:00", "%d/%m/%y %H:%M:%S")| where submit_date > mylimit

Can anyone assist?

Full operation code:
index=itam sourcetype=itam_inc_xml |stats latest(product_name) as Application , latest(priority) as priority , latest(urgency) as urgency , latest(impact) as impact , latest(submit_date) as submit_date, latest(submitter) as submitter, latest(last_resolved_date) as last_resolved_date, latest(closed_date) as closed_date, latest(days_open) as days_open, latest(status) as status, latest(assigned_group) as assigned_group , latest(service_type) as service_type ,latest(description) as summary, latest(detailed_description) as notes , latest(owner) as owner , latest(owner_group) as owner_group , latest(assigned_support_company) as assigned_support_company , latest(assigned_support_organization) as assigned_support_organization , latest(login_id) as login_id , latest(first_name) as first_name , latest(last_name) as last_name by incident_number|eval days_open= round(((now()-(submit_date/1000))/86400),2)|eval submit_date=strftime(submit_date/1000,"%d/%m/%y %H:%M:%S")| eval last_resolved_date=strftime(last_resolved_date/1000,"%d/%m/%y %H:%M:%S")| eval closed_date=strftime(closed_date/1000,"%d/%m/%y %H:%M:%S")|lookup Department_list Employee AS login_id OUTPUTNEW Department|table incident_number, Application, priority, urgency, impact, submit_date, submitter, last_resolved_date, closed_date, days_open, status, assigned_group, service_type, summary, notes, owner, owner_group, assigned_support_company, assigned_support_organization, login_id, first_name, last_name, Department|where Department!=""

[cmerriman · Accepted solution]

you need submit_date in epoch to compare it to it to mylimit, which also should be in epoch.

try doing this at the end:

|eval submit_date1=strptime(submit_date,"%d/%m/%y %H:%M:%S")|eval mylimit=strptime("01/06/2017 00:00:00", "%d/%m/%y %H:%M:%S")| where submit_date1 > mylimit|fields - submit_date1 mylimit

you could also hardcode mylimit, since it isn't changing (that is 6/1/2017 CT):

|eval submit_date1=strptime(submit_date,"%d/%m/%y %H:%M:%S")|search submit_date1>1496296861|fields - submit_date1

[Kaylenn]

Thanks a lot cmerriman.

I could not get the first option to operate but the second one did the job. I will just use an online Epoch conversion tool as I need to change the filtering date.

[cmerriman]

yes, generally when the date is a static number, i use https://www.epochconverter.com/ to convert my timestamp and hardcode it as in the second option.

That first option doesn't work because i have a typo:

|eval mylimit=strptime("01/06/2017 00:00:00", "%d/%m/%Y %H:%M:%S")

four digit years need %Y, two digit years need %y.