- THWACK
- :
- Database Performance Analyzer (DPA)
- :
- DPA Documents
- :
- Custom Alert - Resource Metric Baseline Alert
- Article History
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Email to a Friend
- Printer Friendly Page
- Report Inappropriate Content
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)
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Email to a Friend
- Report Inappropriate Content
I tried to import your alert in the NPM 12.0.1 but it doesn't validate, do you have an updated version?
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Email to a Friend
- Report Inappropriate Content
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.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.
