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)
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
