[SQL Server] Envoyer par mail en format HTML le journal SQLServer
---- =============================================
---- Author: Zouhaier KHARROUBI
---- Create date: 30 Mai 2016
---- Description: Envoyer par mail les logs d'erreurs SQL Server
---- =============================================
CREATE PROCEDURE [dbo].[usp_LogsRapport]
@SERVEURS NVARCHAR(500)= NULL
,@Envoyer_Mail_Rapport BIT=0
--WITH EXECUTE AS OWNER -- Cette option est utlisée seulement en cas d'utilisation de OPENROWSET
AS
SET NOCOUNT ON
SET FMTONLY OFF;
DECLARE @Nom_Serveur VARCHAR(25)
,@REQUETE NVARCHAR(600)
,@tableHTML NVARCHAR(MAX)
,@Nbre_Serveur INT
,@Num_Serveur INT
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#LOGS_SERVEURS]'))
BEGIN
DROP TABLE #LOGS_SERVEURS
END
IF EXISTS(SELECT*FROM tempdb..sysobjects WHERE id =object_id(N'[tempdb]..[#Liste_Serveurs]'))
BEGIN
DROP TABLE #Liste_Serveurs
END
CREATE TABLE #LOGS_SERVEURS
(
SERVER_NAME NVARCHAR(35),
LogDate NVARCHAR(23),
[Source] NVARCHAR(100),
[Message] NVARCHAR(MAX)
)
CREATE TABLE #Liste_Serveurs
(
Num_Serveur INT IDENTITY(1,1),
Nom_Serveur NVARCHAR(150)
)
IF @SERVEURS IS NOT NULL
BEGIN
SELECT @SERVEURS = @SERVEURS + ','
INSERT INTO #Liste_Serveurs(Nom_Serveur)
SELECT * FROM dbo.ufn_CompilerParametreChaineSSRS(@SERVEURS)
END
ELSE
BEGIN
INSERT INTO #Liste_Serveurs(Nom_Serveur)
SELECT '[' + REPLACE(REPLACE(data_source,'[',''),']','') + ']' Nom_Serveur from sys.servers
WHERE product='SQL Server'
AND data_source NOT IN('SERVEUR_TEST1','ERVEUR_TEST2','ERVEUR_TEST2')
END
SELECT @Nbre_Serveur = COUNT(*) FROM #Liste_Serveurs
SELECT @Num_Serveur = 1
WHILE @Num_Serveur <= @Nbre_Serveur
BEGIN
SELECT @Nom_Serveur = Nom_Serveur FROM #Liste_Serveurs WHERE Num_Serveur=@Num_Serveur
--recuperer les logs des serveurs
--SET @REQUETE = N'INSERT INTO #LOGS_SERVEURS'
--SET @REQUETE = @REQUETE + N' SELECT ''' + @Nom_Serveur + '''' + ',CONVERT(VARCHAR(23), LogDate, 120),ProcessInfo [Source],Text [Message]'
--SET @REQUETE = @REQUETE + N'FROM OPENROWSET(''SQLNCLI'', ''Server=' + @Nom_Serveur + N';Trusted_Connection=yes;'','
--SET @REQUETE = @REQUETE + N'''SET FMTONLY OFF; EXEC master.sys.xp_readerrorlog 0 '')'
--EXEC (@REQUETE)
SET @REQUETE = N'INSERT INTO #LOGS_SERVEURS'
SET @REQUETE = @REQUETE + N' SELECT ''' + @Nom_Serveur + '''' + ',CONVERT(VARCHAR(23), LogDate, 120),ProcessInfo [Source],Text [Message]'
SET @REQUETE = @REQUETE + N'FROM OPENQUERY(' + @Nom_Serveur + ',''SET FMTONLY OFF; EXEC master.sys.xp_readerrorlog 0'')'
EXEC (@REQUETE)
SELECT @tableHTML = ISNULL(@tableHTML,'') + N'<H><b><BR><left><font color="blue" size="5">' + REPLACE(REPLACE(@Nom_Serveur,'[',''),']','') + '</left></font></BR></b></H>'
SELECT @tableHTML = @tableHTML + N'<table border="0" cellspacing="1" cellpadding="1">' +
N'<tr><th bgcolor="#4340A4"><font color="white">N</font></th><th bgcolor="#4340A4"><font color="white">Serveur</font></th><th bgcolor="#4340A4"><font color="white">DateMessageErreur</font></th><th bgcolor="#4340A4"><font color="white">Source</font></th><th bgcolor="#4340A4"><font color="white">Message</font></th>'
SELECT @tableHTML = @tableHTML +
CAST(( SELECT [td/@style] =CASE WHEN [Message] LIKE '%failed%' OR [Message] LIKE '%error%' THEN 'color:red; font-weight:bold' ELSE 'color:green; font-weight:bold' END,
td = ROW_NUMBER() OVER(ORDER BY SERVER_NAME,LogDate DESC) , '',
[td/@style] =CASE WHEN [Message] LIKE '%failed%' OR [Message] LIKE '%error%' THEN 'color:red; font-weight:bold' ELSE 'color:green; font-weight:bold' END,
td = REPLACE(REPLACE(@Nom_Serveur,'[',''),']','') , '',
[td/@style] =CASE WHEN [Message] LIKE '%failed%' OR [Message] LIKE '%error%' THEN 'color:red; font-weight:bold' ELSE 'color:green; font-weight:bold' END,
td = CONVERT(VARCHAR(23), LogDate, 120), '',
[td/@style] =CASE WHEN [Message] LIKE '%failed%' OR [Message] LIKE '%error%' THEN 'color:red; font-weight:bold' ELSE 'color:green; font-weight:bold' END,
td = [Source], '',
[td/@style] =CASE WHEN [Message] LIKE '%failed%' OR [Message] LIKE '%error%' THEN 'color:red; font-weight:bold' ELSE 'color:green; font-weight:bold' END,
td = [Message]
FROM #LOGS_SERVEURS WHERE DATEDIFF(DAY,LogDate,GETDATE())<=3 AND
([Message] LIKE '%failed%' OR [Message] LIKE '%error%')
ORDER BY SERVER_NAME ASC ,LogDate DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) )
SET @tableHTML = @tableHTML +
N'</table>'
IF @Envoyer_Mail_Rapport=1
BEGIN
TRUNCATE TABLE #LOGS_SERVEURS
END
SELECT @Num_Serveur = @Num_Serveur + 1
END
SELECT @tableHTML
IF @Envoyer_Mail_Rapport=1
BEGIN
-- Envyer le rapport pour tous les logs SQL Server pour tous les serveurs
SELECT @tableHTML = N'<H>Bonjour<BR></BR></H>' +
N'<H>Veuillez trouver ci-dessous les logs des serveurs SQL Server : <BR></BR></H>' + @tableHTML
SELECT @tableHTML = @tableHTML + N'<H><BR></BR>Merci d''analyser et résoudre les anomalies et les erreurs constatées.<BR></BR></H>' +
N'<H>Cordialement,<BR></BR></H>' +
N'<H>Service Informatique</H>' ;
EXEC msdb..sp_send_dbmail@profile_name='MektabaSqlServerProfilMail',@importance='High', @recipients='Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.',
@subject='Logs Erreurs SQL Server', @body= @tableHTML, @body_format = 'HTML' ;
END
ELSE
BEGIN
SELECT * FROM #LOGS_SERVEURS
END