Monday, July 29, 2013

[Event Notifications] Emailed report based off collected ERRORLOG messages

One of the cool side effects of Event Notifications is the easy collection of SQL Errorlog.  While it doesn't collect all messages, it collects enough for me to be aware of problems.  Alas, it does not collect enough for me to get an email when a server decides to shut down... the skipped messages are usually around startup/shutdown and error dumps.  See for more info.

BUT... since it's EN, you get them in real-time, so you could theoretically do other things with them.  This report runs once a day, but I'm sure someone in the comments will come up with a new use I hadn't considered.  I use Alerts on the SQL Server, but I could think of other triggers (what if you suddenly started getting failed logins?)

Another advantage: this has more than the sql ERRORLOG file has - it's closer to what the Event Logs have.  Which means that you don't just got "query ran out of memory", you get the name of the login as well.  REALLY handy, that.

For this report, I've just built a simple 3-way email.  If there are severity errors, you get a block of those.  If there are non-severity errors, they go in a different block And finally, login emails get their own. 

What to do: 
 1) Set up EN to capture ERRORLOG.  See my "Master of All I Survey" post for more.  If you already have EN set up, you'll need to DROP/CREATE a new EN that includes it.  (No alters, sorry)
2) Create a table with the exclusion table.  This allows you to skip records you know about, without changing code.

 CREATE TABLE [dbo].[EN_sql_errors_exclusion_email]
[id] [int] NOT NULL IDENTITY(1, 1),
[Exclusions] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [dbo].[EN_sql_errors_exclusion_email] ADD CONSTRAINT [PK__EN_sql_errors__ID] PRIMARY KEY CLUSTERED  ([id]) ON [PRIMARY]

3) Create a job.  Here's my description:
This job uses EventNotificationRec..ENAudit_Events and looks for all SQL errors since 1 day ago , split up by those that have 'severity' in the text data, those that don't, and failed logins.  You can exclude data by adding rows to table EN_sql_errors_exclusion_email (uses LIKE, so 'blah%blah' will work; code already includes % at beginning & end)

Enjoy.  Let me know if you have any questions or concerns.

No comments: