Splunk Search

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

maximusdm
Communicator

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!

Tags (3)
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

maximusdm
Communicator

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

maximusdm
Communicator

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

maximusdm
Communicator

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

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