[Eventlog_Tracking] Send a report out when more than 100 messages in a minute

I'm trying to curb the excess eventlog messages in my environment.  Now that I'm using WQL/WMI/PoSH to capture it (writing the presentation now - tentative name is "WMI for the DB Guy/WQL for the DB Gal"), and filtering it as it comes in, I need a way to *AHEM* encourage people to suggest filters, and get rid of error messages. 

So, they're about to get emails with details when it's over 100 in a minute.  With the most frequent errors listed.  It's got a @threshold parameter so you can turn it down if you don't have too many errors.

	SET QUOTED_IDENTIFIER ON
	SET ANSI_NULLS ON
	GO
	CREATE PROCEDURE [dbo].[Eventlog_Tracking_Notifying_on_Busy_Servers] @threshold INT = 100
	AS
	--mdb 2015/07/17 1.00 - Basically, making sure that we don't get blindsided by something going wrong.
	-- Piggybacks on top of the eventlog_tracking job, so if that fails or stops working, so will this.
	-- Looks for any server/log combinations that have more than 100 entries in a minute.
	-- Once a couple issues are fixed, we could drop this number. Obviously, filtering will have to occur, too.
	-- 1.01 - Adding details to try and show the most frequent errors
	-- 1.01 - order top list by server/minute/log
	-- 1.02 - @threshold
	DECLARE @tableHTML NVARCHAR(MAX), @mailsubject VARCHAR(150)
	DECLARE @timegenerated_start SMALLDATETIME, @insert_datetime_start SMALLDATETIME
	--set a steady time so we can reproduce if necessary, and to prevent weird results where the first part gets an earlier minute
	SELECT @timegenerated_start = DATEADD(MINUTE,-30, GETDATE()), @insert_datetime_start = DATEADD(MINUTE,-30, GETDATE())
	--First block - find Server/Log combinations with more than @threshold (was 100) for a minute. Wish we could set this lower.
	SELECT @mailsubject = '[Eventlog] Server with more than ' + CONVERT(VARCHAR(30),@threshold) + ' errors in a minute - ' + CONVERT(VARCHAR(16),GETDATE(),120)
	select @tableHTML = N'<H3>Error Groupings ' + '</H3>'
	+ N'<table border="1">'
	+ N'<th>ErrorCount</th>'
	+ N'<th>ComputerName</th>'
	+ N'<th>EventLog</th>'
	+ N'<th>Time Generated</th>'
	+ CAST(( SELECT
	td = CONVERT(VARCHAR(10),COUNT(*),121) , ''
	, td = CONVERT(VARCHAR(50),RTRIM(LTRIM(ISNULL(computername,'')))) , ''
	, td = CONVERT(VARCHAR(50),RTRIM(LTRIM(ISNULL(eventlog,'')))) , ''
	, td = CONVERT(VARCHAR(16),RTRIM(LTRIM(ISNULL(CONVERT(CHAR(16),TimeGenerated,120),''))))
	FROM EventLog_Details
	WHERE TimeGenerated >= @timegenerated_start
	AND insert_datetime >= @insert_datetime_start
	GROUP BY computername, eventlog, CONVERT(CHAR(16),TimeGenerated,120)
	HAVING COUNT(*)> @threshold --100
	ORDER BY ComputerName asc, CONVERT(CHAR(16),TimeGenerated,120) desc, EventLog asc--COUNT(*) desc
	FOR XML PATH('tr') , TYPE ) AS NVARCHAR(MAX)) + N'</table>' ;
	--adding specific details
	select @tableHTML = @tableHTML + N'<br><br><br>' + N'<H3>Error Specifics ' + '</H3>'
	+ N'<table border="1">'
	+ N'<th>ErrorCount</th>'
	+ N'<th>ComputerName</th>'
	+ N'<th>EventLog</th>'
	+ N'<th>Message</th>'
	+ CAST(( SELECT TOP 20
	td = CONVERT(VARCHAR(10),COUNT(*),121) , ''
	, td = CONVERT(VARCHAR(50),RTRIM(LTRIM(ISNULL(ComputerName,'')))) , ''
	, td = CONVERT(VARCHAR(50),RTRIM(LTRIM(ISNULL(EventLog,'')))) , ''
	, td = CONVERT(VARCHAR(153),RTRIM(LTRIM(ISNULL(LEFT([Message],150)+'...',''))))
	FROM EventLog_Details
	WHERE TimeGenerated >=@timegenerated_start
	AND insert_datetime >= @insert_datetime_start
	AND EXISTS
	(SELECT * FROM
	(SELECT ComputerName, EventLog --, CONVERT(SMALLDATETIME, TimeGenerated), COUNT(*)
	FROM EventLog_Details
	WHERE TimeGenerated >= @timegenerated_start
	AND insert_datetime > @insert_datetime_start
	GROUP BY ComputerName, EventLog, CONVERT(SMALLDATETIME, TimeGenerated)
	HAVING COUNT(*)>@threshold
	) a
	WHERE a.ComputerName = EventLog_Details.ComputerName
	AND a.EventLog = EventLog_Details.EventLog)
	GROUP BY ComputerName, EventLog, LEFT([Message],150)+'...'
	ORDER BY ComputerName, EventLog, COUNT(*) DESC-- LEFT([Message],100)+'...'
	FOR XML PATH('tr') , TYPE ) AS NVARCHAR(MAX)) + N'</table>' ;
	IF @tableHTML IS NOT NULL
	begin
	EXEC msdb.dbo.sp_send_dbmail @profile_name = 'emailaccount',
	@recipients = 'dev@null.com', @subject = @MailSubject,
	@body = @tableHTML, @body_format = 'HTML' ;
	END
	GO

