How to filter out weekdays or weekends in one search while using timewrap?

[penguin1725]

Hello!

I want to use Timewrap to do the following:
If it is a weekday, compare the current data stream to the weekdays in the past 7 days.
If it is a weekend day, compare the current data stream to the weekend days in the past 7 days.

I’ve seen other posts about how to do just one (i.e. just compare weekdays to weekdays). From what I’ve gathered, to just compare weekdays, the search comes down to something like (I’m running this over the last seven days with no snap-to):

| timechart span=15m avg(num_x) as avg_data | eval day_of_week = lower(strftime(_time, "%A")) | where day_of_week!="saturday" AND day_of_week!="sunday" | fields - wday | timewrap d | table _time, avg_data*

Is it possible, though, to somehow add a comparison of just weekend days on the weekend in the same search?

I’m very new to Splunk and my first thought was to use a subsearch with an if statement. This doesn’t work, but it shows my thought process:

| timechart span=15m avg(num_x) as avg_data | eval day_of_week = lower(strftime(_time, "%A")) | eval weekend=if(day_of_week="saturday" OR day_of_week="sunday", "true", "false") | eval test=if(weekend="false", [where day_of_week!="saturday" AND day_of_week!="sunday"], [where day_of_week="saturday" OR day_of_week="sunday"]) | fields - wday | timewrap d | table _time, avg_data*

Any suggestions on how to make this work would be so appreciated! Also, if there is any way to do this without a subsearch, that would be awesome!

Thanks in advance!

[somesoni2 · Accepted solution]

Try this

| timechart span=15m avg(num_x) as avg_data | eval day_of_week = lower(strftime(_time, "%A")) | eval Weekend=if(day_of_week="saturday" OR day_of_week="sunday",avg_data,null())  | eval Weekday=if(day_of_week!="saturday" AND day_of_week!="sunday",avg_data,null()) | table _time Weekday Weekend | timewrap d 

[penguin1725]

Many thanks for the response! This works great AND it doesn't have a subsearch! 🙂

Follow up question: The next thing I'm trying to do is compare the current day to the appropriate average (i.e. a weekday to the average of all weekdays in the search). I've been playing around, and my query is becoming quite the monstrosity.

Do you have any thoughts on how to make this work (this is over the last 7 days) ? Let me know if it would be better to open a new question.

| timechart span=15m avg(num_x) as avg_data | eval day_of_week=lower(strftime(_time, "%A")) | eval Weekend=if(day_of_week="saturday" OR day_of_week="sunday", avg_data,0) | eval Weekday=if(day_of_week!="saturday" AND day_of_week!="sunday",avg_data,0) | table _time Weekday Weekend | timewrap d series=short | rename Weekday_s0 AS current_weekday | rename Weekend_s0 AS current_weekend | addtotals Weekday_* AS sum_weekdays | addtotals Weekend_* AS sum_weekends | eval day=lower(strftime(_time, "%A")) | eval curr_day=if(day="saturday" OR day="sunday", current_weekend, current_weekday) | eventstats max(curr_day) as max_today | eval average=if(day_of_week="saturday" OR day_of_week="sunday", sum_weekends, sum_weekdays/5) | eval anom_upper=if(curr_day>10*average, max_today/2, 0) | eval anom_lower=if(curr_day<average/50, -(max_today/2), 0) | table _time, _span, curr_day, average, anom_upper, anom_lower

Right now only curr_day, anom_upper, and anom_lower are being graphed. average is nowhere in sight.
Note:
-I'm currently hardcoding the "divide by" value in calculating the average (I don't divide the weekend sum as there is only 1 weekend day to compare to right now, and I am dividing the weekday sum by 5).

-I'm assuming that addtotals will ignore any null values it might encounter.

Any thoughts would be so appreciated! Thanks!

[penguin1725]

To follow up on my comment above...this is definitely not very pretty, but it seems to be working:

| timechart span=15m avg(num_x) as avg_data | eval day_of_week=lower(strftime(_time, "%A")) | eval Weekend=if(day_of_week="saturday" OR day_of_week="sunday", avg_data,0) | eval Weekday=if(day_of_week!="saturday" AND day_of_week!="sunday",avg_data,0) | table _time Weekday Weekend | timewrap d series=short | rename Weekday_s0 AS current_weekday | rename Weekend_s0 AS current_weekend | addtotals Weekday_* fieldname=sum_weekdays | addtotals Weekend_* fieldname=sum_weekends | eval day=lower(strftime(_time, "%A")) | eval curr_day=if(day="saturday" OR day="sunday", current_weekend, current_weekday) | eventstats max(curr_day) as max_today | eval average=if(day="saturday" OR day="sunday", sum_weekends, sum_weekdays/5) | eval anom_upper=if(curr_day>10*average, max_today/2, 0) | eval anom_lower=if(curr_day<average/50, -(max_today/2), 0) | table _time, _span, curr_day, average, anom_upper, anom_lower

As I mentioned before, I am searching over the past 7 days and currently hardcoding the "divide by" value in calculating the average (I don't divide the weekend sum as there is only 1 weekend day to compare to right now, and I am dividing the weekday sum by 5). Any thoughts on how to get rid of the hardcoding (preferably without any subsearches) would be so appreciated!
Thanks!