lionofdezert/SQLLogMail.sql

## SQLLogMail.sql
-- Table variable to hold intermediate data
DECLARE @ReportSQLErrorLogs TABLE
    (
      [log_date] [datetime] NULL,
      [processinfo] [varchar](255) NULL,
      [processtext] [text] NULL
    )

DECLARE @NumErrorLogs INT,
    @CurrentLogNum INT

SET @CurrentLogNum = 0

-- Get total number of log files from registry
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
    N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs',
    @NumErrorLogs OUTPUT

SELECT @NumErrorLogs = ISNULL(@NumErrorLogs, 6)
 -- NULL in registry entry for Error Log files mean default of 6 value

WHILE @CurrentLogNum < @NumErrorLogs
    BEGIN
        INSERT INTO @ReportSQLErrorLogs
                EXEC master..xp_readerrorlog @CurrentLogNum
        PRINT @CurrentLogNum
        SELECT @CurrentLogNum = @CurrentLogNum + 1
    END


DECLARE @Body VARCHAR(MAX),
    @TableHead VARCHAR(1000),
    @TableTail VARCHAR(1000)


SET @TableTail = '</table></body></html>' ;

SET @TableHead = '<html><head>' + '<style>'
    + 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} '
    + '</style>' + '</head>'
    + '<body><table cellpadding=0 cellspacing=0 border=0>'
    + '<tr><td align=center bgcolor=#E6E6FA><b>ROW ID</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>Log Date</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>Process Info</b></td>'
    + '<td align=center bgcolor=#E6E6FA><b>Process Text</b></td></tr>' ;
---Create HTML mail Body
SELECT  @Body = ( SELECT    td = row_number() OVER ( ORDER BY I.row_id ),
                            td = I.log_date,
                            '',
                            td = ISNULL(I.processinfo, ''),
                            '',
                            td = ISNULL(I.processtext, ''),
                            ''
                  FROM      #ReportSQLErrorLogs I ( NOLOCK )
                  --- Filter only necessary information
                  WHERE     I.processtext NOT LIKE '%error log%'
                            AND I.processtext NOT LIKE '%Database backed up%'
                            AND I.processtext NOT LIKE '%Logging SQL Server messages in file %'
                            AND I.processtext NOT LIKE '%Authentication mode%'
                            AND I.processtext NOT LIKE '%System Manufacturer%'
                            AND I.processtext NOT LIKE '%All rights reserved.%'
                            AND I.processtext NOT LIKE '%(c) 2005 Microsoft Corporation.%'
                            AND I.processtext NOT LIKE '%Microsoft SQL Server 2008 (SP1)%'
                            AND I.processtext NOT LIKE '%SQL Trace ID%'
                            AND I.processtext NOT LIKE '%full-text catalog%'
                            AND I.processtext NOT LIKE '%Server process ID is%'
                            AND I.processtext NOT LIKE '%starting up database%'
                            AND I.processtext NOT LIKE '%found 0 errors%'
							-- To extract information for last 24 hours
							AND DATEDIFF(HH,I.log_date,GETDATE()) <=24
                FOR
                  XML RAW('tr'),
                      ELEMENTS
                )