view raw Eventlog_Tracking_Notifying_on_Busy_Servers.sql hosted with ❤ by GitHub

[SSIS 2012] SQL Sentry - using custom code to send detailed SSISDB failure emails (part 2)

Since, going back through my old post (http://thebakingdba.blogspot.com/2013/05/ssis-2012-using-sql-sentry-custom-code.html), I found that it was hard to figure out where you add the condition to SQL Sentry event manager. I need to add this to SQLSentry's just-announced condition repository.

Step 1: grab code from below, and put it in a database of your choosing.
http://thebakingdba.blogspot.com/2013/05/ssis-2012-using-sql-sentry-custom-code.html

Step 2: index!  You probably need something like this index.  Mind the blocking during creation

USE [SSISDB]

GO

CREATE NONCLUSTERED INDEX ncidx__internal_event_messages__event_message_id

ON [internal].[event_messages] ([package_name])

INCLUDE ([event_message_id])

GO

Step 3: open SQL Sentry, go to the SSISDB computer in your list of servers, then expand.  Click on the main subgroup, which has the DB icon, the server name , the SQL Server Version, then "(EM/PA)" if you're running both.

Step 4: expand "Conditions".  View->Conditions, or it's already a tab.

Step 5: Under "Explicit", there's an "Add" button.  Click

Step 6: In "Actions Selector: General Conditions", on the right side of the window find "SQL Server Agent Job", then "SQL Server Agent Job: Failure", then select "Execute SQL".

Step 7: Under server, choose the same server (you could run the code on a different box, but the data we need is on that box).

Step 8: For T-SQL Command Text, enter (I use a different name than in the original post, since I use both variants, depending on the server)

exec dba_utils.dbo.ssis_job_failure_info_fromjobname_SqlSentry @job_name = '<%ObjectName%>'

Step 9: Click in some other server or window, so that the details are saved. 





Step 10: Test! 

[Presenting] 24 Hours of PASS 2015 - how I did

Had to share.  From my presentation, here were the results of the survey (if you stuck around until the very end).  It included the comments as well, which were quite complimentary.  I smirked/winced at the comment about chatting me up at PASS, since my presentation wasn't selected and so the odds of me going are pretty damn slim.

But, like I said, I'm very pleased with this.  I know I had 237 people at one point, so it probably peaked at 250-275.  126 responses is better than I'd hoped for.  And Obviously I need to work on my presentation skills some more!  : )