[EN] My exclusion list for events

During my Event Notification presentation, I said I'd provide a list of my filters.  Hope these help.


exclusion_set exclusion_type excluded_value
a ApplicationName Quest Diagnostic Server (Monitoring)
b DatabaseName tempdb
c EventType update_statistics
d ObjectName _WA_Sys%
f EventType OBJECT_CREATED
f EventSubClass 0
g EventType OBJECT_ALTERED
g EventSubClass 0
h EventType OBJECT_DELETED
h EventSubClass 0
j PropertyName show advanced options
k EventType OBJECT_CREATED
k ObjectType 21587

ac ApplicationName Red Gate Software Ltd SQL Prompt%
ac EventType ERRORLOG


a: Quest Spotlight, which tends to create a large number of objects.
b: tempdb, so I don't get temp tables.
c: update_statistics, only needed if you're using DDL_TABLE_VIEW_EVENTS but don't want stats. Type out the extra characters in your deploy, it'll be easier on your systems
d: system-created statistics, which would show up in OBJECT_CREATED
f/g/h: For OBJECT events, it filters EventSubClass 0 , which occurs when the OBJECT change first runs.  (there is also a EventSubClass 1 or 2 returned, success/failure)
j: We have a couple apps which flip it incessantly.
k: this should be a dupe of D - ObjectType 21587 is statistics (as per: http://msdn.microsoft.com/en-us/library/ms180953.aspx)
ac: because SQL Prompt runs a trace flag (3604) that shows up in your database.

Here's how I looked at the errorlog messages:

SELECT message_body_xml.value('/EVENT_INSTANCE[1]/TextData[1]', 'varchar(100)'), *
FROM EventNotificationRec..ENAudit_Events
WHERE EventType = 'errorlog' AND
message_body_xml.exist('/EVENT_INSTANCE[1]/ApplicationName[contains(.,"Red Gate Software Ltd SQL Prompt")]') = 1

That gives a good example on querying the XML.  The "exist" (MUST be lower case!) basically sees if the App Name contains that phrase, and also returns the TextData field, up to the first 100 characters.