DESCRIPTION
This alert is very similar to a red wait time bar on the Ignite Home page, i.e. it will warn when the wait time so far today is above it's normal behavior. For example, if it's currently 9am, this alert will determine the normal wait time as of 9am and then compare today's time against that.It will return a percentage of increase for today. If the normal wait time is 500 seconds and today has 700 seconds, the alert will return 40 (700-500/500)*100. A good threshold to set for this alert depends on how stable your wait times are, the below it uses 10-25% for a warning and over 25% for critical.
Also included below in the query, but commented out, is the concept of running this alert between 8am and 8pm, which may be more important than other timeslices. If you only want to be alerted for certain times, you can uncomment that and adjust the timeframe.
Warning: This alert has different SQL Scripts based on the Ignite Repository database platform (SQL Server vs Oracle). See below for proper script.
Available Database Instances: All database platforms are supported as this alert executes against the Ignite repository.
ALERT DEFINITION
To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Single Numeric Return. Configure the alert with values similar to these:
Alert Name: Abnormal Wait Time
Execution Interval: 1 Hour
Notification Text: This instance has more wait time than normal so far today.
SQL Statement: Choose the proper statement based on your Repository DB Platform
SQL Server Repository Statement:
SELECT COALESCE(ROUND(100.0*today.today_time/history.baseline_time,0),0)-100.0 pct_increase
FROM (
SELECT COALESCE(SUM(evs.timesecs),0) today_time
FROM con_event_sum_#DBID# evs
-- grab the timing data for today so far
WHERE evs.datehour >= CONVERT(date,CURRENT_TIMESTAMP,101)) today,
(
SELECT COALESCE(AVG(timesecs),1) baseline_time
FROM (
SELECT CONVERT(date,evs.datehour,101) as [day], SUM(evs.timesecs) timesecs
FROM con_event_sum_#DBID# evs
INNER JOIN conev_#DBID# ev ON ev.id = evs.eventid
WHERE evs.period='H'
-- go back for 14 days to determine history
AND evs.datehour between CURRENT_TIMESTAMP-14.0 and CURRENT_TIMESTAMP
-- only compare time today to same timeframes on other days
AND DATEPART(HOUR,evs.datehour) <= DATEPART(HOUR,CURRENT_TIMESTAMP)
-- only run this query between 8am and 8pm
--AND DATEPART(HOUR,CURRENT_TIMESTAMP) BETWEEN 8 and 20
GROUP BY CONVERT(date,evs.datehour,101)
) a) history
Oracle Repository Statement:
SELECT COALESCE(ROUND(100.0*today.today_time/history.baseline_time,0),0)-100.0 pct_increase
FROM (
SELECT COALESCE(SUM(evs.timesecs),0) today_time
FROM con_event_sum_1 evs
-- grab the timing data for today so far
WHERE evs.datehour >= TRUNC(CURRENT_TIMESTAMP)) today,
(
SELECT COALESCE(AVG(timesecs),1) baseline_time
FROM (
SELECT TRUNC(evs.datehour) as day, SUM(evs.timesecs) timesecs
FROM con_event_sum_1 evs
INNER JOIN conev_1 ev ON ev.id = evs.eventid
WHERE evs.period='H'
-- go back for 14 days to determine history
AND evs.datehour between CURRENT_TIMESTAMP-14.0 and TRUNC(CURRENT_TIMESTAMP)
-- only compare time today to same timeframes on other days
AND TO_NUMBER(TO_CHAR(evs.datehour,'HH24')) <= TO_NUMBER(TO_CHAR(CURRENT_TIMESTAMP,'HH24'))
-- only run this query between 8am and 8pm
--AND TO_NUMBER(TO_CHAR(CURRENT_TIMESTAMP,'HH24')) BETWEEN 8 and 20
GROUP BY TRUNC(evs.datehour)
) a) history
Execute Against: Repository
Units: Percent Increase in Wait Time
High Threshold: MIN 25, MAX empty
Medium Threshold: MIN 10, MAX 25
