[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.

[EN] Errorlog emails based off Event Notification

NOTE! THIS DOES NOT WORK.  In writing this, we found there's a bug in EN where ERRORLOG doesn't get sent if the Source is "Server".  Which shutdown qualifies as.  You can search for "shut down", but you'll only get the message when the service restarts - the message gets sent close enough to shutdown that it doesn't make it to the centralized service until after restart.



All of our servers that have Event Notifications get the ERRORLOG event type.  What can we do with that?  Well, one thing we needed was a notice when a machine announced it was shutting down.  We've seen that a few times over the years, where the server decides it's better off restarting, and does it on its own.  This isn't a hard bounce - the error logs actually state that it's a deliberate choice by the server.  Offhand I know we've seen it due to DBCC CHECKDB, but I seem to recall other instances.

On looking at the code, I realize I could change the CTE to use half the IO.  However, I want to make sure I catch ANY instances, and the exist/contains XML query is CASE SENSITIVE.  So, I'm staying with mine for now.

But here it is in case....
 and EventType = 'errorlog'

 AND message_body_xml.exist('/EVENT_INSTANCE[1]/TextData[contains(.,"shutdown")]') = 1

And here's the full email.

set quoted_identifier on

declare @now datetime

select @now = convert(smalldatetime,getdate())

;with cte as 

(

SELECT message_body_xml.value('/EVENT_INSTANCE[1]/TextData[1]', 'varchar(150)') as textdata, * 

FROM ENAudit_Events with (NOLOCK) WHERE insert_datetime >= @now

 and EventType = 'errorlog'

 )

select * into ##listofshutdown from cte where textdata like '%shutdown%'

if (select count(*) from ##listofshutdown) > 0

begin

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'youremailprofile',

    @recipients = 'youremail@dev.null.com',

    @query = 'select * from ##listofshutdown' ,

    @subject = 'Attention - a machine has announced it is shutting down via EN ERRORLOG',

    @query_attachment_filename = 'shutdowninfo.txt',

    @query_result_separator =  ' ',

    @query_result_width = 750,

    @attach_query_result_as_file = 1 ;

end

drop table ##listofshutdown

[PASS] everything from my presentation today on Event Notifications

Thanks to all who attended.  Here's my Share with all the code samples and slide deck from today.  I'll be cleaning things up in the next couple of hours, replacing updated code and the like. 
Everything's current now - the "script 1-5" are the scripts shown during the demonstration.  The SSMS report and SSRS report have been added, and my automation script is the current version.  The Parse_EN_Messages was updated in November and hasn't changed since.

At the very least, find yourself a testbox and run Script 1 - that shows how it all works, and is a great Proof Of Concept.  You could also change the trace group to DDL_SERVER_LEVEL_EVENTS and see all the various things tracked.


Thanks again!
TBD


https://docs.google.com/folder/d/0B3a287PS_UJIcnY3Q1pvX3p1eEE/edit?usp=sharing

And my original blog post on it, which I keep updating:
http://thebakingdba.blogspot.com/2012/10/master-of-all-i-survey-using-event.html

--
and the video (Thanks to Mike for handling the video duties!)
mms://passmedia.sqlpass.org/share/dba/MasterofAllISurvey_03272013.wmv

[PASS] I'm presenting March 27, 2013!

Howdy, all!  I am pleased to be presenting on Event Notifications this week.  It's a soup-to-nuts on tracking code changes and more, across your environment.  I honestly think most people reading this would find it handy.  We've found it essential in our environment.

More information at dba.sqlpass.org

Date: March 27, 2013 

Noon Eastern time – www.timeanddate.com/worldclock/fixedtime.html

Topic: Master of All I Survey - Tracking Code Changes Across Dozens of Servers Seamlessly and Automatically – sponsored by Quest Software

Presenter: Michael Bourgon

Abstract:

Everyone's been bitten by it - rogue changes made on servers, be it SPs, index rebuilds, or even configuration changes. What if you knew? What if you could look historically across servers and track down what changes were made when, by who, and from where? It exists, it's already built into MSSQL, it's easy to implement and it's been there waiting for you for 7 years. Come learn what Event Notification is and why you'll want it in your environment.

Bio:
Michael Bourgon is the Senior Production DBA for Emdeon Pharmacy Services.  He started work with Sybase on RS/6000 over 15 years ago, and has been meddling with some form of the SQL Server codebase ever since.  He's always looking for a better way to be a Lazy DBA - spending far too many hours automating processes in order to accomplish that.  In his spare time he listens to weird music, reads Science Fiction, and tries to ensure his daughter grows up a nerd, too. 

 Door prize: There will be a raffle for a $50 Amazon gift certificate.  You do not need to register for the meeting, but if you want to enter the raffle, you must register at www.livemeeting.com/lrs/8000181573/Registration.aspx no later than 5:00 PM EDT on March 26th.

Live Meeting Link: www.livemeeting.com/cc/8000181573/join

[SSMS] Fun fact - Emacs!

Was going through my freshly formatted install, and put 2012 SSMS on it.  Then went digging to change the comment/uncomment shortcuts, and (of course) change Ctrl+O to it's old Query Analyzer function: New Database Engine Query (new window, choosing database connection at that time).  I honestly don't know how people DON'T have this mapped.  Do you seriously click each time, or go File->New->Database Engine Query?

Lo and behold...
Edit.EmacsWordCapitalize
Edit.EmacsSetMark
Edit.EmacsScrollLineTop

and so on, and so forth.  Forty-three commands in total.  Impressive, if only because Emacs is the quintessential best/worst about Linux.

I'm both amused and annoyed by this:
1) You went to all the effort to emulate Emacs, but couldn't bother setting up a base shortcut for Database Engine Query, which you use all the frickin' time?
2) All the other glaring issues with SSMS, but you had time to emulate Emacs?
and of course, the most Egregious:
3) No VI mode!

[RDP] Finding and logging people off from command line

Thanks to Tony for this!

Obviously, you can use the Terminal Services Manager (now the Remote Desktop Services Manager) to find people and log them off.  This is a command line way to do the same thing.  Easier, possibly, and doesn't (to my knowledge) require the Manager pack be downloaded & installed.

To list users:
query session /server:yourservernamehere

To kick off users:
RESET SESSION 1 /SERVER:yourservernamehere
or
rwinsta /SERVER:yourservernamehere 1

(replace 1 with whatever the ID is in the results from the QUERY)