-- Replace the entity codes and row numbers
SET @Body = REPLACE(@Body, '_x0020_', SPACE(1))
SET @Body = REPLACE(@Body, '_x003D_', '=')
SET @Body = REPLACE(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
SET @Body = REPLACE(@Body, '<TRRow>0</TRRow>', '')

SELECT  @Body = @TableHead + @Body + @TableTail

--- Send HTML mail
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'aasim.rokhri@gmail.com', -- Mention email addresses separated by semicolon
    @subject = 'SQL SERVER LOGS REPORT',
    @profile_name = 'DBA', -- Change profile name according to your own
    @body = @Body,
    @body_format = 'HTML' ; --Mail format should be HTML
	-- Table variable to hold intermediate data
	DECLARE @ReportSQLErrorLogs TABLE
	(
	[log_date] [datetime] NULL,
	[processinfo] [varchar](255) NULL,
	[processtext] [text] NULL
	)

	DECLARE @NumErrorLogs INT,
	@CurrentLogNum INT

	SET @CurrentLogNum = 0

	-- Get total number of log files from registry
	EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
	N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs',
	@NumErrorLogs OUTPUT

	SELECT @NumErrorLogs = ISNULL(@NumErrorLogs, 6)
	-- NULL in registry entry for Error Log files mean default of 6 value

	WHILE @CurrentLogNum < @NumErrorLogs
	BEGIN
	INSERT INTO @ReportSQLErrorLogs
	EXEC master..xp_readerrorlog @CurrentLogNum
	PRINT @CurrentLogNum
	SELECT @CurrentLogNum = @CurrentLogNum + 1
	END


	DECLARE @Body VARCHAR(MAX),
	@TableHead VARCHAR(1000),
	@TableTail VARCHAR(1000)


	SET @TableTail = '</table></body></html>' ;

	SET @TableHead = '<html><head>' + '<style>'
	+ 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} '
	+ '</style>' + '</head>'
	+ '<body><table cellpadding=0 cellspacing=0 border=0>'
	+ '<tr><td align=center bgcolor=#E6E6FA><b>ROW ID</b></td>'
	+ '<td align=center bgcolor=#E6E6FA><b>Log Date</b></td>'
	+ '<td align=center bgcolor=#E6E6FA><b>Process Info</b></td>'
	+ '<td align=center bgcolor=#E6E6FA><b>Process Text</b></td></tr>' ;
	---Create HTML mail Body
	SELECT @Body = ( SELECT td = row_number() OVER ( ORDER BY I.row_id ),
	td = I.log_date,
	'',
	td = ISNULL(I.processinfo, ''),
	'',
	td = ISNULL(I.processtext, ''),
	''
	FROM #ReportSQLErrorLogs I ( NOLOCK )
	--- Filter only necessary information
	WHERE I.processtext NOT LIKE '%error log%'
	AND I.processtext NOT LIKE '%Database backed up%'
	AND I.processtext NOT LIKE '%Logging SQL Server messages in file %'
	AND I.processtext NOT LIKE '%Authentication mode%'
	AND I.processtext NOT LIKE '%System Manufacturer%'
	AND I.processtext NOT LIKE '%All rights reserved.%'
	AND I.processtext NOT LIKE '%(c) 2005 Microsoft Corporation.%'
	AND I.processtext NOT LIKE '%Microsoft SQL Server 2008 (SP1)%'
	AND I.processtext NOT LIKE '%SQL Trace ID%'
	AND I.processtext NOT LIKE '%full-text catalog%'
	AND I.processtext NOT LIKE '%Server process ID is%'
	AND I.processtext NOT LIKE '%starting up database%'
	AND I.processtext NOT LIKE '%found 0 errors%'
	-- To extract information for last 24 hours
	AND DATEDIFF(HH,I.log_date,GETDATE()) <=24
	FOR
	XML RAW('tr'),
	ELEMENTS
	)

	-- Replace the entity codes and row numbers
	SET @Body = REPLACE(@Body, '_x0020_', SPACE(1))
	SET @Body = REPLACE(@Body, '_x003D_', '=')
	SET @Body = REPLACE(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
	SET @Body = REPLACE(@Body, '<TRRow>0</TRRow>', '')

	SELECT @Body = @TableHead + @Body + @TableTail

	--- Send HTML mail
	EXEC msdb.dbo.sp_send_dbmail
	@recipients = 'aasim.rokhri@gmail.com', -- Mention email addresses separated by semicolon
	@subject = 'SQL SERVER LOGS REPORT',
	@profile_name = 'DBA', -- Change profile name according to your own
	@body = @Body,
	@body_format = 'HTML' ; --Mail format should be HTML