Splunk Search

Removing intervals from span?

stevensa
Explorer

I am trying to report a statistic over the last X Business Days (7 or 30) by multiple hosts. The result chart should span that interval, but not include weekends. I've gotten my search to ignore weekends, and can even get a chart that does not have weekends, but unlike a timechart it is not sorted by date. Here is basically what I am doing:

"My Search" earliest=* latest=* NOT date_wday=saturday NOT date_wday=sunday 
| timechart span=1d avg(Field) by host

If I do that search then I get weekend dates in my timechart, even though there are no statistics for the weekend days themselves. An alternative theme I've tried is variations on:

"My Search" earliest=* latest=* NOT date_wday=saturday NOT date_wday=sunday 
| eval Day = strftime(_time, "%D") 
| chart avg(Field) over Day by host

However, all of the variations I have tried on that theme leave the days out of date order. I am brand new to Splunk, so if anyone can point me in the right direction to some way report accross a timespan while removing weekends... I would be very, very grateful. Thank you!

1 Solution

sideview
SplunkTrust
SplunkTrust

here you go. This will give you a table/chart with with x-axis values like "Mon", "Tue" etc.

"My Search" NOT date_wday=saturday NOT date_wday=sunday 
| timechart span=1d avg(Field) by host 
| eval dayname = strftime(_time, "%a") 
| search dayname!=Sat dayname!=Sun
| fields - _time

in the initial search clause we filter out the data from sat/sunday to save splunkd some work (if as you say there are literally no events on weekends then this is wasted effort).

Then we let timechart do it's thing. But then afterwards we create the dayname field from the _time values that timechart returns.

So then we can filter out the weekend rows again with another search clause.

Next step is that we throw away the _time values. This is because if the Flash charting system sees a _time field with epochtime-looking values, it will interpret that as a time axis (so saturday and sunday would come back from the dead at least visually).

From there if you want to get fancier and replace "Thu" with "Thu 01/05/10" in your axis labels, then you can eval the date and month in another field and then after the search filtering, glue yourself back a field that seems sensible:"

eg, this example gives you labels of "Thu 01/05/10"

"My Search" NOT date_wday=saturday NOT date_wday=sunday 
| timechart span=1d avg(Field) by host 
| eval dayname = strftime(_time, "%a")
| eval date = strftime(_time, " %D") 
| search dayname!=Sat dayname!=Sun 
| eval day=dayname + date 
| fields - _time dayname date

hey presto. Now your x-axis labels will be strings like "Tue 12/21/10", but only the weekdays.

View solution in original post

sideview
SplunkTrust
SplunkTrust

here you go. This will give you a table/chart with with x-axis values like "Mon", "Tue" etc.

"My Search" NOT date_wday=saturday NOT date_wday=sunday 
| timechart span=1d avg(Field) by host 
| eval dayname = strftime(_time, "%a") 
| search dayname!=Sat dayname!=Sun
| fields - _time

in the initial search clause we filter out the data from sat/sunday to save splunkd some work (if as you say there are literally no events on weekends then this is wasted effort).

Then we let timechart do it's thing. But then afterwards we create the dayname field from the _time values that timechart returns.

So then we can filter out the weekend rows again with another search clause.

Next step is that we throw away the _time values. This is because if the Flash charting system sees a _time field with epochtime-looking values, it will interpret that as a time axis (so saturday and sunday would come back from the dead at least visually).

From there if you want to get fancier and replace "Thu" with "Thu 01/05/10" in your axis labels, then you can eval the date and month in another field and then after the search filtering, glue yourself back a field that seems sensible:"

eg, this example gives you labels of "Thu 01/05/10"

"My Search" NOT date_wday=saturday NOT date_wday=sunday 
| timechart span=1d avg(Field) by host 
| eval dayname = strftime(_time, "%a")
| eval date = strftime(_time, " %D") 
| search dayname!=Sat dayname!=Sun 
| eval day=dayname + date 
| fields - _time dayname date

hey presto. Now your x-axis labels will be strings like "Tue 12/21/10", but only the weekdays.

sideview
SplunkTrust
SplunkTrust

Sweet. Happy to help. If you ever have larger questions than this site can comfortably accommodate, I do consulting and app-development on top of splunk full time. Feel free to contact me if you need more comprehensive help.

0 Karma

stevensa
Explorer

nick,
I want to thank you very, very much for taking the time to write out an extremely helpful, detailed and well written answer. Not only did this solve the problem but showed me how to approach some other minor issues I was having. You made my day.. thank you very much!

thall79
Communicator

For the past business week try this:

"My Search" earliest=-7d@w1 latest=@w6 | timechart span=1d avg(Field) by host

Travis.

0 Karma

thall79
Communicator

This search may work better:
"My Search" NOT date_wday="saturday" NOT date_wday="sunday" | convert ctime(_time) as Date timeformat="%Y/%m/%d" | sort _time
| chart avg(Field) over Date by host

0 Karma

thall79
Communicator

Ok after thinking about the 30day part I realize that search would not work. Give this a try and see if it's closer to what you are looking for.

"My Search" NOT date_wday="saturday" NOT date_wday="sunday" | strcat date_month "-" date_mday date | chart avg(Field) over date by host

0 Karma

stevensa
Explorer

Travis, thanks for your response. Unfortunately, my boss requires the past X number of Business days up to yesterday (past 7 business/weekdays for some reports, 30 for others). Combining the search query you proposed with one for the present business week results in the original problem. Thanks though 🙂

0 Karma

ftk
Motivator

You can do this as follows using bucket to aggregate your stats by day and then charting them:

"My Search" earliest=* latest=* NOT date_wday=saturday NOT date_wday=sunday
| bucket _time span=1d
| chart avg(Field) over _time by host

ftk
Motivator

That is interesting -- what version are you using? I tried this on my 4.1.6 install and it worked just fine. Make sure you use chart instead of timechart.

0 Karma

stevensa
Explorer

ftk,
I appreciate your response, however this still results in Saturday and Sunday showing up in the report that this search produces(leaving me with the original problem). Am I possibly missing an option in the report builder?
Thank you.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...