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.
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'
WHERE CONVERT (float,uncompBytes) / POWER(2,30) >= @maxSize AND (userId=@userId OR dbo.isBkpJobVisible(@userId,commCellId,clientId,appTypeId,instance,backupSet,applicationId)=1)
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.
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.
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.
The following sample query returns transaction log backup jobs that are delayed more than a specified number of minutes.
DECLARE @delayedEvents table
DECLARE @clientIds table
INSERT INTO @clientIds
SELECT entityId FROM @clients
SELECT clientId FROM @clientGroups
INNER JOIN APP_ClientGroupAssoc
ON APP_ClientGroupAssoc.clientGroupId = entityId
INSERT INTO @delayedEvents
SELECT JobId , delayedFor ,
client , instance ,appType , backupSet , subclient
SELECT JobId , delayedFor ,
client , instance ,appType, backupSet , subclient
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
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
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.|
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.