topic Re: How to edit my search to filter out from Sunday through Saturday date range? in Splunk Search

How to edit my search to filter out from Sunday through Saturday date range?

maximusdm — Mon, 16 Jan 2017 20:47:32 GMT

How do I present data from 2 weeks ago, last week and current week based on the following rule:
-the data range has to be from Sunday-Saturday

That means if today is Wednesday, I need to display data from last Sunday up to the current date (Wednesday).
And I would need the previous week, i.e, previous Sunday-Saturday and so forth.
Here is my current search but it is counting 7 consecutive days:

index=index001 sourcetype=free_delta          
earliest=-2w@w latest=now              
| timechart span=1w count("duration") AS Total             
| eval period=case(_time>=relative_time(now(),"@w"),"This Week", _time>=relative_time(now(),"-1w@w"),"Prior Week",1=1,"Third Week")              
| table period Total

How can I change it to filter out from Sunday through Saturday date range? Thanks!

Re: How to edit my search to filter out from Sunday through Saturday date range?

somesoni2 — Mon, 16 Jan 2017 21:49:11 GMT

How about this?

index=index001 sourcetype=free_delta          
 earliest=-2w@w latest=now     
 | eval period=case(_time>=relative_time(now(),"@w"),"This Week", _time>=relative_time(now(),"-1w@w"),"Prior Week",1=1,"Third Week")              
| stats count(duration) as Total by period

Re: How to edit my search to filter out from Sunday through Saturday date range?

maximusdm — Mon, 16 Jan 2017 21:57:54 GMT

sorry but how are you pulling Sunday-Saturday date range? it still looks like 7 consecutive days.

Re: How to edit my search to filter out from Sunday through Saturday date range?

somesoni2 — Mon, 16 Jan 2017 22:31:44 GMT

By default the week in Splunk starts from Sunday and ends in Saturday only. Did you check the dates returned by this query and see if they all are sundays??

index=index001 sourcetype=free_delta          
 earliest=-2w@w latest=now              
 | timechart span=1w count("duration") AS Total

Re: How to edit my search to filter out from Sunday through Saturday date range?

maximusdm — Tue, 17 Jan 2017 19:50:50 GMT

yes I see the dates but the example above brings me the data from Sunday the 8th till now. This is what I am trying to do. Giving today's date of 1/17/17 TUESDAY bring me the following data:
1/15/17 - 1/17/17 => This week - Sunday through Tuesday (today's date)
1/08/17 - 1/14/17 => Previous week
1/01/17 - 1/07/17 => The week before previous week

Re: How to edit my search to filter out from Sunday through Saturday date range?

somesoni2 — Tue, 17 Jan 2017 20:26:28 GMT

That doesn't seems right. If I ran a query like this on my Splunk, I get three rows with following dates. Each date is the sunday and give count of events for that week from Sunday to Saturday. So above query should work fine. What's the output you get??

Search: index=_internal sourcetype=splunkd earliest=-2w@w | timechart span=1w count

Result
_time count
2017-01-01 X
2017-01-08 Y
2017-01-15 Z

Re: How to edit my search to filter out from Sunday through Saturday date range?

maximusdm — Tue, 17 Jan 2017 21:01:00 GMT

If I run these individually I get the right dates for the 3 different periods:
earliest=-2w@w latest=@1w
earliest=-1w@w latest=@w
earliest=@w latest=now

Re: How to edit my search to filter out from Sunday through Saturday date range?

DalJeanis — Tue, 17 Jan 2017 21:51:42 GMT

Insert this into your search before you do the timechart -

 | eval MyDay = relative_time(_time,"-0d@d")
 | eval MySun = relative_time(_time,"-0d@w")
 | eval MySat = MySun+518400
 | eval SkipWeekend = case(MyDay==MySun,1,MyDay==MySat,1,true(),0)
 | search SkipWeekend=0

Translation of the above code -
set MyDay to the epoch time of the day portion of _time
set MySun to the epoch time of the current/prior Sunday of _time
set MySat to the epoch time of the Saturday which follows MySun. (518400 is the number of seconds in six days.)
set SkipWeekend to 1 if MyDay is either MySat or MySun
search for events where SkipWeekend is 0

The above code will work even if you plan your weeks to be Monday thru Sunday -- or even Wednesday thru Tuesday -- because regardless of what fiscal week a day might be in, it's still a Saturday.