In my previous article titled How to centralize your SQL Server Event Logs, I discussed how to forward all your event logs into a single table within a database. I called the database DBA and the table was called EventLogStaging.
This is the follow up article how we can take the information contained within that table and alert us in real time to errors that occur.
Why do this
The main reason I do this is for simplicity. While it may seem easier to create individual alerts for errors you are interested in, I can assure you that with this method, nothing is left to chance. When we create a SQL Agent job for example, we have to provide alerting details for each and every job we create. We must also set up Database Mail if we want those alerts to come to us via email. For every server we monitor for every job we create, we must do this.
With my method, I simply use the process that is already in place described in the first article and expand on it to create alerts based on triggers. Of course, you must follow the previous article step by step to make use of this functionality. We must also define what we want to be alerted on by writing errors to the Windows Application Log.
Let's get started
The first thing we have to do is create another table. We will call this table EventLog and it will be used to hold only events we want from the EventLogStaging table. Remember that the previous article put EVERYTHING into the EventLogStaging table. We use this new table to only hold 1 event per server per hour.
USE [DBA] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[EventLog]( [RecordNumber] [int] NOT NULL, [Category] [int] NOT NULL, [ComputerName] [varchar](250) NOT NULL, [EventCode] [int] NOT NULL, [EventType] [int] NOT NULL, [Message] [varchar](2000) NULL, [SourceName] [varchar](250) NOT NULL, [TimeGenerated] [datetime] NULL, [TimeWritten] [datetime] NOT NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
The next thing we must do is create a trigger and a stored procedure. The first is a trigger on the EventLogStaging table. Remember the EventLogStaging table was created in the previous article.
USE [DBA] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER InsertToEventLog ON [dbo].[EventLogStaging] for INSERT AS -- Get the information from the Inserted virtual table declare @RecordNumber int declare @Category int declare @ComputerName varchar(250) declare @EventCode int declare @EventType int declare @Message nvarchar(4000) declare @SourceName varchar(250) declare @TimeGenerated datetime declare @TimeWritten datetime select @RecordNumber = RecordNumber , @Category = Category , @ComputerName = ComputerName , @EventCode = EventCode , @EventType = EventType , @Message = Message , @SourceName = SourceName , @TimeGenerated = TimeGenerated , @TimeWritten = TimeWritten from inserted if(@@rowcount = 1) -- Do one row only exec DBA.dbo.EventLogStagingInsertTrigger @RecordNumber, @Category , @ComputerName, @EventCode, @EventType, @Message, @SourceName , @TimeGenerated, @TimeWritten else raiserror('Error occurred when inserting rows from EventLogStaging table to EventLog table', 16, 1) with log
The above trigger creation will tell you
The module 'InsertToEventLog' depends on the missing object 'DBA.dbo.EventLogStagingInsertTrigger'. The module will still be created; however, it cannot run successfully until the object exists.
But that is ok, because we will create the stored procedure in the next step.
USE [DBA] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE Proc EventLogStagingInsertTrigger @RecordNumber int, @Category int, @ComputerName varchar(250), @EventCode int, @EventType int, @Message varchar(2000), @SourceName varchar(30), @TimeGenerated datetime, @TimeWritten datetime As If NOT exists( select ComputerName, EventType, SourceName from EventLog where (ComputerName = @ComputerName and EventType = @EventType and SourceName = @SourceName) ) Begin Insert into EventLog values (@RecordNumber, @Category, @ComputerName, @EventCode, @EventType, @Message, @SourceName, @TimeGenerated, @TimeWritten) End -- If Record exists, check if the record has expired, if it does, insert a new one thus trigger a new alert Else Begin declare @startTime DateTime Begin select @startTime = max(TimeWritten) from EventLog where (ComputerName = @ComputerName and EventType = @EventType and SourceName = @SourceName and Message = @Message ) End If(((DateDiff(minute, @startTime, @TimeWritten))> 60) Or @startTime is null) -- If the message doesn't exist, @startTime would be NULL Begin Insert into EventLog values (@RecordNumber, @Category, @ComputerName, @EventCode, @EventType, @Message, @SourceName, @TimeGenerated, @TimeWritten) End End
The stored procedure above has one main goal. That goal is to insert records into the table EventLog. It will only write data into the EventLog table that is older than 60 minutes or completely new. It will compare by the TimeWritten column to first see if a record exists that is older than 60 minutes. We do this so we are not spammed for multiple alerts that are the same. The Windows Event Log may write 5 messages that are the same within an hour's time, but we will only use the first one it encounters once per hour.
Create a trigger and stored procedure to send mail
Lets first create a trigger in our new table EventLog. I call this trigger SendEmailorPage since that is what is ultimately does.
USE [DBA] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER SendEmailOrPage ON [dbo].[EventLog] FOR INSERT AS -- Get the information from the Inserted virtual table declare @RecordNumber int declare @Category int declare @ComputerName nvarchar(250) declare @EventCode int declare @EventType int declare @Message nvarchar(4000) declare @SourceName nvarchar(250) declare @TimeGenerated datetime declare @TimeWritten datetime select @RecordNumber = RecordNumber, @Category = Category, @ComputerName = ComputerName, @EventCode = EventCode, @EventType = EventType, @Message = Message, @SourceName = SourceName, @TimeGenerated = TimeGenerated, @TimeWritten = TimeWritten from inserted where Message IS NOT NULL if(@@rowcount = 1) -- Do one row only exec DBA.dbo.EventLogInsertTrigger @RecordNumber, @ComputerName, @Message, @TimeGenerated ;
You will receive another message from SQL Server Management Studio telling you
The module 'SendEmailOrPage' depends on the missing object 'DBA.dbo.EventLogInsertTrigger'. The module will still be created; however, it cannot run successfully until the object exists.
Not to worry. We will create the stored procedure next.
USE [DBA] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE proc EventlogInsertTrigger @RecordNumber int, @ComputerName varchar(50), @Message varchar(1000), @TimeGenerated datetime As Begin DECLARE @HtmlMessageBody NVARCHAR(MAX) declare @SevIndex int declare @sevStr char(2) declare @sev int declare @sub nvarchar(250) declare @toEmail varchar(500) declare @now datetime declare @ServerName nvarchar(250) select @now = getdate() set @ServerName = @ComputerName select @toEmail = 'this_is_your_email_Address@your_domain.com' --- CHANGE THIS. USE YOUR EMAIL ADDRESS!!!! ------------------------------------------------------------------------------------ SELECT @HtmlMessageBody = N'<H1>Server '+@ServerName+' is reporting an error</H1>' --Large Bold "Heading 1" above the table + N'<table border="1" cellspacing="0">' --Start the table tag and make the grid "pretty" + N'<tr bgcolor="Red">' --Start a header row with a "pretty" background color + N' <th>ERROR</th>' -- This is a "th" or Table Header for a column + N'</tr>' --Finish the header row. + CAST(--==== This builds and concatenates each row into the HTML ( SELECT @Message FOR XML PATH('tr'), TYPE --This does the concatenation and de-entization of each row in HTML <tr></tr> tags ) AS NVARCHAR(MAX) --Must absolutely convert the resulting XML to NVARCHAR(MAX) ) + N'</table>' --Finishes up the table tag ; ------------------------------------------------------------------------------------ BEGIN select @SevIndex = patindex('%Severity%', @Message) select @sevStr = substring(@Message, @SevIndex + 10 , 2) select @sev = cast(@sevStr As tinyint) select @sub = 'Error ' + @sevStr + ' occurred on computer ' + @ServerName Begin EXEC msdb.dbo.sp_send_dbmail @recipients = @toEmail,@subject = @sub, @body = @HtmlMessageBody, @body_format = 'HTML' End END END
It may seem like over kill, but I like my emails pretty. Don't forget to set your email address in the @toEmail variable or you will never receive an email.
Remember when I said it was a hassle setting up DB Mail on the servers we want message from? Well, you have to set it up on the server that the database DBA is on. The stored procedure EventLogInsertTrigger needs it. So make sure you have DB Mail configured and tested before you start trying to use this.
So you want to see it in action, right? No problem. Let's just create a dummy record into the table EventLogStaging and see what happens.
USE [DBA] GO INSERT INTO [dbo].[EventLogStaging] ([RecordNumber] ,[Category] ,[ComputerName] ,[EventCode] ,[EventType] ,[Message] ,[SourceName] ,[TimeGenerated] ,[TimeWritten]) VALUES ('123' ,'2' ,'TEST_SERVER_123' ,'17063' ,'1' ,'Error: 50000 Severity: 17 State: 1 Something REALLY BAD HAS HAPPENED!!!!' ,'MSSQLSERVER' ,'2012-04-01 10:10:00.000' ,'2012-04-01 10:10:00.000' ) GO
Did you receive an email? It should look something like this.
Notice that if you run the insert statement again, you will not receive an email. If you try again in an hour, you will.
Overview - What is happening here
In my first article, I described how we can use the SQL Server Agent to 'forward' SQL Server events to a single SQL Server instance. The server we use to 'receive' those events has a small Visual Basic Script running on it that parses the Windows Event Logs into a table called EventLogStaging.
When a record gets inserted into the EventLogStaging, a trigger is fired. It takes the inserted records and IF the record does not exist in the table EventLog within the last 60 minutes, it inserts it. Once it is in the EventLog table another trigger is fired. That trigger calls a stored procedure that sends an email. So basically, everything that ever goes into the table EventLog will always send an email.
The errors that will eventual make it into the EventLog table and send emails are anything SQL logs in the Windows Event Log. Of course, the informational messages will be in the Windows Event Log, but the Visual Basic Script in the first article only parses the Errors. Some of you may ask, "What errors?". It will eventually be the errors you decide to write to the Windows Application Log. We do this with the RAISERROR function using the option 'with log'. Anything you can think of to monitor within SQL can be used to create a Windows Application Log Error thus sending an email.
In my next article, I will expand on this functionality to show how we can get emails for some very useful things. At my current employer, I use the methods I described above to receive alerts for Failed Logins, Failed SQL Server Agent Jobs, CPU spikes, Long Running SQL Server Agent jobs, Down SQL Server, Down SQL Server Agent and low disk space warnings, all from a single server. Just remember, that if you can make use of RAISERROR ‘with log’, you can figure out some pretty neat stuff for yourself. Stay tuned........