Custom Alert - Resource Metric Baseline Alert

Description

This alert will compare the avg of the last X (matches frequency in Minutes) iterations for the metric specified and compare against the last 14 day average and standard deviation for that metric. It returns 0 if the current metric avg is not higher then previous 14 day baseline average which has a standard deviation multiplier factored in. (You can change the default of 2x multiplier. You can also change out the Metric used.

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.

Definition

To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:

• Alert Name: CPU Utilization Baseline Alert
• Execution Interval: 10 Min
• Notification Text: CPU Utilization is greater than the 14 day baseline.
• Execute Against: Repository
• Units:
• High Threshold: MIN 1, MAX empty  (You have to set this exactly as shown. This script returns 1 for a true condition and 0 for a false)

Define the Metric Name you want to run for example 'CPU Utilization', 'Signal Waits Percent', 'SQL Disk Write Latency'.

To find the available metrics first find your DB Instance ID using the query (SELECT * from COND) Then run this query ( SELECT * from CON_METRICS_NAMES_[MYDBID] ) replace [MYDBID] with the results from COND.

SQL Statement (SQL Server)

--Definition:
-- You can change the default of 2x multiplier with variable @StdDevMultiplier
-- You can change the metric this runs for with variable @Metric_Name
--Start Alert Logic
DECLARE @Metric_Name VARCHAR(255) = 'CPU Utilization'
DECLARE @StdDevMultiplier FLOAT = 2.0

SELECT 'Current Value is: ' + cast(Today.Avg_Value AS VARCHAR(20)) + '  Last 14 Day Avg: ' + cast(History.Avg_Value AS VARCHAR(20)) + '  Std Dev of: ' + cast(History.Std_Dev AS VARCHAR(20)) AS Alarm_Avg_Value
     , CASE 
          WHEN Today.Avg_Value > History.Avg_Value + (History.Std_Dev * @StdDevMultiplier)
               THEN 1
          ELSE 0
          END AS Alarm_True
FROM (
     SELECT coalesce(AVG(a.V * 1.0), - 1) Avg_Value
     FROM (
          SELECT TOP (#FREQUENCY#) md.V
          FROM CON_METRICS_NAMES_#DBID# mn
          INNER JOIN CON_METRICS_#DBID# m
               ON m.METRIC_NAME_ID = mn.ID
          INNER JOIN CON_METRICS_DETAIL_#DBID# md
               ON m.ID = md.METRICS_ID
          WHERE mn.NAME = @Metric_Name
          ORDER BY md.D DESC
          ) a
     ) Today
     , (
          SELECT coalesce(AVG(md.V * 1.0), - 1) Avg_Value
               , stdev(md.V) Std_Dev
          FROM CON_METRICS_NAMES_#DBID# mn
          INNER JOIN CON_METRICS_#DBID# m
               ON m.METRIC_NAME_ID = mn.ID
          INNER JOIN CON_METRICS_DETAIL_#DBID# md
               ON m.ID = md.METRICS_ID
          WHERE mn.NAME = @Metric_Name
               -- go back for 14 days to determine history
               AND md.D BETWEEN CURRENT_TIMESTAMP - 14.0
                    AND CURRENT_TIMESTAMP - 1
                         -- only compare time today to same timeframes on other days
               AND DATEPART(HOUR, md.D) <= DATEPART(HOUR, CURRENT_TIMESTAMP)
               -- only run this query between 8am and 8pm
               --AND DATEPART(HOUR,CURRENT_TIMESTAMP) BETWEEN 8 and 20
          ) History

SQL Statement (Oracle)

SELECT 'Current Value is: ' || ROUND(Today.Avg_Value, 2) || ' Last 14 Day Avg: ' || ROUND(History.Avg_Value, 2) || ' Std Dev of: ' || ROUND(History.Std_Dev, 2) AS Alarm_Avg_Value
     , CASE 
          WHEN Today.Avg_Value > History.Avg_Value + (History.Std_Dev * 2)
               THEN 1
          ELSE 0
          END AS Alarm_True
FROM (
     SELECT coalesce(AVG(a.V * 1.0), - 1) Avg_Value
     FROM (
          SELECT V
          FROM (
               SELECT md.V
               FROM CON_METRICS_NAMES_#DBID# mn
               INNER JOIN CON_METRICS_#DBID# m
                    ON m.METRIC_NAME_ID = mn.ID
               INNER JOIN CON_METRICS_DETAIL_#DBID# md
                    ON m.ID = md.METRICS_ID
               WHERE mn.NAME = 'Disk Read Time - Commits'
               ORDER BY md.D DESC
               )
          WHERE ROWNUM <= 3
          ) a
     ) Today
     , (
          SELECT coalesce(AVG(md.V * 1.0), - 1) Avg_Value
               , stddev(md.V) Std_Dev
          FROM CON_METRICS_NAMES_#DBID# mn
          INNER JOIN CON_METRICS_#DBID# m
               ON m.METRIC_NAME_ID = mn.ID
          INNER JOIN CON_METRICS_DETAIL_#DBID# md
               ON m.ID = md.METRICS_ID
          WHERE mn.NAME = 'Disk Read Time - Commits'
               -- go back for 14 days to determine history
               AND md.D BETWEEN CURRENT_TIMESTAMP - 14.0
                    AND CURRENT_TIMESTAMP - 1
                         -- only compare time today to same timeframes on other days
               AND TO_NUMBER(TO_CHAR(md.D, 'HH24')) <= TO_NUMBER(TO_CHAR(CURRENT_TIMESTAMP, 'HH24'))
               -- only run this query between 8am and 8pm
               --AND DATEPART(HOUR,CURRENT_TIMESTAMP) BETWEEN 8 and 20
          ) History