Splunk Search

How to write a search that automatically compares volume for this year against the same day of the week last year?

Contributor

Greetings!

Trying to build a search that automatically compares volume for this year against the same day of the week last year - however, I am having trouble figuring out how to get the same day of the week a year ago...

earliest=1y@(monday last year) latest=1y@(monday last year)
Tags (3)
1 Solution

Revered Legend

Try this to set the earliest and latest dynamically using subsearch (from today).

your base search terms 
[| gentimes start=-1 | eval earliest=relative_time(now(),"-1y@w")+relative_time(now(),"@d")-relative_time(now(),"@w")  
| eval latest=relative_time(earliest,"+1d") | table earliest, latest| format "" "" "" "" "" ""]  
| rest of search

View solution in original post

Builder

The problem is that the day of the week changes, so 02/Dec today is Tue, last year 02/Dec/2013 was Sun... so, you'll need some coding to define wish Tuesday from last year to be used, the closest "up" or closest "down".

Now, the easier option is to use something like: earliest=-364d@d latest=-365d@d

As 364/7 = 52, you'll always get the values from exactly 52 weeks ago, at the same day of the week as Today. If Today is Tuesday and you want to check the results from yesterday (Monday), just subtract one (or add 6): earliest=-363d@d latest=-364d@d

Hopefully it'll give you some direction.

Revered Legend

Try this to set the earliest and latest dynamically using subsearch (from today).

your base search terms 
[| gentimes start=-1 | eval earliest=relative_time(now(),"-1y@w")+relative_time(now(),"@d")-relative_time(now(),"@w")  
| eval latest=relative_time(earliest,"+1d") | table earliest, latest| format "" "" "" "" "" ""]  
| rest of search

View solution in original post

Contributor

This seems to work! I like it!!

Any suggestions on working dynamics into a timechart that compares today vs last year (on the same day of the week)?

Here's my attempt

[| gentimes start=-1 | eval earliest=relative_time(now(),"-1y@w")+relative_time(now(),"@d")-relative_time(now(),"@w")
| eval latest=relative_time(earliest,"+1d") | table earliest, latest| format "" "" "" "" "" ""] | fields _time count | eval _time=_time+(364*24*60*60) | timechart count

0 Karma

Motivator

sideview's solution will only work for Monday. Because you can't set the earliest and latest dynamically, I suggest the following approach:

earliest=-1y@w1 latest=-1y@w6

Then you need a where clause to compare Today's wday to that weeks wday:

earliest=-1y@w1 latest=-1y@w6 | where date_wday=lower(strftime(now(),"%A"))
0 Karma

SplunkTrust
SplunkTrust

Use the Advanced mode in the timerange picker and set your earliest to -1y@w1 and your latest to -1y@w1+1d

It looks extremely strange but it is legal syntax and all documented http://docs.splunk.com/Documentation/Splunk/6.2.0/Search/Specifytimemodifiersinyoursearch although the docs could have more practical examples.

Basically it's saying go back a year, then snap down to 12am on the nearest monday. And the latest time then says go back a year, snap down to 12am on monday, then go 1 day forward from there. This will in many cases be equivalent to -1y@w2 but I recommend the more explicit way with +1d as a best practice -- In general you have to worry about the snapping behavior putting your latest time before the earliest time and the +1d removes that worry.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!