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!
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,"))
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! 🙂
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,"))
@skoelpin, thanks a lot for your reply! I just also found another way to do it! 🙂