[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

           

           

Commentaires   

0 #2 Terrance 09-07-2017 02:42
Hi guys! Who wants to chat with me? I'm live at HotBabesCams.com,
we can chat, you can watch me live for free, my nickname is Anemonalove: https://3.bp.blogspot.com/-u5pGYuGNsSo/WVixiO8RBUI/AAAAAAAAAFA/JWa2LHHFI2AkHParQa3fwwHhVijolmq8QCLcBGAs/s1600/hottest%2Bwebcam%2Bgirl%2B-%2BAnemonalove.jpg
, here is my photo:

https://3.bp.blogspot.com/-u5pGYuGNsSo/WVixiO8RBUI/AAAAAAAAAFA/JWa2LHHFI2AkHParQa3fwwHhVijolmq8QCLcBGAs/s1600/hottest%2Bwebcam%2Bgirl%2B-%2BAnemonalove.jpg
Citer
0 #1 Silvia 11-06-2017 17:03
-- Dramaticalⅼy іmprove yoսr search engine rank - Even though there
аre software ɑpplicatіоns available on the market which heⅼp automate the linking proсess, use them ѕparingly, if at all.
Good articles sіtes genuinely realⅼy rznk estremely and havee off highlʏ well-qualified traffic.
This tool can be VERY valսable when planning yoᥙr search engine optimization markᥱting.


Alsoo visit my web-site :: backlinks: https://www.fiverr.com/cldisplay_seo/build-25-to-30-real-high-authority-backlinks-for-your-site
Citer

Ajouter un Commentaire


Code de sécurité
Rafraîchir