topic Re: How can i filter date? in Splunk Dev

How can i filter date?

mrccasi — Thu, 23 Mar 2017 07:09:55 GMT

Hi all. I would like to ask on how can I filter a date with a format "Feb-16" "Month-Year" in search command.

Report Month

May-15
Dec-15
Jan-16
Feb-16
Mar-16
Apr-16

And the result should be Feb-16 onward.

Report Month

Feb-16
Mar-16
Apr-16

Thank you.

Re: How can i filter date?

gfreitas — Tue, 29 Sep 2020 13:23:03 GMT

Is this "Report Month" text or Splunk _time? If it`s Splunk time you can use the time picker, you can also eval a new field based on _time with the epoch month and try to use a where month_epoch > epoch_base_month
Another way can be eval a new field with year + month in number and filter like 1602 (for Feb 16), 1603 (for Mar 16), 1604 (for Apr 16)

Re: How can i filter date?

adonio — Thu, 23 Mar 2017 12:52:16 GMT

take a look at this answer: https://answers.splunk.com/answers/513047/search-two-fields-in-one-csv-lookup.html#comment-514036
you can use the same concept

Re: How can i filter date?

DalJeanis — Thu, 23 Mar 2017 14:17:27 GMT

The strptime function unfortunately does not include a default to the first day of the month when extracting Mmm-yy or YYYY-MM format dates. Therefore, you have to concatenate a 01 onto the end and tell splunk that the 01 is the day.

This makes your test data...

| makeresults | eval mydate="May-15 Dec-15 Jan-16 Feb-16 Mar-16 Apr-16" | makemv mydate | mvexpand mydate

This converts it to epoch time and a display version of the epoch time...

| eval mydateEpoch=strptime(mydate."-01","%b-%y-%d")
| eval mydateDisplay=strftime(mydateEpoch,"%Y-%m-%d")

Either one of these filters for dates from Feb 2016 forward...

| where mydateDisplay>="2016-02-01"

...or...

| where mydateEpoch >= strptime("2016-02-01","%Y-%m-%d")

For efficiency reasons, you always want to filter out records as early as possible, before doing calculations, so this would be the proper order...

| makeresults | eval mydate="May-15 Dec-15 Jan-16 Feb-16 Mar-16 Apr-16" | makemv mydate | mvexpand mydate

| eval mydateEpoch=strptime(mydate."-01","%b-%y-%d")
| where mydateEpoch >= strptime("2016-02-01","%Y-%m-%d")
| eval mydateDisplay=strftime(mydateEpoch,"%Y-%m-%d")