[SQL Server] Envoyer par mail en format HTML l’historique d’exécution des jobs

CREATE PROCEDURE [dbo].[usp_RapportHistoriquesJobs]

                @SERVEURS NVARCHAR(100)= NULL

                WITH EXECUTE AS OWNER

 AS

/*

Auteur : KHARROUBI Zouhaier

Date Création : 24/06/2013

Objectif : afficher le rapport d'execution des jobs des serveurs SQLServer

--Important : le contenu du parametre @SERVEURS doit etre comme le suivant : 'SQLSERVER_1,SQLSERVER_2,SQLSERVER_3,SQLSERVER_4,'

*/

                SET DATEFORMAT DMY

 

                DECLARE @LISTE_SERVEUR VARCHAR(MAX)

                DECLARE @REQUETE NVARCHAR(1000)

                DECLARE @NOM_SRV_LIEN    VARCHAR(35)

                DECLARE @NOM_SERVEUR    VARCHAR(35)

                DECLARE @SEPARATEUR CHAR(1)

                DECLARE @POSITION           INT

                DECLARE @DEBUT                INT

                DECLARE @TAILLE               INT          

                DECLARE @JOB_START_DATE DATETIME

                DECLARE @txtLine    AS NVARCHAR(MAX)

                DECLARE @tableHTML  AS NVARCHAR(MAX)

 

                SET @SEPARATEUR = ','

                SET @JOB_START_DATE = CAST(FLOOR(CAST(DATEADD(DAY,-1,GetDate()) AS FLOAT)) AS DATETIME)

 

 

                SET NOCOUNT ON

 

                IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#JOB_HISTORY]'))

                               DROP TABLE #JOB_HISTORY

 

                CREATE TABLE #JOB_HISTORY

    (

     SERVER_NAME NVARCHAR(35),

                 JOB NVARCHAR(100),

                 STATUS_EXECUTION CHAR(9),

                 DATE_EXECUTION DATETIME

    )

                IF @SERVEURS IS NULL OR @SERVEURS='ALL'

                               BEGIN

                                               SET @LISTE_SERVEUR = 'SQLSERVER_1' + @SEPARATEUR + 'SQLSERVER_2' + @SEPARATEUR + 'SQLSERVER_3' + @SEPARATEUR

                                               SET @LISTE_SERVEUR = @LISTE_SERVEUR + 'SQLSERVER_4' + @SEPARATEUR

                                               SET @LISTE_SERVEUR = @LISTE_SERVEUR + 'SQLSERVER_5\NomInstance' + @SEPARATEUR

                                               SET @LISTE_SERVEUR = @LISTE_SERVEUR + 'SQLSERVER_6\NomInstance' + @SEPARATEUR                                

                               END

                ELSE

                               SET @LISTE_SERVEUR=@SERVEURS + @SEPARATEUR

                              

                SET @POSITION = CHARINDEX(@SEPARATEUR, @LISTE_SERVEUR)

                SET @DEBUT = 1

                WHILE @POSITION <= LEN(@LISTE_SERVEUR) AND @POSITION>0

                   BEGIN

                               SET @TAILLE=@POSITION-@DEBUT

                               SET @NOM_SRV_LIEN = SubString(@LISTE_SERVEUR, @DEBUT, @TAILLE )

                               SET @DEBUT = @POSITION + 1

                               SET @POSITION = CHARINDEX(@SEPARATEUR, @LISTE_SERVEUR,@POSITION+1)     

                                              

                                               SELECT @NOM_SERVEUR = dbo.ServerName(@NOM_SRV_LIEN)

                                               IF @NOM_SERVEUR <> 'SQLSERVER_1'

                                                 /******Interroger historique des jobs sur les serveurs liés ******/

                                                               BEGIN

                                                                               SET @REQUETE = N'INSERT INTO #JOB_HISTORY SELECT ''' + @NOM_SERVEUR + ''' AS ServerName,'

                                                                              SET @REQUETE = @REQUETE + N'JOB, STATUS_EXECUTION, DATE_EXECUTION '

                                                                              SET @REQUETE = @REQUETE + N'FROM OPENROWSET(''SQLNCLI'', ''Server=' + @NOM_SRV_LIEN + N';Trusted_Connection=yes;'','

                                                                              SET @REQUETE = @REQUETE + N'''SELECT JOB,STATUS_EXECUTION ,MAX(DATE_EXECUTION) DATE_EXECUTION '

                                                                              SET @REQUETE = @REQUETE + N'FROM '

                                                                              SET @REQUETE = @REQUETE + N'('

                                                                              SET @REQUETE = @REQUETE + N'SELECT  name JOB,'

                                                                              SET @REQUETE = @REQUETE + N'STATUS_EXECUTION=CASE run_status '

                                                                               SET @REQUETE = @REQUETE + N'WHEN 1 THEN ''''SUCCEEDED'''' '

                                                                              SET @REQUETE = @REQUETE + N'ELSE  ''''FAILED'''' '

                                                                              SET @REQUETE = @REQUETE + N'END '

                                                                               SET @REQUETE = @REQUETE + N',CAST(CAST(run_date AS CHAR(10)) + '''' '''' '

                                                                              SET @REQUETE = @REQUETE + N'+ SUBSTRING(run_time,1,2) '

                                                                              SET @REQUETE = @REQUETE + N'+ '''':'''' + SUBSTRING(run_time,3,2) '

                                                                              SET @REQUETE = @REQUETE + N'+ '''':'''' + SUBSTRING(run_time,5,2) AS DATETIME) DATE_EXECUTION '

                                                                              SET @REQUETE = @REQUETE + N'FROM '

                                                                              SET @REQUETE = @REQUETE + N'( '

                                                                              SET @REQUETE = @REQUETE + N'SELECT J.name,H.run_status '

                                                                              SET @REQUETE = @REQUETE + N',H.run_date '

                                                                              SET @REQUETE = @REQUETE + N',REPLICATE(''''0'''', 6 - LEN(H.run_time)) + CAST(H.run_time AS CHAR(6)) run_time '

                                                                              SET @REQUETE = @REQUETE + N',H.run_duration FROM msdb..sysjobs J '

                                                                              SET @REQUETE = @REQUETE + N'JOIN msdb..sysjobhistory H ON H.job_id=J.job_id '

                                                                              SET @REQUETE = @REQUETE + N'WHERE H.step_id=0 AND J.enabled=1 '

                                                                              SET @REQUETE = @REQUETE + N') T '

                                                                              SET @REQUETE = @REQUETE + N') RES_FINAL '

                                                                              SET @REQUETE = @REQUETE + N'GROUP BY JOB,STATUS_EXECUTION '

                                                                              SET @REQUETE = @REQUETE + N'ORDER BY JOB,DATE_EXECUTION '')'

 

                                                               END

                                               Else

                                                               BEGIN

                                                                            /******Interroger historique des jobs sur le serveur principal ******/

                                                                              SET @REQUETE = N'INSERT INTO #JOB_HISTORY SELECT ''' + 'SQLSERVER_3' + ''' AS ServerName,'

                                                                              SET @REQUETE = @REQUETE + N'JOB,STATUS_EXECUTION ,MAX(DATE_EXECUTION) DATE_EXECUTION '

                                                                              SET @REQUETE = @REQUETE + N'FROM '

                                                                              SET @REQUETE = @REQUETE + N'('

                                                                              SET @REQUETE = @REQUETE + N'SELECT  name JOB,'

                                                                              SET @REQUETE = @REQUETE + N'STATUS_EXECUTION=CASE run_status '

                                                                              SET @REQUETE = @REQUETE + N'WHEN 1 THEN ''SUCCEEDED'' '

                                                                              SET @REQUETE = @REQUETE + N'ELSE  ''FAILED'' '

                                                                              SET @REQUETE = @REQUETE + N'END'

                                                                              SET @REQUETE = @REQUETE + N',CAST(CAST(run_date AS CHAR(10)) + '' '''

                                                                              SET @REQUETE = @REQUETE + N'+ SUBSTRING(run_time,1,2)'

                                                                              SET @REQUETE = @REQUETE + N'+ '':'' + SUBSTRING(run_time,3,2)'

                                                                              SET @REQUETE = @REQUETE + N'+ '':'' + SUBSTRING(run_time,5,2) AS DATETIME) DATE_EXECUTION '

                                                                              SET @REQUETE = @REQUETE + N'FROM '

                                                                              SET @REQUETE = @REQUETE + N'('

                                                                              SET @REQUETE = @REQUETE + N'SELECT J.name,H.run_status'

                                                                              SET @REQUETE = @REQUETE + N',H.run_date'

                                                                              SET @REQUETE = @REQUETE + N',REPLICATE(''0'', 6 - LEN(H.run_time)) + CAST(H.run_time AS CHAR(6)) run_time'

                                                                              SET @REQUETE = @REQUETE + N',H.run_duration FROM msdb..sysjobs J '

                                                                              SET @REQUETE = @REQUETE + N'JOIN msdb..sysjobhistory H ON H.job_id=J.job_id '

                                                                              SET @REQUETE = @REQUETE + N'WHERE H.step_id=0 AND J.enabled=1 '

                                                                              SET @REQUETE = @REQUETE + N') T '

                                                                              SET @REQUETE = @REQUETE + N') RES_FINAL '

                                                                              SET @REQUETE = @REQUETE + N'GROUP BY JOB,STATUS_EXECUTION '

                                                                              SET @REQUETE = @REQUETE + N'ORDER BY JOB,DATE_EXECUTION'

                                                               END

                                               EXEC  (@REQUETE)

                   END

               

                SET NOCOUNT OFF

               

                -- Envoie du rapport pour tous les jobs SQLServer sur tous les serveurs

                SET @tableHTML =

                N'<H><b>Bonjour</b><BR></BR></H>' +

                N'<H><b>Veuillez trouver ci-dessous le journal d''exécution des Jobs SQLServer : </b> <BR></BR></H>'

               

                DECLARE liste_serveurs CURSOR FOR

    SELECT DISTINCT SERVER_NAME FROM #JOB_HISTORY

                OPEN liste_serveurs

 

                FETCH NEXT FROM liste_serveurs INTO @NOM_SRV_LIEN

 

                WHILE @@FETCH_STATUS = 0

 

                               BEGIN

                              

                                               SET @tableHTML = @tableHTML + N'<H><b><BR><font color="blue" size="10">' + @NOM_SRV_LIEN + ' : </font></BR></b></H>'

                                              

                                               SET @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">Job</font></th><th bgcolor="#4340A4"><font color="white">Status Exécution</font></th><th bgcolor="#4340A4"><font color="white">Date Exécution</font></th>'

                                                                             

                                               SET @tableHTML = @tableHTML +       

                                               CAST(( SELECT [td/@style] =CASE WHEN STATUS_EXECUTION NOT LIKE '%SUCCEEDED%' THEN 'color:red; font-weight:bold' ELSE 'color:green; font-weight:bold'END,

                                               td = ROW_NUMBER() OVER(ORDER BY SERVER_NAME,JOB,DATE_EXECUTION ASC) ,       ' ',

                                               [td/@style] =CASE WHEN STATUS_EXECUTION NOT LIKE '%SUCCEEDED%' THEN 'color:red; font-weight:bold' ELSE 'color:green; font-weight:bold' END,

                                               td =  SERVER_NAME ,       ' ',

                                               [td/@style] =CASE WHEN STATUS_EXECUTION NOT LIKE '%SUCCEEDED%' THEN 'color:red; font-weight:bold' ELSE 'color:green; font-weight:bold'END,

                                               td = JOB,       ' ',

                                               [td/@style] =CASE WHEN STATUS_EXECUTION NOT LIKE '%SUCCEEDED%' THEN 'color:red; font-weight:bold' ELSE 'color:green; font-weight:bold'END,

                                               td = STATUS_EXECUTION,       ' ',

                                               [td/@style] =CASE WHEN STATUS_EXECUTION NOT LIKE '%SUCCEEDED%' THEN 'color:red; font-weight:bold' ELSE 'color:green; font-weight:bold'END,

                                               td = CONVERT(VARCHAR(23), DATE_EXECUTION, 120) FROM #JOB_HISTORY WHERE DATEDIFF(DAY,DATE_EXECUTION,GETDATE())<=1 AND SERVER_NAME LIKE '%' + @NOM_SRV_LIEN + '%'

                                               ORDER BY SERVER_NAME,JOB,DATE_EXECUTION

                                               FOR XML PATH('tr'), TYPE

                                               ) AS NVARCHAR(MAX) )

                                              

                                               SET @tableHTML = @tableHTML + N'</table>'

                                               FETCH NEXT FROM liste_serveurs INTO @NOM_SRV_LIEN

                               END

 

                SET @tableHTML = @tableHTML +       

                N'</table>' +

                N'<H><BR></BR></H>' +

                N'<H><b>Merci d''analyser et résoudre les jobs qui ont échoués.</b> <BR></BR></H>' +

                N'<H><i>Cordialement,<BR></BR></H>' +

                N'<H>Service Bases de Données</i></H>' ;

               

                EXEC msdb..sp_send_dbmail@profile_name='EnvoiMail',@importance='High', @recipients='Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.',

                @subject='JOURNAL D''EXECUTION DES JOBS SQLServer', @body= @tableHTML, @body_format = 'HTML' ;

               

                -- Envoie du rapport pour tous les jobs SQLServer que leurs noms commencent par ZZZ

                SET @tableHTML =

                N'<H><b>Bonjour</b><BR></BR></H>' +

                N'<H><b>Veuillez trouver ci-dessous le journal d''exécution des Jobs SQLServer : </b> <BR></BR></H>' +

                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">Job</font></th><th bgcolor="#4340A4"><font color="white">Status Exécution</font></th><th bgcolor="#4340A4"><font color="white">Date Exécution</font></th>'

                                              

                SET @tableHTML = @tableHTML +       

                CAST(( SELECT [td/@style] =CASE WHEN STATUS_EXECUTION NOT LIKE '%SUCCEEDED%' THEN 'color:red; font-weight:bold' ELSE 'color:green; font-weight:bold'END,

                td = ROW_NUMBER() OVER(ORDER BY SERVER_NAME,JOB,DATE_EXECUTION ASC) ,       ' ',

                [td/@style] =CASE WHEN STATUS_EXECUTION NOT LIKE '%SUCCEEDED%' THEN 'color:red; font-weight:bold' ELSE 'color:green; font-weight:bold'END,

                td =  SERVER_NAME ,       ' ',

                [td/@style] =CASE WHEN STATUS_EXECUTION NOT LIKE '%SUCCEEDED%' THEN 'color:red; font-weight:bold' ELSE 'color:green; font-weight:bold'END,

                td = JOB,       ' ',

                [td/@style] =CASE WHEN STATUS_EXECUTION NOT LIKE '%SUCCEEDED%' THEN 'color:red; font-weight:bold' ELSE 'color:green; font-weight:bold'END,

                td = STATUS_EXECUTION,       ' ',

                [td/@style] =CASE WHEN STATUS_EXECUTION NOT LIKE '%SUCCEEDED%' THEN 'color:red; font-weight:bold' ELSE 'color:green; font-weight:bold'END,

                td = CONVERT(VARCHAR(23), DATE_EXECUTION, 120) FROM #JOB_HISTORY WHERE DATEDIFF(DAY,DATE_EXECUTION,GETDATE())<=AND JOB LIKE 'ZZZ%'  ORDER BY SERVER_NAME,JOB,DATE_EXECUTION

                FOR XML PATH('tr'), TYPE

                ) AS NVARCHAR(MAX) )

 

                SET @tableHTML = @tableHTML +       

                N'</table>' +

                N'<H><BR></BR></H>' +

                N'<H><b>Merci d''analyser et résoudre les jobs qui ont échoués.</b> <BR></BR></H>' +

                N'<H><i>Cordialement,<BR></BR></H>' +

                N'<H>Service Bases de Données</i></H>' ;

               

                EXEC msdb..sp_send_dbmail@profile_name='EnvoiMail',@importance='High', @recipients='Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.',

                @subject='JOURNAL D''EXECUTION DES JOBS SQLServer', @body= @tableHTML, @body_format = 'HTML' ;

 

                CLOSE liste_serveurs

                DEALLOCATE liste_serveurs

                DROP TABLE #JOB_HISTORY

Ajouter un Commentaire


Code de sécurité
Rafraîchir