[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