Archive

How to filter date based on the future data?

Explorer

Hi -

I have a SessionStartDate 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!

Tags (1)
0 Karma

Contributor

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

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

Contributor

I am assuming you have field called SessionStartDate 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 SessionStartDate and take first one OR two digit as Month and assign to the field ReportMonth. So once you are search for the ReportMonth 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.

Explorer

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.

0 Karma

Ultra Champion

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.

0 Karma

Contributor
0 Karma

Contributor

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

0 Karma

Explorer

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.
0 Karma

Explorer

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

My previous code is:

| where strptime('SessionStartDate',"%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.

0 Karma

Contributor

I am assuming you have field called SessionStartDate 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 SessionStartDate and take first one OR two digit as Month and assign to the ReportMonth. So once you are search for the ReportMonth 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.

0 Karma