Alerts and Notifications - Sample Alert Rules

The following alert rules are samples and can be modified to meet a specific need. Both samples include user inputs to give users more control over the alerts they create by using the custom alert rule. For example, users can enter the number of gigabytes that trigger an alert by entering a value in the maxSize user input.

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

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

CommCell Views