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

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.

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

SQL Statement: Choose the proper statement based on your Repository DB Platform


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 Server Repository Statement:

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

Oracle Repository Statement:

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

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)

Tags (2)
Comments

I tried to import your alert in the NPM 12.0.1 but it doesn't validate, do you have an updated version?

joe-n​,

This alert isn't for NPM or the Orion product. It is for Database Performance Analyzer and should be entered in the DPA alerts page. 

Version history
Revision #:
1 of 1
Last update:
‎04-17-2014 10:08 AM
Updated by: