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

Custom Alert - Abnormal Wait Times

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

Version history
Revision #:
1 of 1
Last update:
‎02-19-2014 11:18 AM
Updated by: