cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

alerts by times/days of the week

Jump to solution

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.

0 Kudos
1 Solution

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'.

View solution in original post

1 Reply

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'.

View solution in original post