Alerts and Notifications - Sample Alert Rules

Table of Contents

Backup Job More Than xGB

SQL Query

The following sample query is based on the RunningBackups database view. It returns backup jobs that back up more than the specified gigabytes of data. Using the @userId variable, the query checks the security applied to the creator of the alert. Because of this check, the creator of the alert receives alerts for jobs he or she has permission to view.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT jobID, UserName, clientId, appTypeId, instance, backupSet, applicationId, CONVERT (nvarchar(max), ROUND((CONVERT (float,uncompBytes)/POWER(2,30)),2))+' GB' as 'BackedupSize'
FROM RunningBackups
WHERE CONVERT (float,uncompBytes) / POWER(2,30) >= @maxSize AND (userId=@userId OR dbo.isBkpJobVisible(@userId,commCellId,clientId,appTypeId,instance,backupSet,applicationId)=1)

User Inputs

The maxSize input is used in the sample query and is created using the Manage Inputs button. When the alert rule is used to create an alert, users enter the number of gigabytes that trigger the alert. The comparison used in the query is greater than or equal to. For example, enter 10 to trigger an alert if a backup job is equal to or larger than 10 gigabytes.

Primary Key

To avoid receiving multiple alerts for one backup job, the JobId column was marked as the primary key column on the Please Specify Output Columns page of the Create New Alert Rule wizard.

Frequency

The frequency of this rule was set to 15 minutes on the on the How Often To Run This Query page of the Create New Alert Rule wizard.

Transaction Log Backup Job Delay

SQL Query

The following sample query returns transaction log backup jobs that are delayed more than a specified number of minutes.

DECLARE @delayedEvents table
(

JobId BIGINT,
delayedFor INT,
client INT,
Instance INT,
appType INT,
BackupSet INT,
Subclient INT
)


DECLARE @clientIds table
(
clientId int
)

INSERT INTO @clientIds
SELECT entityId FROM @clients
UNION
SELECT clientId FROM @clientGroups
INNER JOIN APP_ClientGroupAssoc
ON APP_ClientGroupAssoc.clientGroupId = entityId


INSERT INTO @delayedEvents
SELECT JobId , delayedFor ,
client , instance ,appType , backupSet , subclient
FROM (
SELECT JobId , delayedFor ,
client , instance ,appType, backupSet , subclient
FROM (
SELECT NTDdpDr.eventId AS eventId ,
(jobId_h * CAST (POWER(2.0,32.0) AS BIGINT ) + jobId_l ) AS JobId ,
DATEDIFF(MINUTE, occurtime, GETDATE()) AS delayedFor,
NTDdpDr.clientId AS client,
NTDdpDr.appTypeId AS appType,
NTDdpDr.instanceId AS instance,
NTDdpDr.backupSetId AS backupSet,
NTDdpDr.subClientId AS subclient
FROM NTDetectedAlerts AS DA
INNER JOIN NTDdpDr
ON NTDdpDr.eventId = DA.eventId
INNER JOIN APP_AppTypeGroups
ON APP_AppTypeGroups.appTypeId = NTDdpDr.appTypeId
INNER JOIN @clientIds as clientFilters
ON clientFilters.clientId = NTDdpDr.clientId
WHERE
alertType = 3 -- DATA PROTECTION
AND criteria = 5 -- NT_DELAYED
AND appGroup = 'Database'
AND backupLevel = 2 -- INCREMENTAL
AND processStat = 1
) AS detectedAlerts
WHERE delayedFor > @threshold
) AS matchingAlerts

SELECT DISTINCT JobId , delayedFor ,
client , appType , instance , backupSet , subclient
FROM @delayedEvents

User Inputs

The following user inputs are used in the sample query and are created using the Manage Inputs button:

Variable Name Variable Type Control Type Description
clients ClientEntity DROPDOWN Create as list was selected from the Add Inputs dialog box. When the alert rule is used to create an alert, users can select multiple clients from the drop-down box.
clientGroups ClientGroupEntity DROPDOWN Create as list was selected from the Add Inputs dialog box. When the alert rule is used to create an alert, users can select multiple client computer groups from the drop-down box.
threshold integer TEXTBOX When the alert rule is used to create an alert, users enter the delay threshold in minutes. For example, enter 10 to trigger an alert if the job is delayed more than 10 minutes.

Primary Key

To avoid receiving multiple alerts for one transaction log backup job, the JobId column was marked as the primary key column on the Please Specify Output Columns page of the Create New Alert Rule wizard.

Related Topics

Managing Inputs

Customizing Output Columns

Editing an Alert Rule

CommCell Views