Getting Data In

Minimise return records by filtering date field

Kaylenn
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 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!=""

Tags (2)
0 Karma
1 Solution

cmerriman
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

cmerriman
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

Kaylenn
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

cmerriman
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...