Splunk Search

How to search for events that occured on the last 4 weeks where the week starts on Monday?

Communicator

Hi Guys,

How do I search events that occurred on the last 4 work weeks that starts on Monday and doesn't include the current work week? Let say, I will search for the events today, 23/02 week 8. My search result should only have:
week4 - 22/01 to 28/01
week5- 29/01 to 04/02
week6- 05/2 to 11/02
week 7- 12/02 to 18/02

I am using the below query and relative time Earliest: 4 weeks ago, Latest: First Day of this week.

On the Earliest,
when I select "No Snap-to" -> the week starts on the current day, if I search on Fri the week starts on Fri
when I select "First day of the week" -> the day starts on Sunday

How can I make it start on Monday?

| bin _time span=1w | eval week=strftime(_time,"%V")  
| stats count as eventscount by _time week

Thanks!

1 Solution

SplunkTrust
SplunkTrust

You can either use timewrap to do this or you can use relative_time like this

| eval w=case(
(('_time' > relative_time(now(),"+1d@d-5w-30m")) AND ('_time' <= relative_time(now(),"+1d@d-5w+1d+30m"))),5,
(('_time' > relative_time(now(),"+1d@d-4w-30m")) AND ('_time' <= relative_time(now(),"+1d@d-4w+1d+30m"))),4,
(('_time' > relative_time(now(),"+1d@d-3w-30m")) AND ('_time' <= relative_time(now(),"+1d@d-3w+1d+30m"))),3,
(('_time' > relative_time(now(),"+1d@d-2w-30m")) AND ('_time' <= relative_time(now(),"+1d@d-2w+1d+30m"))),2,
(('_time' > relative_time(now(),"+1d@d-1w-30m")) AND ('_time' <= relative_time(now(),"+1d@d-1w+1d+30m"))),1)
, shift=case(isnotnull(w),(((((((((((((("+" + w) + "w-30m,+") + w) + "w-20m,+") + w) + "w-10m,+") + w) + "w-0m,+") + w) + "w+10m,+") + w) + "w+20m,+") + w) + "w+30m,")) 

View solution in original post

Communicator

While waiting for someone to answer my question, I also found a way to do it.

I included "earliest="-4w@w1" latest=@w1" on my search and it works just the way I wanted.

Found it from http://docs.splunk.com/Documentation/Splunk/6.6.2/Search/Specifytimemodifiersinyoursearch

Now I am starting to have a Happy Friday! 🙂

0 Karma

SplunkTrust
SplunkTrust

You can either use timewrap to do this or you can use relative_time like this

| eval w=case(
(('_time' > relative_time(now(),"+1d@d-5w-30m")) AND ('_time' <= relative_time(now(),"+1d@d-5w+1d+30m"))),5,
(('_time' > relative_time(now(),"+1d@d-4w-30m")) AND ('_time' <= relative_time(now(),"+1d@d-4w+1d+30m"))),4,
(('_time' > relative_time(now(),"+1d@d-3w-30m")) AND ('_time' <= relative_time(now(),"+1d@d-3w+1d+30m"))),3,
(('_time' > relative_time(now(),"+1d@d-2w-30m")) AND ('_time' <= relative_time(now(),"+1d@d-2w+1d+30m"))),2,
(('_time' > relative_time(now(),"+1d@d-1w-30m")) AND ('_time' <= relative_time(now(),"+1d@d-1w+1d+30m"))),1)
, shift=case(isnotnull(w),(((((((((((((("+" + w) + "w-30m,+") + w) + "w-20m,+") + w) + "w-10m,+") + w) + "w-0m,+") + w) + "w+10m,+") + w) + "w+20m,+") + w) + "w+30m,")) 

View solution in original post

Communicator

@skoelpin, thanks a lot for your reply! I just also found another way to do it! 🙂