[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())<=1 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