Custom Alert - Postgres Relation Growth Alert

DESCRIPTION

This alert works in conjunction with the COLLECT RELATION (TABLE) SIZES - POSTGRES alert in this same section. Please install that alert as a requirement for this alert. This alert will review the last 2 days of data from the collector alert and calculate the growth. You can use the thresholds in the alert to get warned when a database grows by more than 10% is a given day as an example.

ALERT DEFINITION

To create this alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return making it look similar to this example.

Alert Name: Relation Growth Alert - Postgres

Execution Interval: 1 Day

Notification Text: The following relations have grown by more than X% in the last day

Available Instances: <choose any Postgres instances>

SQL Statement:

DECLARE @CurrentCollection DATETIME
	,@PriorCollection DATETIME
	,@CollectorAlertID INT
	,@DBName VARCHAR(200);

-- Change this query to match the name you used for the collector alert name, this is the default
SELECT @CollectorAlertID = id
FROM con_alert
WHERE alertname = 'Collect Relation (Table) Sizes - Postgres';

SELECT @DBName = name
FROM cond
WHERE id = #DBID#

-- figure out the last collection and the one prior to that so we can compare
DECLARE col_cursor CURSOR
FOR
SELECT TOP 2 actiondate
FROM (
	   SELECT DISTINCT ah.actiondate   
	FROM con_alert a   
	INNER JOIN con_alert_history ah ON ah.alertid = a.id   
	WHERE a.id = @CollectorAlertID   
		AND ah.actiondate >= DATEADD(day, - 10, CURRENT_TIMESTAMP)   
		AND ah.dbname = @DBName
	) a
ORDER BY actiondate DESC;

OPEN col_cursor;

FETCH NEXT
FROM col_cursor
INTO @CurrentCollection;

FETCH NEXT
FROM col_cursor
INTO @PriorCollection;

CLOSE col_cursor;

DEALLOCATE col_cursor;

-- get the collected data and store into temp tables for later use
IF OBJECT_ID('tempdb..#TodaySizes') IS NOT NULL   
	DROP TABLE #TodaySizes

IF OBJECT_ID('tempdb..#YesterdaySizes') IS NOT NULL   
	DROP TABLE #YesterdaySizes

-- get alert's last run of sizes for all databases
SELECT ahr.parametername db_name
	,ah.actiondate
	,CONVERT(FLOAT, REPLACE(ahr.levelvalue, ',', '')) db_size_mb
INTO #TodaySizes
FROM con_alert a
INNER JOIN con_alert_history ah ON ah.alertid = a.id
INNER JOIN con_alert_history_results ahr ON ahr.historyid = ah.historyid
WHERE a.id = @CollectorAlertID
	AND ah.actiondate = @CurrentCollection
	AND ah.dbname = @DBName

-- get 2nd to last run of sizes for all database, so we can compare to previous run
SELECT ahr.parametername db_name
	,ah.actiondate
	,CONVERT(FLOAT, REPLACE(ahr.levelvalue, ',', '')) db_size_mb
INTO #YesterdaySizes
FROM con_alert a
INNER JOIN con_alert_history ah ON ah.alertid = a.id
INNER JOIN con_alert_history_results ahr ON ahr.historyid = ah.historyid
WHERE a.id = @CollectorAlertID
	AND ah.actiondate = @PriorCollection
	AND ah.dbname = @DBName

-- calculate the growth from yesterday to today and return the pct to DPA
SELECT TodaySizes.db_name
	,100.0 * (TodaySizes.db_size_mb - YesterdaySizes.db_size_mb) / YesterdaySizes.db_size_mb AS pct_growth
FROM #TodaySizes TodaySizes
INNER JOIN #YesterdaySizes YesterdaySizes ON TodaySizes.db_name = YesterdaySizes.db_name
WHERE TodaySizes.db_size_mb - YesterdaySizes.db_size_mb > 0

DROP TABLE #TodaySizes;

DROP TABLE #YesterdaySizes;

Execute Against: Repository

Units: Empty

High Threshold: Min: 10, Max: empty

Warning Threshold: Min: 5, Max: 10

REPORTING FROM THIS DATA

The data this alert collects is stored in the Ignite alert tables and can be retrieved running a query similar to this.

Note: this query retrieves data collected by this alert for the last 14 days. If the alert name was changed from the default listed above, change the where a.alertname part of this query as well.

SELECT ah.dbname instance_name
	,ah.actiondate TIMESTAMP
	,ahr.parametername database_name
	,convert(FLOAT, replace(ahr.levelvalue, ',', '')) pct_growth_from_yesterday
FROM con_alert a
INNER JOIN con_alert_history ah ON ah.alertid = a.id
INNER JOIN con_alert_history_results ahr ON ahr.historyid = ah.historyid
WHERE a.alertname = 'Relation Growth Alert - Postgres'
	AND ah.actiondate >= current_timestamp - 14.0
ORDER BY ah.dbname
	,ah.actiondate
	,ahr.parametername