[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