Getting Data In

Minimise return records by filtering date field

New Member

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 submitdate > 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=itamincxml |stats latest(productname) as Application , latest(priority) as priority , latest(urgency) as urgency , latest(impact) as impact , latest(submitdate) as submitdate, latest(submitter) as submitter, latest(lastresolveddate) as lastresolveddate, latest(closeddate) as closeddate, latest(daysopen) as daysopen, latest(status) as status, latest(assignedgroup) as assignedgroup , latest(servicetype) as servicetype ,latest(description) as summary, latest(detaileddescription) as notes , latest(owner) as owner , latest(ownergroup) as ownergroup , latest(assignedsupportcompany) as assignedsupportcompany , latest(assignedsupportorganization) as assignedsupportorganization , latest(loginid) as loginid , latest(firstname) as firstname , latest(lastname) as lastname by incidentnumber|eval daysopen= round(((now()-(submitdate/1000))/86400),2)|eval submitdate=strftime(submitdate/1000,"%d/%m/%y %H:%M:%S")| eval lastresolveddate=strftime(lastresolveddate/1000,"%d/%m/%y %H:%M:%S")| eval closeddate=strftime(closeddate/1000,"%d/%m/%y %H:%M:%S")|lookup Departmentlist Employee AS loginid OUTPUTNEW Department|table incidentnumber, Application, priority, urgency, impact, submitdate, submitter, lastresolveddate, closeddate, daysopen, status, assignedgroup, servicetype, summary, notes, owner, ownergroup, assignedsupportcompany, assignedsupportorganization, loginid, firstname, last_name, Department|where Department!=""

Tags (2)
0 Karma
1 Solution

Super Champion

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

View solution in original post

Super Champion

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

View solution in original post

New Member

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.

0 Karma

Super Champion

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.

0 Karma