We had a need to check application processes once and a while to be sure that they are running. So I created this table and stored procedure for an alerts jobs or exception reporting. At the time we did not have Data Driven Subscriptions in SSRS, which can do the same thing as this process, and I haven’t had the time to convert it.
It’s a simple structure; the table holds the information about what to check, the stored procedure to run using sp_ExecuteSQL, when to run it, how often to check it, who to send emails to using our friendly SQL mailer sp_send_dbmail. The store procedure reads from the table, figures out which alerts need to run. The store procedure that gets called needs to have two Output parameters @AlertStatus (INT) that will be 1 or 0 and @Info Varchar(500) which is additional information that will be put into the email. See BOL on how to use sp_send_dbmail and sp_ExecuteSQL.
Create a job and a schedule that runs the stored procedure, Alert_Monitor, at the least interval needed
Table:
Create table Alerts(
AlertID int identity(1,1), -- Unique alert ID
StoredProcedure nvarchar(200), -- Stored proc to run - must have @AlertStatus tinyint OUTPUT
EmailRecipients nvarchar(200), -- Who gets emails - comma separated list
EmailSubject nvarchar(100), -- Subject -- servername will be prefaced so we know live from test
EmailBody nVarchar(300), -- Email Body
AlertStart24Hour int, -- 24 hour/minutes clock when to start
AlertStop24Hour int, -- 24 hour/minutes clock when to stop
AlertMonday [bit],
AlertTuesday [bit],
AlertWednesday [bit],
AlertThursday [bit],
AlertFriday [bit],
AlertSaturday [bit],
AlertSunday [bit],
MinutesBetweenChecks int, -- How many minutes to wait before checking again
WhoOwnsThisAlert varchar(100), -- just to keep track of ownership
LastCheckTime DATETIME DEFAULT (GETDATE()), -- last check time
IncludeOnDeveloperOnCall [bit]
)
The last field IncludeDeveloperOnCall was added so whenever it’s run it will look at a table that tells which developer is on call and to get it and add the email/page to the recipients of the email.
Code:
CREATE PROC [dbo].[Alert_Monitor]
AS /*
created 10/23/2007
This looks at the alerts defined in the Alerts Table in DBA_OPS
This will run every 5 to 20 minutes as a job.
*/
SET nocount ON
-- define temp table and load with alerts to check
DECLARE @Alerts TABLE
(
AlertID INT,
processed INT DEFAULT ( 0 )
)
INSERT INTO @Alerts ( AlertID )
SELECT AlertID
FROM Alerts WITH ( NOLOCK )
-- Are we between the start and stop time?
WHERE DATEPART(HOUR, GETDATE()) * 100 + ( DATEPART(mi, GETDATE()) ) BETWEEN AlertStart24Hour
AND AlertStop24Hour
-- is it time to run it again?
AND DATEADD(mi, MinutesBetweenChecks, LastCheckTime) <= GETDATE()
-- are we running it today?
AND CASE DATEPART(dw, GETDATE())
WHEN 1 THEN AlertSunday
WHEN 2 THEN AlertMonday
WHEN 3 THEN AlertTuesday
WHEN 4 THEN AlertWednesday
WHEN 5 THEN AlertThursday
WHEN 6 THEN AlertFriday
WHEN 7 THEN AlertSaturday
END = 1
DECLARE @storedProcedure NVARCHAR(130),
@Recipients NVARCHAR(200),
@Subject NVARCHAR(200),
@Message NVARCHAR(500),
@ReturnCode INT,
@ReturnInfo VARCHAR(500),
@ParameterDefinition NVARCHAR(50),
@AlertID INT,
@email VARCHAR(50)
DECLARE @RecTable TABLE ( email VARCHAR(50) )
SET @ParameterDefinition = N'@AlertStatus INT OUTPUT,@Info VARCHAR(500) OUTPUT'
SET @Recipients = ''
-- add any other email/pagers from other source table – Developer on call Table
INSERT @RecTable
SELECT SomeEmail
FROM Emailtable
WHERE GETDATE() BETWEEN StartDate AND EndDate
-- create list of recipients
WHILE EXISTS ( SELECT *
FROM @RecTable )
BEGIN
SET @email = ( SELECT MIN(email)
FROM @RecTable
)
SET @Recipients = CASE WHEN LEN(@Recipients) = 0 THEN ''
ELSE @Recipients + ';'
END + @email
DELETE FROM @RecTable
WHERE email = @email
END
-- loop through alerts and check
WHILE EXISTS ( SELECT *
FROM @Alerts )
BEGIN
SET @ReturnInfo = ''
SET @ReturnCode = 0
SET @AlertID = ( SELECT MIN(AlertID)
FROM @Alerts
)
SET @storedProcedure = ( SELECT StoredProcedure
FROM Alerts WITH ( NOLOCK )
WHERE AlertID = @AlertID
)
SET @storedProcedure = N'EXEC ' + @storedProcedure
+ N' @AlertStatus OUTPUT,@Info OUTPUT'
-- call stored proc
EXEC master.dbo.sp_executesql @storedProcedure,
@ParameterDefinition, @AlertStatus = @ReturnCode OUTPUT,
@Info = @ReturnInfo OUTPUT
-- if returned nonzero then send alert
IF @ReturnCode <> 0
BEGIN
SET @Recipients = ( SELECT EmailRecipients
+ CASE WHEN IncludeOnDeveloperOnCall = 1
THEN ';' + @Recipients
ELSE ''
END
FROM Alerts WITH ( NOLOCK )
WHERE AlertID = @AlertID
)
SET @Subject = ( SELECT EmailSubject
FROM Alerts WITH ( NOLOCK )
WHERE AlertID = @AlertID
) + N' ' + @@SERVERNAME
SET @Message = ( SELECT EmailBody
FROM Alerts WITH ( NOLOCK )
WHERE AlertID = @AlertID
)
SET @Message = @Message
+ CASE WHEN LEN(@ReturnInfo) > 0 THEN '
Additional info - ' + @ReturnInfo
ELSE ''
END
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'mailprofile',
@recipients = @Recipients, @body = @Message,
@subject = @Subject, @body_format = 'HTML'
END
-- update last check time and go onto the next alert to check
UPDATE Alerts
SET LastCheckTime = GETDATE()
WHERE AlertID = @AlertID
DELETE FROM @Alerts
WHERE AlertID = @AlertID
END
No comments:
Post a Comment