topic Re: How to filter date based on the future data? in Splunk Search

How to filter date based on the future data?

mrccasi — Tue, 29 Sep 2020 17:26:21 GMT

Hi -

I have a Session_Start_Date field that needed to be filter. The condition is that, for example, the data upload were at October 2017, the session start date field needs to be filtered 1 month onward from October. so I need to get the datas starting Nov onwards since this is a future report. Is there any way to do this?

Thank you!

Re: How to filter date based on the future data?

Kwip — Wed, 27 Dec 2017 08:46:14 GMT

Please give some example on what you are looking for and the sample output.

Re: How to filter date based on the future data?

mrccasi — Wed, 27 Dec 2017 09:03:49 GMT

Hi @Kwip

for example, I have a date below:

Session_Start_Date

10/30/2017
9/25/2017
11/15/2017
11/26/2017
9/25/2017

This is a data for the month of October, I only need to get the date starting November onward. The data is uploaded monthly.
So for example the month is November, I need to get the data starting December onward. Hope this helps. Thank you.

Re: How to filter date based on the future data?

mrccasi — Tue, 29 Sep 2020 17:26:26 GMT

so the output should only be 11/25/2017 and 11/26/2017.

My previous code is:

| where strptime('Session_Start_Date',"%m/%d/%Y") >= strptime("11/1/2017","%m/%d/%Y")

But since data is uploaded monthly, the date should not be hardcoded like the 11/1/2017.

Re: How to filter date based on the future data?

nikita_p — Wed, 27 Dec 2017 09:10:55 GMT

Hi @mrccasi,
could you check if below splunk docs helps you.
https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/Data/Whitelistorblacklistspecificincomingdata

Re: How to filter date based on the future data?

nickhills — Wed, 27 Dec 2017 09:16:29 GMT

If i have understood correctly, you should be able to use time snapping for this:

If you set your earliest search time to -1mon@mon it will restrict the search to events which have only occurred since the 1st day of the current month.

See https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/SearchTimeModifiers for more details.

Re: How to filter date based on the future data?

Kwip — Tue, 29 Sep 2020 17:26:29 GMT

I am assuming you have field called Session_Start_Date and that field values are in the format given below,
mm/dd/yyyy
10/30/2017
9/25/2017
11/15/2017
11/26/2017
9/25/2017

your base query
| rex field=Session_Start_Date"(?<Report_Month>\d{1,2})" 
| stats count by Report_Month 
| search Report_Month>10

The above query will rex the field Session_Start_Date and take first one OR two digit as Month and assign to the field Report_Month. So once you are search for the Report_Month value greater than the current month value you can get the result you are looking for.

Please check and let me know if you face any issues.
You can use table or stats with the fields you want to use.

Re: How to filter date based on the future data?

Kwip — Tue, 29 Sep 2020 17:26:32 GMT

I am assuming you have field called Session_Start_Date and that field values are in the format given below,
mm/dd/yyyy
10/30/2017
9/25/2017
11/15/2017
11/26/2017
9/25/2017

your base query
| rex field=Session_Start_Date"(?<Report_Month>\d{1,2})" 
| stats count by Report_Month 
| search Report_Month>10

The above query will rex the field Session_Start_Date and take first one OR two digit as Month and assign to the Report_Month. So once you are search for the Report_Month value greater than the current month value you can get the result you are looking for.

Please check and let me know if you face any issues.

Re: How to filter date based on the future data?

andrey2007 — Tue, 29 Sep 2020 17:26:35 GMT

[base of your search ] earliest=[ search ...search_to_restrict_session_start_date| eval date=strptime(Session_Start_Date, "%m/%d/%Y") | eval date=relative_time(date, "+1mon@mon") | return $date]...

So you will get events starting from the beginning of next month after selected Session_Start_Date

Re: How to filter date based on the future data?

mrccasi — Thu, 28 Dec 2017 02:32:07 GMT

Hello -

Thank you. I've tried your query but the issue is when the month is november which is 11 it should get the data for january onwards. So i dont think the

| search Report_Month>10 query is okay? and since January is 01, i dont know if > can be use.