alerts by times/days of the week

Question

Hey guys, So I'm trying to pull alert history, but I want to only grab alerts that were triggered for specific times of the day. (i.e. sunday 7-7 and 10-12, monday 6-7 and 10-12 etc. etc.) Wondering if anyone has done something like that. My trouble is understanding how swql evaluates parentheses in AND/OR statements. Examples that I've seen so far haven't been as complex. This is the chunk that's confusing me: ... WHERE --SUNDAY ( ah.eventtype = 0 and weekday(ah.timestamp) = 0 and Hour(ah.timestamp) >= 7 and Hour(ah.timestamp) < 19 ) or ( ah.eventtype = 0 and weekday(ah.timestamp) = 0 and Hour(ah.timestamp) >= 22 ) --MONDAY THROUGH THURSDAY or ( ah.eventtype=0 and weekday(ah.timestamp) >=1 and weekday(ah.timestamp) <=4 and Hour(ah.timestamp) < 19 ) or ( ah.eventtype=0 and weekday(ah.timestamp) >=1 and weekday(ah.timestamp) <=4 and Hour(ah.timestamp) >=22 and Hour(ah.timestamp) < 24 ) --FRIDAY AND SATURDAY or ( ah.eventtype=0 and weekday(ah.timestamp) >= 5 and weekday(ah.timestamp) <=6 and Hour(ah.timestamp) >= 7 and Hour(ah.timestamp) < 19 ) I figure I'll get 'er soon enough with trial and error, but any insight y'all have would be greatly appreciated.

gwilliamson11 · Accepted Answer

As it turns out, that syntax was valid, and wasn't what was causing my erratic results. What I was failing to do was specify 'tolocal' for both the weekday function as well as the Hour function. Like this:

...

--MONDAY

or
((
ah.eventtype=0
and weekday(tolocal(ah.timestamp)) = 1
and Hour(tolocal(ah.timestamp)) < 19
))
or
((
ah.eventtype=0
and weekday(tolocal(ah.timestamp)) = 1
and Hour(tolocal(ah.timestamp)) >=22
and Hour(tolocal(ah.timestamp)) < 24
))

...

I hope somebody find that useful. We're using this report to look at the duration between an alert going active and it being acknowledged, but only during times we have someone assigned as 'Eyes on Glass'.