Eventlog_Capture - reporting on 100 in a minute

Eventlog_Tracking_Notifying_on_Busy_Servers.sql

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 = '[email protected]', @subject = @MailSubject,
            @body = @tableHTML, @body_format = 'HTML' ;
END 

GO