Bringing Alerts and Operators together with Notifications

FatherJack

I have covered SQL Server Alerts (Alerts are good, arent they?) on this blog before and I more recently did a post regarding Notifications (Are your Jobs talking to you) and how they should be configured. Now we need to check that these things are linked up so that when an Alert condition is met that you get the appropriate Notifications sent to Operators.
Straight into the code we need and then a review of what it does ... DECLARE @ChosenOperator SYSNAME
DECLARE @FailSafeOp TABLE
    (
      AlertFailSafeOperator NVARCHAR(255) ,
      AlertNotificationMethod INT ,
      AlertForwardingServer NVARCHAR(255) ,
      AlertForwardingSeverity INT ,
      AlertPagerToTemplate NVARCHAR(255) ,
      AlertPagerCCTemplate NVARCHAR(255) ,
      AlertPagerSubjectTemplate NVARCHAR(255) ,
      AlertPagerSendSubjectOnly NVARCHAR(255) ,
      AlertForwardAlways INT
    )
-- Table to hold results of procedure to query server settings INSERT  INTO @FailSafeOp
        EXEC MASTER.[dbo].[sp_MSgetalertinfo] @includeaddresses = 0
         IF EXISTS ( SELECT  1
            FROM    @FailSafeOp AS fso
            WHERE   [fso].[AlertNULLafeOperator] IS NOT NULL )
    SELECT  @ChosenOperator = AlertFailSafeOperator
    FROM    @FailSafeOp AS fso ELSE
    RAISERROR('No FailSafeOperator found. You should alter your SQL Agent settings to include one.',16,0,1) WITH NOWAIT
-- Assumes that the Operator "DBA_Team" exists and has an email address detail SET @ChosenOperator = ISNULL(@ChosenOperator, N'DBA_Team' )
-- Output the results of whether there are Alerts with no assigned operator notification SELECT  [s].[name] ,
        [s].[severity] ,
        ISNULL([s3].[name], '| = - No operator assigned - = |') AS [Operator] ,
        CASE WHEN [s3].[name] IS NULL
             THEN 'EXEC [dbo].[sp_add_notification] @alert_name = N'
                  + QUOTENAME([s].name, '''') + ', @operator_name = N'
                  + QUOTENAME(@ChosenOperator, '''')
                  + ', @notification_method = 1'
        END AS [Add Notification TSQL] ,
        [s3].[enabled] ,
        [s3].[email_address] ,
        [s].[occurrence_count] ,
        CASE [s2].[notification_method]
          WHEN 1 THEN 'Email'
          WHEN 2 THEN 'Pager'
          WHEN 3 THEN 'netsend'
          WHEN 7 THEN 'All'
          ELSE CONVERT(CHAR(30), [s2].[notification_method])
        END AS [Notification Method] FROM    [dbo].[sysalerts] AS s
        LEFT  JOIN [dbo].[sysnotifications] AS s2 ON [s].[id] = [s2].[alert_id]
        LEFT JOIN [dbo].[sysoperators] AS s3 ON [s2].[operator_id] = [s3].[id] WHERE   [s].[enabled] = 1 -- include this line if you want to only see problem alerts
        AND [s3].[id] IS NULL
So this code creates a temporary table variable to hold operator information briefly;
runs the undocumented sp_msgetalertinfo stored procedure (some details here) to get failsafe operator data from the registry on the server;
queries sysalerts, sysnotifications and sysoperators to check that all alerts are set to notify operators
Where there is no operator being notified then the [Add Notification TSQL] column will have the TSQL to create a notification to the failsafe operator, if there is one.

As always, understand this code, especially the way that you can't rely on undocumented stored procedures to be consistent or reliable, before you run this on any of your servers, especially those that hold information that is precious to you.