Splunk Search

How to compare day to same day for N weeks?

michaudel
Explorer

I got a question where someone is looking for the hits to a page, but only on Fridays between 6PM and 2 AM the following Saturday. I looked around a bit for a good way to do this and hadn't come up with anything so I thought I would ask.

The challenge here is the fact that we only care about Fridays during a certain time period.

I started with this:
earliest=@w5+18h latest=@w6+2h index=...

but the problem is this only gets me last Friday. I would like to pull every friday for the last "n" weeks.

So i wrote this and it works pretty well, but what I don't like is that splunk still searches every day, only to throw out all but a few days. Thought i would see if anyone had a better way to do this, thanks, Ethan

index=... ... | eval sdate = strftime(_time,"%a %d %B %Y - %H") | eval day = strftime(_time,"%a") | eval hour = strftime(_time,"%H") |search hour >= "18" OR hour = "00" OR hour = "01" AND day = "Fri" |stats count(hits) as HIT by sdate

Labels (1)
1 Solution

kristian_kolb
Ultra Champion

There is a much easier way to do this. It will not work for all types of data (the only example of which, AFAIK, are WinEventLogs). So most logfile types will work.

Splunk will automatically (for each event) create fields called date_hour, date_wday, date_minute etc, which can be used for this purpose, so;

sourcetype=blah (date_wday=friday date_hour>18) OR (date_wday=saturday date_hour<2) | ...

would find those events.

NB. These date_* fields will be created from the timestamp inside the event WITHOUT compensating for TZ.

/K

View solution in original post

Lucas_K
Motivator

Once you have your data using a base search like Kristian's above you can use something like this ( http://splunk-base.splunk.com/answers/59045/how-do-i-make-a-multi-dimension-timechart ) to chart your comparisons.

Here is a good blog post also explaining how to display overlapping time frames ( http://blogs.splunk.com/2012/02/19/compare-two-time-ranges-in-one-report ).

michaudel
Explorer

Thanks I checked it out, got to where i need from above, then started to work to make the overlapping time frames... thanks again

0 Karma

kristian_kolb
Ultra Champion

There is a much easier way to do this. It will not work for all types of data (the only example of which, AFAIK, are WinEventLogs). So most logfile types will work.

Splunk will automatically (for each event) create fields called date_hour, date_wday, date_minute etc, which can be used for this purpose, so;

sourcetype=blah (date_wday=friday date_hour>18) OR (date_wday=saturday date_hour<2) | ...

would find those events.

NB. These date_* fields will be created from the timestamp inside the event WITHOUT compensating for TZ.

/K

sjbriggs
Path Finder

Thanks for this.  Sometimes we overthink solutions and fail to see the easiest one is right in front of us.  I spent all morning trying timewrap and a variety of datetime math solutions because all I wanted to do was compare the 11am hour of bytes per host every day of the week to troubleshoot a problem.  

Much appreciated 🙂

Tags (1)
0 Karma

michaudel
Explorer

Thanks that worked perfectly

0 Karma