[SQL Server] Afficher l'historique d'exécution des jobs et des packages SSIS

  • Imprimer

-- =============================================

-- Author:          Zouhaier KHARROUBI

-- Create date:     18/02/2019

-- Description:     Afficher l'historique d'exécution des jobs et des packages SSIS

-- =============================================

        --Add the parameters for the stored procedure here

DECLARE      @Date_Debut                      AS DATETIME = NULL

              ,@Lancer_Requete                  AS BIT=1

              ,@destinataire_mail               AS VARCHAR(500)=NULL

 

BEGIN

 

       SET NOCOUNT ON;

       SET FMTONLY OFF;

 

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

             BEGIN

                    DROP TABLE #Serveurs_LiesTemp

             END

 

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

             BEGIN

                    DROP TABLE #Serveurs_Lies

             END

 

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

             BEGIN

                    DROP TABLE #Objets

             END

 

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

             BEGIN

                    DROP TABLE #ResultatTemp

             END

 

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

             BEGIN

                    DROP TABLE #Resultat

             END

 

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

             BEGIN

                    DROP TABLE #ResultatFinal

             END

 

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

             BEGIN

                    DROP TABLE   #ListeJob

             END

 

       CREATE TABLE #Serveurs_LiesTemp

       (

       SRV_NAME                          VARCHAR(150),

       SRV_PROVIDERNAME           VARCHAR(150),

       SRV_PRODUCT                       VARCHAR(150),

       SRV_DATASOURCE                    VARCHAR(150),

       SRV_PROVIDERSTRING         VARCHAR(150),

       SRV_LOCATION               VARCHAR(150),

       SRV_CAT                                 VARCHAR(150)

       )                         

 

       CREATE TABLE #Serveurs_Lies

       (

       Num_Lig                                 INT IDENTITY (1,1),

       SRV_NAME                          VARCHAR(150),

       SRV_DATASOURCE                    VARCHAR(150),

       ServerName                        VARCHAR(150) NULL

       ) 

 

       CREATE NONCLUSTERED INDEX [IDX_#Serveurs_Lies_ServerName] ON #Serveurs_Lies

       (

             ServerName ASC

       )

 

 

       CREATE TABLE #Objets

       (

       Valeur                            VARCHAR(150)  NULL,

       Type_Val                          VARCHAR(70)         NULL

       )

 

       CREATE NONCLUSTERED INDEX [IDX_#Objets_Type_Val] ON #Objets

       (

             Type_Val ASC

       )

 

       CREATE TABLE #Resultat

       (

             Num_Lig                                                      INT                                     ,

             Environnement                                  varchar(100)        NULL,

             Serveur_Lie                                           varchar(150)        NULL,

             Nom_Serveur                                           varchar(150)        NULL,

             Job_Name                                              sysname                           NULL,

             instance_id_job                                       bigint                     NULL,

             run_date_job                                   int                               NULL,

             run_time_job                                   varchar(8000)       NULL,

             date_heure_deb_exec_job                        datetime                   NULL,

             date_heure_fin_exec_job                        datetime                   NULL,

             run_duration_job                               varchar(8)                 NULL,

             run_status_job                                        int                               NULL,

             run_status_desc_job                            varchar(9)                 NULL,

             message_erreur_job                             nvarchar(4000)             NULL,              

             step_name                                             sysname                           NULL,

             command                                                      nvarchar(max)       NULL,

             instance_id_step_job                     bigint                     NULL,              

             run_date_step_job                              int                               NULL,

             run_time_step_job                              varchar(8000)       NULL,

             date_heure_deb_exec_step_job             datetime                   NULL,

             date_heure_fin_exec_step_job             datetime                   NULL,

             run_duration_step_job                          varchar(8)                 NULL,

             run_status_step_job                            int                               NULL,

             run_status_desc_step_job                 varchar(9)                 NULL,

             message_erreur_step_job                        nvarchar(4000)             NULL,

             Chemin_Package                                        nvarchar(526)       NULL,

             Chemin_Projet                                  nvarchar(543)       NULL,

             execution_id                                   bigint                     NULL,

             event_message_id                               bigint                     NULL,

             status_package                                        int                               NULL,

             status_desc_package                            varchar(18)                NULL,

             start_time_package                             datetime                   NULL,

             end_time_package                               datetime                   NULL,

             machine_name                                   nvarchar(128)       NULL,

             event_name                                            nvarchar(1024)             NULL,

             message_time                                   datetime                   NULL,

             message_erreur_package                         nvarchar(max)       NULL,

             message_source_name                            nvarchar(4000)             NULL,

             subcomponent_name                              nvarchar(4000)             NULL,

             execution_path                                        nvarchar(max)       NULL,

             CommandBackup                                  varchar(MAX)        NULL,

             destinataire_mail                              varchar(500)        NULL,

             Date_Debut_Requete                             datetime                   NULL,

             Date_Fin_Requete                               datetime                   NULL,

             Duree_Requete_Second                     int                               NULL,

             echec_collect                                  bit                               NULL,

             message_erreur_synthese                        varchar(max)        NULL,

             message_erreur_exec                            varchar(max)        NULL

       )

 

       CREATE NONCLUSTERED INDEX [IDX_#Resultat_Environnement_Serveur_Lie_Job_Name] ON #Resultat

       (

             Environnement                                  ASC,

             Serveur_Lie                                           ASC,

             Job_Name                                              ASC

       )

 

             CREATE TABLE #ResultatTemp

       (

             Num_Lig                                                      INT IDENTITY  (1,1),

             Environnement                                  varchar(100)        NULL,

             Serveur_Lie                                           varchar(150)        NULL,

             Nom_Serveur                                           varchar(150)        NULL,

             Job_Name                                              sysname                           NULL,

             instance_id_job                                       bigint                     NULL,

             run_date_job                                   int                               NULL,

             run_time_job                                   varchar(8000)       NULL,

             date_heure_deb_exec_job                        datetime                   NULL,

             date_heure_fin_exec_job                        datetime                   NULL,

             run_duration_job                               varchar(8)                 NULL,

             run_status_job                                        int                               NULL,

             run_status_desc_job                            varchar(9)                 NULL,

             message_erreur_job                             nvarchar(4000)             NULL,              

             step_name                                             sysname                           NULL,

             command                                                      nvarchar(max)       NULL,

             instance_id_step_job                     bigint                     NULL,              

             run_date_step_job                              int                               NULL,

             run_time_step_job                              varchar(8000)       NULL,

             date_heure_deb_exec_step_job             datetime                   NULL,

             date_heure_fin_exec_step_job             datetime                   NULL,

             run_duration_step_job                          varchar(8)                 NULL,

             run_status_step_job                            int                               NULL,

             run_status_desc_step_job                 varchar(9)                 NULL,

             message_erreur_step_job                        nvarchar(4000)             NULL,

             Chemin_Package                                        nvarchar(526)       NULL,

             Chemin_Projet                                  nvarchar(543)       NULL,

             execution_id                                   bigint                     NULL,

             event_message_id                               bigint                     NULL,

             status_package                                        int                               NULL,

             status_desc_package                            varchar(18)                NULL,

             start_time_package                             datetime                   NULL,

             end_time_package                               datetime                   NULL,

             machine_name                                   nvarchar(128)       NULL,

             event_name                                            nvarchar(1024)             NULL,

             message_time                                   datetime                   NULL,

             message_erreur_package                         nvarchar(max)       NULL,

             message_source_name                            nvarchar(4000)             NULL,

             subcomponent_name                              nvarchar(4000)             NULL,

             execution_path                                        nvarchar(max)       NULL,

             CommandBackup                                  varchar(MAX)        NULL,

             destinataire_mail                              varchar(500)        NULL,

             Date_Debut_Requete                             datetime                   NULL,

             Date_Fin_Requete                               datetime                   NULL,

             Duree_Requete_Second                     int                               NULL,

             echec_collect                                  bit                               NULL,

             message_erreur_synthese                        varchar(max)        NULL,

             message_erreur_exec                            varchar(max)        NULL

       )

 

       CREATE NONCLUSTERED INDEX [IDX_#ResultatTemp_Environnement_Serveur_Lie_Job_Name] ON #ResultatTemp

       (

             Environnement                                  ASC,

             Serveur_Lie                                           ASC,

             Job_Name                                              ASC

       )

 

       CREATE TABLE #ListeJob

       (

             Num_Lig                                                      INT IDENTITY (1,1),

             Serveur_Lie                                           varchar(150)        NULL,

             Job_Name                                              sysname                           NULL,

             date_heure_deb_exec_job                        datetime                   NULL

       )

 

 

 

       DECLARE       @Num_Lig                         AS INT

                    ,@Nbre_Lig                        AS INT

                    ,@Serveur_Lie              AS VARCHAR(150)

                    ,@ServerName               AS NVARCHAR(4000)

                    ,@DataBaseName                    AS NVARCHAR(150)

                    ,@Requete                         AS NVARCHAR(MAX)

                    ,@Date_DebutCast           AS NVARCHAR(30)

                    ,@LocalHot                        AS VARCHAR(50)

                    ,@Date_Debut_Requete AS DATETIME

                    ,@Date_Fin_Requete         AS DATETIME

                    ,@Date_Courante                   AS DATE

      

 

    -- Insert statements for procedure here

 

       SELECT @LocalHot           = @@SERVERNAME

               ,@Date_Courante   = GETDATE()

               --,@Date_Courante = '20190218'

 

       IF  @Date_Debut IS NULL

             BEGIN

                    SELECT @Date_Debut=DATEADD(HOUR,9,CAST(DATEADD(DAY,-1,@Date_Courante) AS DATETIME))

             END

 

       SELECT @Date_DebutCast= CONVERT(NVARCHAR(30),@Date_Debut, 126)

 

      

       IF @Lancer_Requete=1

             BEGIN

                    INSERT INTO #Serveurs_LiesTemp

                    EXEC sp_linkedservers 

 

 

                     INSERT INTO #Serveurs_Lies (SRV_NAME,SRV_DATASOURCE)

                    SELECT DISTINCT SRV_NAME,SRV_DATASOURCE FROM #Serveurs_LiesTemp WHERE SRV_NAME NOT IN('MonServeur_1','MonServeur_2')

                    --AND SRV_NAME='MonServeur_3'

            

                    SELECT @Num_Lig=1,@Nbre_Lig=COUNT(Num_Lig) FROM #Serveurs_Lies

 

                    WHILE  @Num_Lig <= @Nbre_Lig

                           BEGIN

                                  BEGIN TRY

 

                                        SELECT @Serveur_Lie =SRV_DATASOURCE,@ServerName = NULL FROM #Serveurs_Lies WHERE Num_Lig=@Num_Lig

 

                                       

                                        SELECT @Requete     = N'SELECT @ServerName_Out=ServerName FROM OPENQUERY(' + QUOTENAME(@Serveur_Lie) + N',''SELECT @@SERVERNAME AS ServerName'')'

                                        EXECUTE sp_executesql @Requete,N'@ServerName_Out VARCHAR(150) OUTPUT',@SERVERNAME_Out=@ServerName OUTPUT

 

                                        IF NOT EXISTS (SELECT ServerName FROM #Serveurs_Lies WHERE ISNULL(ServerName,'')=@ServerName)

                                               BEGIN

 

                                                      UPDATE #Serveurs_Lies

                                                             SET

                                                             ServerName=@ServerName

                                                      WHERE Num_Lig=@Num_Lig

 

                                                      TRUNCATE TABLE #Objets

 

                                                      --Vérifier l'existance de la base SSISDB

                                                      SELECT @Requete     = N'SELECT name AS Valeur, ''DATABASE'' AS Type_Val FROM ' + QUOTENAME(@Serveur_Lie) + '.master.sys.databases WHERE name=''SSISDB'''

                   

                                                      INSERT INTO #Objets (Valeur,Type_Val)

                                                      EXECUTE sp_executesql @Requete

 

                                                      SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN N'SELECT * FROM OPENQUERY(' + QUOTENAME(@Serveur_Lie) + ',''' ELSE '' END

 

                                                      IF EXISTS (SELECT Valeur FROM #Objets WHERE Type_Val='DATABASE')

                                                             BEGIN

                                                                                                                                      

                                                                   SELECT @Requete = @Requete + N'SELECT DISTINCT ''''' + @Serveur_Lie + ''''' AS Serveur_Lie, ''''' + @ServerName + ''''' AS Nom_Serveur,T1.[name] AS Job_Name,T3.instance_id AS instance_id_job,T3.run_date AS run_date_job

                                                                   ,REPLICATE(''''0'''', 6 - LEN(T3.run_time))

                                                                   + CAST(T3.run_time AS CHAR(6)) AS run_time_job

                                 

                                                                   ,CASE T3.run_date WHEN 0 THEN NULL ELSE

                                                                   CONVERT(datetime,

                                                                                 STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                 STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120) END date_heure_deb_exec_job

                                 

                                                                   ,REPLICATE(''''0'''',6-LEN(CAST(T3.run_duration AS VARCHAR(8)))) + CAST(T3.run_duration AS VARCHAR(8))  AS run_duration_job,T3.run_status AS run_status_job

                                                                   ,run_status_desc_job = CASE T3.run_status WHEN 1 THEN ''''Succeeded'''' ELSE  ''''Failed'''' END,T3.[message] AS message_erreur_job

                                                                    ,T2.step_name,T2.command,T4.instance_id AS instance_id_step_job,T4.run_date AS run_date_step_job

                                                                   ,REPLICATE(''''0'''', 6 - LEN(T4.run_time))

                                                                   + CAST(T4.run_time AS CHAR(6)) AS run_time_step_job

                                 

                                                                   ,CASE T4.run_date WHEN 0 THEN NULL ELSE

                                                                   CONVERT(datetime,

                                                                                 STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                 STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120) END date_heure_deb_exec_step_job

                                                                  

                                                                   ,REPLICATE(''''0'''',6-LEN(CAST(T4.run_duration AS VARCHAR(8)))) + CAST(T4.run_duration AS VARCHAR(8)) AS run_duration_step_job,T4.run_status AS run_status_step_job

                                                                    ,run_status_desc_step_job = CASE T4.run_status WHEN 1 THEN ''''Succeeded'''' ELSE  ''''Failed'''' END,T4.[message] AS message_erreur_step_job

 

                                                             ,T5.Chemin_Package,T5.Chemin_Projet,T5.execution_id,T5.event_message_id,T5.status_package,T5.status_desc_package

                                                                    ,T5.start_time_package,T5.end_time_package,T5.machine_name

                                                                   ,T5.event_name,T5.message_time

                                                                  ,T5.[message_erreur_package],T5.[message_source_name],T5.[subcomponent_name],T5.[execution_path]

                                                                   ,T6.CommandBackup'

 

                                                                   SELECT @Requete =  @Requete + N' FROM msdb.dbo.sysjobs T1

                                                                   JOIN msdb.dbo.[sysjobsteps] T2 ON T2.job_id=T1.job_id

                                                                   JOIN msdb.dbo.sysjobhistory T3 ON T3.job_id=T1.job_id AND T3.step_id=0'

 

                                                                   + N' AND CONVERT(datetime,

                                                                   STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                    STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>=''''' + @Date_DebutCast + ''''''

 

                                                                   + ' JOIN msdb.dbo.sysjobhistory T4 ON T4.job_id=T1.job_id AND T4.step_id=T2.step_id'

                                                                   + N' AND CONVERT(datetime,

                                                                                               STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                               STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>=''''' + @Date_DebutCast + ''''''

 

                                                                   + N' AND CONVERT(datetime,

                                                                                               STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                               STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>='

 

                                                                   + N'CONVERT(datetime,

                                                                   STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                    STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)'

 

                                                                   + N' LEFT JOIN (

                                                                   SELECT DISTINCT S5.event_message_id,S1.project_id,''''\SSISDB\''''+ S3.name +''''\Projets\'''' + S1.[name] + ''''\Packages\'''' + S2.[name] AS Chemin_Projet

                                                                   ,''''\SSISDB\''''+ S3.name +''''\'''' + S1.[name] + ''''\'''' + S2.[name] AS Chemin_Package,

                                                                   S4.[execution_id],

                                                                   S4.[status] AS status_package,

                                                                   [status_desc_package]= CASE S4.[status] WHEN 1 THEN ''''Created''''

                                                                                                                         WHEN 2 THEN ''''Running''''

                                                                                                                         WHEN 3 THEN ''''Canceled''''

                                                                                                                         WHEN 4 THEN ''''Failed''''

                                                                                                                         WHEN 5 THEN ''''Pending''''

                                                                                                                         WHEN 6 THEN ''''Ended Unexpectedly''''

                                                                                                                         WHEN 7 THEN ''''Succeeded''''

                                                                                                                         WHEN 8 THEN ''''Stopping''''

                                                                                                                         WHEN 9 THEN ''''Completed ''''

 

                                                                   ELSE ''''Inconnu''''

                                                                   END'

 

                                                                   SELECT @Requete =  @Requete + N',CONVERT (datetime, S4.[start_time]) AS start_time_package,

                                                                   CONVERT (datetime, S4.[end_time]) AS end_time_package,

                                                                    /* CONVERT(FLOAT,DATEDIFF(millisecond, S4.[start_time], ISNULL(S4.[end_time], SYSDATETIMEOFFSET())))/1000 AS duration_package, */

                                                                   S4.[machine_name],

                                                                    S5.event_name,CONVERT(DATETIME,S5.message_time) AS message_time,

                                                                   CASE WHEN LEN(S5.[message]) <= 4096 THEN S5.[message] ELSE LEFT(S5.[message], 1024) + ''''...'''' END AS [message_erreur_package],

                                                                   CASE WHEN LEN(S5.[message_source_name]) <= 1024 THEN S5.[message_source_name] ELSE LEFT(S5.[message_source_name], 1024) + ''''...'''' END AS [message_source_name],

                                                                   CASE WHEN LEN(S5.[subcomponent_name]) <= 1024 THEN S5.[subcomponent_name] ELSE LEFT(S5.[subcomponent_name], 1024) + ''''...'''' END AS [subcomponent_name]

                                                                   ,CASE WHEN LEN(S5.[execution_path]) <= 1024 THEN S5.[execution_path] ELSE LEFT(S5.[execution_path], 1024) + ''''...'''' END AS [execution_path]                               

                                                                   FROM [SSISDB].[internal].[projects] S1

                                                                   JOIN [SSISDB].[internal].[packages] S2 ON S2.project_id=S1.project_id

                                                                   JOIN [SSISDB].[internal].[folders] S3 ON S3.folder_id=S1.folder_id'

 

                                                                   SELECT @Requete =  @Requete + N' JOIN [SSISDB].[catalog].[executions] S4 ON S4.folder_name=S3.name AND S4.project_name=S1.name AND S4.package_name=S2.name

                                                                   JOIN [SSISDB].[catalog].[event_messages] S5 ON S5.operation_id=S4.execution_id

                                                                   LEFT JOIN [SSISDB].[catalog].[extended_operation_info] S6 ON S6.info_id=S5.extended_info_id

                                                                   WHERE S4.[start_time] >=''''' + @Date_DebutCast + ''''''

                                                                   + N') T5 ON T2.command LIKE ''''%'''' + Chemin_Package + ''''%'''' COLLATE French_CI_AS

                                                                   AND T5.start_time_package >= CASE T3.run_date WHEN 0 THEN NULL ELSE

                                                                   CONVERT(datetime,

                                                                                 STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                 STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120) END

                                                                   AND T5.end_time_package <= DATEADD(SECOND,T3.run_duration,CASE T3.run_date WHEN 0 THEN NULL ELSE

                                                                   CONVERT(datetime,

                                                                                 STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                 STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120) END)'

                                                                   + N' CROSS APPLY ('

                                                                   + N'SELECT TOP 1 Command AS CommandBackup

                                                                   FROM [msdb].[dbo].[sysjobsteps]

                                                                   WHERE command LIKE ''''%%_BACKUP%'''''

                                                             + N' ) T6'                             

                                                             + ' WHERE T1.name NOT LIKE ''''%cyclique%'''' AND T4.run_status<>1 AND T3.run_status<>1 /*AND CONVERT(datetime,

                                                                                               STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                               STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>=''''' + @Date_DebutCast + '''''*/'

                                                                    

                                                             END

                                                      ELSE

                                                             BEGIN

                                                                   IF NOT EXISTS (SELECT Valeur FROM #Objets WHERE Type_Val='DATABASE')

                                                                          BEGIN

                                                                                

                                                                                 SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN N'SELECT * FROM OPENQUERY(' + QUOTENAME(@Serveur_Lie) + ',''' ELSE '' END

 

                                                                                 SELECT @Requete = @Requete     + N'SELECT DISTINCT ''''' + @Serveur_Lie + ''''' AS Serveur_Lie,''''' + @ServerName + ''''' AS Nom_Serveur,T1.[name] AS Job_Name,T3.instance_id AS instance_id_job,T3.run_date AS run_date_job

                                                                                 ,REPLICATE(''''0'''', 6 - LEN(T3.run_time))

                                                                                 + CAST(T3.run_time AS CHAR(6)) AS run_time_job

                                 

                                                                                 ,CASE T3.run_date WHEN 0 THEN NULL ELSE

                                                                                 CONVERT(datetime,

                                                                                               STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                               STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120) END date_heure_deb_exec_job

                                                                  

                                                                                 ,REPLICATE(''''0'''',6-LEN(CAST(T3.run_duration AS VARCHAR(8)))) + CAST(T3.run_duration  AS VARCHAR(8)) AS run_duration_job,T3.run_status AS run_status_job

                                                                                 ,run_status_desc_job = CASE T3.run_status WHEN 1 THEN ''''Succeeded'''' ELSE  ''''Failed'''' END,T3.[message] AS message_erreur_job

                                                                                 ,T2.step_name,T2.command,T4.instance_id AS instance_id_step_job,T4.run_date AS run_date_step_job

                                                                                 ,REPLICATE(''''0'''', 6 - LEN(T4.run_time))

                                                                                 + CAST(T4.run_time AS CHAR(6)) AS run_time_step_job

                                 

                                                                                 ,CASE T4.run_date WHEN 0 THEN NULL ELSE

                                                                                 CONVERT(datetime,

                                                                                               STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                               STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120) END date_heure_deb_exec_step_job

                                                                  

                                                                                 ,REPLICATE(''''0'''',6-LEN(CAST(T4.run_duration AS VARCHAR(8)))) + CAST(T4.run_duration  AS VARCHAR(8)) AS run_duration_step_job,T4.run_status AS run_status_step_job

                                                                                 ,run_status_desc_step_job = CASE T4.run_status WHEN 1 THEN ''''Succeeded'''' ELSE  ''''Failed'''' END,T4.[message] AS message_erreur_step_job

                                                                                 ,NULL AS Chemin_Package,NULL AS Chemin_Projet,NULL AS execution_id,NULL AS event_message_id,NULL AS status_package,NULL AS status_desc_package

                                                                                 ,NULL AS start_time_package,NULL AS end_time_package,NULL AS machine_name

                                                                                 ,NULL AS event_name,NULL AS message_time

                                                                                 ,NULL AS [message_erreur_package],NULL AS [message_source_name],NULL AS [subcomponent_name],NULL AS [execution_path]

                                                                                 ,T5.CommandBackup'

                                                                                 SELECT @Requete = @Requete + N' FROM msdb.dbo.sysjobs T1

                                                                                 JOIN msdb.dbo.[sysjobsteps] T2 ON T2.job_id=T1.job_id

                                                                                 JOIN msdb.dbo.sysjobhistory T3 ON T3.job_id=T1.job_id AND T3.step_id=0'

 

                                                                                 + ' AND CONVERT(datetime,

                                                                                 STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                 STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>=''''' + @Date_DebutCast + ''''''

 

                                                                                 + N' JOIN msdb.dbo.sysjobhistory T4 ON T4.job_id=T1.job_id AND T4.step_id=T2.step_id'

                                                                                 + ' AND CONVERT(datetime,

                                                                                 STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                 STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>=''''' + @Date_DebutCast + ''''''

 

                                                                                 + N' AND CONVERT(datetime,

                                                                                 STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                 STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>='

 

                                                                                 + N'CONVERT(datetime,

                                                                                 STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                 STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)'

 

                                                                                 + N' CROSS APPLY ('

                                                                                 + N'SELECT TOP 1 Command AS CommandBackup

                                                                                 FROM [msdb].[dbo].[sysjobsteps]

                                                                                 WHERE command LIKE ''''%LOG_BACKUP%'''' OR command LIKE ''''%%_BACKUP%'''''

                                                                                 + N' ) T5'                      

                                                                                 + ' WHERE T1.name NOT LIKE ''''%cyclique%'''' AND T4.run_status<>1 AND T3.run_status<>1 AND CONVERT(datetime,

                                                                                               STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' +

                                                                                               STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>=''''' + @Date_DebutCast + ''''''

                                                                          END

                                                             END

                                                            

                                                             SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN @Requete + N''')' ELSE REPLACE(@Requete,'''''','''') END

                                                                                                                                                                                            

                                                             SELECT @Date_Debut_Requete = GETDATE()

 

 

                                                             INSERT INTO #ResultatTemp (Serveur_Lie,Nom_Serveur,Job_Name,instance_id_job,run_date_job,run_time_job,date_heure_deb_exec_job

                                                             ,run_duration_job,run_status_job,run_status_desc_job,message_erreur_job  

                                                      ,step_name,command,instance_id_step_job,run_date_step_job,run_time_step_job,date_heure_deb_exec_step_job

                                                      ,run_duration_step_job,run_status_step_job,run_status_desc_step_job,message_erreur_step_job,Chemin_Package,Chemin_Projet,execution_id,event_message_id,status_package,status_desc_package

                                                      ,start_time_package,end_time_package,machine_name,event_name,message_time,message_erreur_package,message_source_name,subcomponent_name,execution_path,CommandBackup)

                                                             EXECUTE sp_executesql @Requete

 

                                                             SELECT @Date_Fin_Requete = GETDATE()

 

                                                             UPDATE #ResultatTemp

                                                             SET

                                                                    Date_Debut_Requete   = @Date_Debut_Requete

                                                                   ,Date_Fin_Requete     = @Date_Fin_Requete

                                                                   ,Duree_Requete_Second = DATEDIFF(second,@Date_Debut_Requete,@Date_Fin_Requete)

                                                                   ,date_heure_fin_exec_job = DATEADD(SECOND,(3600 * SUBSTRING(run_duration_job,1,2)) + (SUBSTRING(run_duration_job,3,2) * 60) + SUBSTRING(run_duration_job,5,2),date_heure_deb_exec_job)

                                                                   ,date_heure_fin_exec_step_job = DATEADD(SECOND,(3600 * SUBSTRING(run_duration_step_job,1,2)) + (SUBSTRING(run_duration_step_job,3,2) * 60) + SUBSTRING(run_duration_step_job,5,2),date_heure_deb_exec_step_job)

                                                             WHERE Serveur_Lie=@Serveur_Lie

 

                                               END

 

                                  END TRY

                                  BEGIN CATCH

                                        INSERT INTO #ResultatTemp (Serveur_Lie,Job_Name,date_heure_deb_exec_job,message_erreur_exec,echec_collect) VALUES (@Serveur_Lie,'Ma Requete',GETDATE(), ERROR_MESSAGE(),1)

                                  END CATCH

            

                                  SELECT  @Num_Lig =   @Num_Lig + 1

 

                           END

 

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

                           BEGIN

                                  DROP TABLE [tempdb]..[HistoJob]

                           END

 

                    INSERT INTO #ListeJob (Serveur_Lie,Job_Name,date_heure_deb_exec_job)

                    SELECT Serveur_Lie,Job_Name,MAX(date_heure_deb_exec_job) AS date_heure_deb_exec_job  FROM #ResultatTemp

                    GROUP BY Serveur_Lie,Job_Name

 

                    SELECT @Num_Lig=1,@Nbre_Lig=COUNT(Serveur_Lie) FROM #ListeJob

 

                    WHILE @Num_Lig <= @Nbre_Lig

                           BEGIN

 

                                  INSERT INTO #Resultat (Num_Lig,Serveur_Lie,Nom_Serveur,Job_Name,instance_id_job,run_date_job,run_time_job,date_heure_deb_exec_job,date_heure_fin_exec_job

                                               ,run_duration_job,run_status_job,run_status_desc_job,message_erreur_job  

                                        ,step_name,command,instance_id_step_job,run_date_step_job,run_time_step_job,date_heure_deb_exec_step_job,date_heure_fin_exec_step_job

                                        ,run_duration_step_job,run_status_step_job,run_status_desc_step_job,message_erreur_step_job,Chemin_Package,Chemin_Projet,execution_id,event_message_id,status_package,status_desc_package

                                        ,start_time_package,end_time_package,machine_name,event_name,message_time,message_erreur_package,message_source_name,subcomponent_name,execution_path,CommandBackup,Date_Debut_Requete

                                               ,Date_Fin_Requete,Duree_Requete_Second,echec_collect,message_erreur_exec)                    

                                 

                                  SELECT TOP 10 T1.Num_Lig,T1.Serveur_Lie,Nom_Serveur,T1.Job_Name,instance_id_job,run_date_job,run_time_job,T1.date_heure_deb_exec_job,date_heure_fin_exec_job

                                               ,run_duration_job,run_status_job,run_status_desc_job,message_erreur_job  

                                        ,step_name,command,instance_id_step_job,run_date_step_job,run_time_step_job,date_heure_deb_exec_step_job,date_heure_fin_exec_step_job

                                        ,run_duration_step_job,run_status_step_job,run_status_desc_step_job,message_erreur_step_job,Chemin_Package,Chemin_Projet,execution_id,event_message_id,status_package,status_desc_package

                                        ,start_time_package,end_time_package,machine_name,event_name,message_time,message_erreur_package,message_source_name,subcomponent_name,execution_path,CommandBackup,Date_Debut_Requete

                                               ,Date_Fin_Requete,Duree_Requete_Second,echec_collect,message_erreur_exec

                                  FROM #ResultatTemp T1

                                  JOIN #ListeJob T2 ON T2.Serveur_Lie=T1.Serveur_Lie AND T2.Job_Name=T1.Job_Name AND T2.date_heure_deb_exec_job=T1.date_heure_deb_exec_job AND T2.Num_Lig=@Num_Lig

                                  WHERE (ISNULL(T1.event_name,'OnError')='OnError' OR ISNULL(T1.event_name,'OnWarning')='OnWarning')

 

 

                                  SELECT @Num_Lig =  @Num_Lig + 1

                           END

 

 

                    UPDATE #Resultat

                    SET

                           Environnement= CASE WHEN CommandBackup  LIKE '%SAUVEGARDE_PRODUCTION%' THEN 'PRODUCTION'

                                                      WHEN CommandBackup         LIKE '%SAUVEGARDE_HOMOLOGATION%' THEN 'HOMOLOGATION'

                                                      WHEN CommandBackup         LIKE '%SAUVEGARDE_DEVELOPPEMENT%' THEN 'DEVELOPPEMENT'

                                                      ELSE 'INCONNU'

                                                      END

                           ,destinataire_mail = CASE  WHEN Job_Name LIKE '%application_1%' THEN 'Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.'

                                                                          WHEN Job_Name LIKE '%application_2%' THEN 'Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.'

                                                                          WHEN Job_Name LIKE '%application_3%' THEN 'Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.'

                                                                          ELSE 'Destinataire Mail Inconnu'

                                                                   END

                    WHERE Environnement IS NULL

 

                    UPDATE #Resultat

                    SET

                           Environnement= CASE WHEN Serveur_Lie LIKE '%PRD%' THEN 'PRODUCTION'

                                                             WHEN Serveur_Lie LIKE '%HOM%' THEN 'HOMOLOGATION'

                                                             WHEN Serveur_Lie LIKE '%DEV%' THEN 'DEVELOPPEMENT'

                                                             ELSE 'INCONNU'

                                                      END

                    WHERE Environnement IS NULL

 

                    UPDATE T1

                    SET

                           message_erreur_synthese = T2.message_erreur_synthese

                    FROM #Resultat T1

                    JOIN (

                     SELECT environnement,Serveur_Lie,Job_Name,CAST(ROW_NUMBER() OVER(ORDER BY environnement,Serveur_Lie,Job_Name ASC) AS VARCHAR(3)) + ' ) ' +

                     environnement + ', sur ' + Serveur_Lie + ' échec du job #' + Job_Name + '# au niveau de l''étape #' + step_name + '#' AS message_erreur_synthese

                    FROM (

                    SELECT distinct environnement,Serveur_Lie,Job_Name,step_name,run_status_step_job

                    FROM #Resultat

                    WHERE run_status_step_job<>1 AND environnement<>'INCONNU'

                    ) R ) T2 ON T2.Environnement=T1.Environnement AND T2.Serveur_Lie=T1.Serveur_Lie AND T2.Job_Name=T1.Job_Name

 

                    SELECT Num_Lig,Environnement,Serveur_Lie,Nom_Serveur,Job_Name,instance_id_job,run_date_job,run_time_job,date_heure_deb_exec_job,date_heure_fin_exec_job

                    ,CASE LEN(run_duration_job) WHEN 6 THEN STUFF(STUFF(run_duration_job,3,0,':'),6,0,':') ELSE run_duration_job END AS run_duration_job

                    ,run_status_job,run_status_desc_job,message_erreur_job      

             ,step_name,command,instance_id_step_job,run_date_step_job,run_time_step_job,date_heure_deb_exec_step_job,date_heure_fin_exec_step_job

                    ,CASE LEN(run_duration_step_job) WHEN 6 THEN STUFF(STUFF(run_duration_step_job,3,0,':'),6,0,':') ELSE run_duration_step_job END AS run_duration_step_job

             ,run_status_step_job,run_status_desc_step_job,message_erreur_step_job,Chemin_Package,Chemin_Projet,execution_id,event_message_id,status_package,status_desc_package

             ,start_time_package,end_time_package,machine_name,event_name,message_time,message_erreur_package,message_source_name,subcomponent_name,execution_path,CommandBackup

                    ,destinataire_mail, Date_Debut_Requete,Date_Fin_Requete,Duree_Requete_Second,message_erreur_synthese,message_erreur_exec

                    INTO [tempdb]..HistoJob

                    FROM #Resultat

                    WHERE date_heure_deb_exec_job>=@Date_Debut AND ((status_package=4 AND ISNULL(event_name,'OnError')='OnError')

                    OR (status_package=6 AND ISNULL(event_name,'OnWarning')='OnWarning')

                    OR run_status_job<>1

                    OR ISNULL(echec_collect,0)=1)

                    ORDER BY ROW_NUMBER() OVER( ORDER BY [message_time]) DESC

 

                    --DROP TABLE #Serveurs_LiesTemp

                    --DROP TABLE #Serveurs_Lies

                    --DROP TABLE #Objets

                    --DROP TABLE #Resultat

             END

       ELSE

             BEGIN

                    SELECT Num_Lig,Environnement,Serveur_Lie,Nom_Serveur,Job_Name,instance_id_job,run_date_job,run_time_job,date_heure_deb_exec_job,date_heure_fin_exec_job,run_duration_job

                                  ,run_status_job,run_status_desc_job,message_erreur_job      

                           ,step_name,command,instance_id_step_job,run_date_step_job,run_time_step_job,date_heure_deb_exec_step_job,date_heure_fin_exec_step_job,run_duration_step_job

                                  ,run_status_step_job,run_status_desc_step_job                            

                                  /*Supprimer de l'affichage le compte AD avec lequel a été lancée l'étape du job*/

                                  ,message_erreur_step_job = CASE WHEN message_erreur_step_job LIKE '%MEKTABA-INFO\%' THEN

                                                                                        SUBSTRING(message_erreur_step_job,CHARINDEX('MEKTABA-INFO\',message_erreur_step_job,1) + 28,LEN(message_erreur_step_job))

                                                                                        WHEN message_erreur_step_job LIKE '%MEKTABA-FR\%' THEN

                                                                                        SUBSTRING(message_erreur_step_job,CHARINDEX('MEKTABA-FR\',message_erreur_step_job,1) + 28,LEN(message_erreur_step_job))                            

                                 

                                  ELSE message_erreur_step_job END

 

                                  ,Chemin_Package,Chemin_Projet,execution_id,event_message_id,status_package,status_desc_package

                           ,start_time_package,end_time_package,machine_name,event_name,message_time,message_erreur_package,message_source_name,subcomponent_name,execution_path,CommandBackup

                                  ,destinataire_mail, Date_Debut_Requete,Date_Fin_Requete,Duree_Requete_Second,message_erreur_synthese,message_erreur_exec

                    FROM [tempdb]..HistoJob

                    WHERE  (ISNULL(event_name,'OnError')='OnError' OR ISNULL(event_name,'OnWarning')='OnWarning')

                    AND Serveur_Lie<>'ServeurLie_1'

                    AND Job_Name IS NOT NULL

                    AND Job_Name NOT LIKE '%MonJob_1%'

                    AND step_name<>'Etape Test'

                    AND ISNULL(destinataire_mail,'')=ISNULL(@destinataire_mail,'')

                    ORDER BY ROW_NUMBER() OVER( ORDER BY [message_time]) DESC

             END

 

END

GO

 

 

 

-- =============================================
-- Author: Zouhaier KHARROUBI
-- Create date: 18/02/2019
-- Description: Afficher l'historique d'exécution des jobs et des packages SSIS
-- =============================================
--Add the parameters for the stored procedure here
DECLARE @Date_Debut AS DATETIME = NULL
,@Lancer_Requete AS BIT=1
,@destinataire_mail AS VARCHAR(500)=NULL
 
BEGIN
 
SET NOCOUNT ON;
SET FMTONLY OFF;
 
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Serveurs_LiesTemp]'))
BEGIN
DROP TABLE #Serveurs_LiesTemp
END
 
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Serveurs_Lies]'))
BEGIN
DROP TABLE #Serveurs_Lies
END
 
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Objets]'))
BEGIN
DROP TABLE #Objets
END
 
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#ResultatTemp]'))
BEGIN
DROP TABLE #ResultatTemp
END
 
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#Resultat]'))
BEGIN
DROP TABLE #Resultat
END
 
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#ResultatFinal]'))
BEGIN
DROP TABLE #ResultatFinal
END
 
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#ListeJob]'))
BEGIN
DROP TABLE #ListeJob
END
 
CREATE TABLE #Serveurs_LiesTemp
(
SRV_NAME VARCHAR(150),
SRV_PROVIDERNAME VARCHAR(150),
SRV_PRODUCT VARCHAR(150),
SRV_DATASOURCE VARCHAR(150),
SRV_PROVIDERSTRING VARCHAR(150),
SRV_LOCATION VARCHAR(150),
SRV_CAT VARCHAR(150)
)                          
 
CREATE TABLE #Serveurs_Lies
(
Num_Lig INT IDENTITY (1,1),
SRV_NAME VARCHAR(150),
SRV_DATASOURCE VARCHAR(150),
ServerName VARCHAR(150) NULL
)  
 
CREATE NONCLUSTERED INDEX [IDX_#Serveurs_Lies_ServerName] ON #Serveurs_Lies
(
ServerName ASC
)
 
 
CREATE TABLE #Objets
(
Valeur VARCHAR(150) NULL,
Type_Val VARCHAR(70) NULL
)
 
CREATE NONCLUSTERED INDEX [IDX_#Objets_Type_Val] ON #Objets
(
Type_Val ASC
)
 
CREATE TABLE #Resultat 
(
Num_Lig INT ,
Environnement varchar(100) NULL,
Serveur_Lie varchar(150) NULL,
Nom_Serveur varchar(150) NULL,
Job_Name sysname NULL,
instance_id_job bigint NULL,
run_date_job int NULL,
run_time_job varchar(8000) NULL,
date_heure_deb_exec_job datetime NULL,
date_heure_fin_exec_job datetime NULL,
run_duration_job varchar(8) NULL,
run_status_job int NULL,
run_status_desc_job varchar(9) NULL,
message_erreur_job nvarchar(4000) NULL,
step_name sysname NULL,
command nvarchar(max) NULL,
instance_id_step_job bigint NULL,
run_date_step_job int NULL,
run_time_step_job varchar(8000) NULL,
date_heure_deb_exec_step_job datetime NULL,
date_heure_fin_exec_step_job datetime NULL,
run_duration_step_job varchar(8) NULL,
run_status_step_job int NULL,
run_status_desc_step_job varchar(9) NULL,
message_erreur_step_job nvarchar(4000) NULL,
Chemin_Package nvarchar(526) NULL,
Chemin_Projet nvarchar(543) NULL,
execution_id bigint NULL,
event_message_id bigint NULL,
status_package int NULL,
status_desc_package varchar(18) NULL,
start_time_package datetime NULL,
end_time_package datetime NULL,
machine_name nvarchar(128) NULL,
event_name nvarchar(1024) NULL,
message_time datetime NULL,
message_erreur_package nvarchar(max) NULL,
message_source_name nvarchar(4000) NULL,
subcomponent_name nvarchar(4000) NULL,
execution_path nvarchar(max) NULL,
CommandBackup varchar(MAX) NULL,
destinataire_mail varchar(500) NULL,
Date_Debut_Requete datetime NULL,
Date_Fin_Requete datetime NULL,
Duree_Requete_Second int NULL,
echec_collect bit NULL,
message_erreur_synthese varchar(max) NULL,
message_erreur_exec varchar(max) NULL
)
 
CREATE NONCLUSTERED INDEX [IDX_#Resultat_Environnement_Serveur_Lie_Job_Name] ON #Resultat
(
Environnement ASC,
Serveur_Lie ASC,
Job_Name ASC
)
 
CREATE TABLE #ResultatTemp
(
Num_Lig INT IDENTITY (1,1),
Environnement varchar(100) NULL,
Serveur_Lie varchar(150) NULL,
Nom_Serveur varchar(150) NULL,
Job_Name sysname NULL,
instance_id_job bigint NULL,
run_date_job int NULL,
run_time_job varchar(8000) NULL,
date_heure_deb_exec_job datetime NULL,
date_heure_fin_exec_job datetime NULL,
run_duration_job varchar(8) NULL,
run_status_job int NULL,
run_status_desc_job varchar(9) NULL,
message_erreur_job nvarchar(4000) NULL,
step_name sysname NULL,
command nvarchar(max) NULL,
instance_id_step_job bigint NULL,
run_date_step_job int NULL,
run_time_step_job varchar(8000) NULL,
date_heure_deb_exec_step_job datetime NULL,
date_heure_fin_exec_step_job datetime NULL,
run_duration_step_job varchar(8) NULL,
run_status_step_job int NULL,
run_status_desc_step_job varchar(9) NULL,
message_erreur_step_job nvarchar(4000) NULL,
Chemin_Package nvarchar(526) NULL,
Chemin_Projet nvarchar(543) NULL,
execution_id bigint NULL,
event_message_id bigint NULL,
status_package int NULL,
status_desc_package varchar(18) NULL,
start_time_package datetime NULL,
end_time_package datetime NULL,
machine_name nvarchar(128) NULL,
event_name nvarchar(1024) NULL,
message_time datetime NULL,
message_erreur_package nvarchar(max) NULL,
message_source_name nvarchar(4000) NULL,
subcomponent_name nvarchar(4000) NULL,
execution_path nvarchar(max) NULL,
CommandBackup varchar(MAX) NULL,
destinataire_mail varchar(500) NULL,
Date_Debut_Requete datetime NULL,
Date_Fin_Requete datetime NULL,
Duree_Requete_Second int NULL,
echec_collect bit NULL,
message_erreur_synthese varchar(max) NULL,
message_erreur_exec varchar(max) NULL
)
 
CREATE NONCLUSTERED INDEX [IDX_#ResultatTemp_Environnement_Serveur_Lie_Job_Name] ON #ResultatTemp
(
Environnement ASC,
Serveur_Lie ASC,
Job_Name ASC
)
 
CREATE TABLE #ListeJob
(
Num_Lig INT IDENTITY (1,1),
Serveur_Lie varchar(150) NULL,
Job_Name sysname NULL,
date_heure_deb_exec_job datetime NULL
)
 
 
 
DECLARE @Num_Lig AS INT
,@Nbre_Lig AS INT
,@Serveur_Lie AS VARCHAR(150)
,@ServerName AS NVARCHAR(4000)
,@DataBaseName AS NVARCHAR(150)
,@Requete AS NVARCHAR(MAX)
,@Date_DebutCast AS NVARCHAR(30)
,@LocalHot AS VARCHAR(50)
,@Date_Debut_Requete AS DATETIME
,@Date_Fin_Requete AS DATETIME
,@Date_Courante AS DATE
 
    -- Insert statements for procedure here
 
SELECT @LocalHot = @@SERVERNAME
  ,@Date_Courante = GETDATE()
  --,@Date_Courante = '20190218'
 
IF  @Date_Debut IS NULL
BEGIN
SELECT @Date_Debut=DATEADD(HOUR,9,CAST(DATEADD(DAY,-1,@Date_Courante) AS DATETIME))
END
 
SELECT @Date_DebutCast= CONVERT(NVARCHAR(30),@Date_Debut, 126)
 
IF @Lancer_Requete=1
BEGIN
INSERT INTO #Serveurs_LiesTemp
EXEC sp_linkedservers  
 
 
INSERT INTO #Serveurs_Lies (SRV_NAME,SRV_DATASOURCE)
SELECT DISTINCT SRV_NAME,SRV_DATASOURCE FROM #Serveurs_LiesTemp WHERE SRV_NAME NOT IN('MonServeur_1','MonServeur_2')
--AND SRV_NAME='MonServeur_3'
SELECT @Num_Lig=1,@Nbre_Lig=COUNT(Num_Lig) FROM #Serveurs_Lies
 
WHILE  @Num_Lig <= @Nbre_Lig
BEGIN
BEGIN TRY
 
SELECT @Serveur_Lie =SRV_DATASOURCE,@ServerName = NULL FROM #Serveurs_Lies WHERE Num_Lig=@Num_Lig
 
SELECT @Requete = N'SELECT @ServerName_Out=ServerName FROM OPENQUERY(' + QUOTENAME(@Serveur_Lie) + N',''SELECT @@SERVERNAME AS ServerName'')'
EXECUTE sp_executesql @Requete,N'@ServerName_Out VARCHAR(150) OUTPUT',@SERVERNAME_Out=@ServerName OUTPUT
 
IF NOT EXISTS (SELECT ServerName FROM #Serveurs_Lies WHERE ISNULL(ServerName,'')=@ServerName)
BEGIN
 
UPDATE #Serveurs_Lies 
SET
ServerName=@ServerName
WHERE Num_Lig=@Num_Lig
 
TRUNCATE TABLE #Objets
 
--Vérifier l'existance de la base SSISDB
SELECT @Requete = N'SELECT name AS Valeur, ''DATABASE'' AS Type_Val FROM ' + QUOTENAME(@Serveur_Lie) + '.master.sys.databases WHERE name=''SSISDB'''
INSERT INTO #Objets (Valeur,Type_Val)
EXECUTE sp_executesql @Requete
 
SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN N'SELECT * FROM OPENQUERY(' + QUOTENAME(@Serveur_Lie) + ',''' ELSE '' END
 
IF EXISTS (SELECT Valeur FROM #Objets WHERE Type_Val='DATABASE')
BEGIN
SELECT @Requete = @Requete + N'SELECT DISTINCT ''''' + @Serveur_Lie + ''''' AS Serveur_Lie, ''''' + @ServerName + ''''' AS Nom_Serveur,T1.[name] AS Job_Name,T3.instance_id AS instance_id_job,T3.run_date AS run_date_job
,REPLICATE(''''0'''', 6 - LEN(T3.run_time)) 
+ CAST(T3.run_time AS CHAR(6)) AS run_time_job
,CASE T3.run_date WHEN 0 THEN NULL ELSE
CONVERT(datetime, 
STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' + 
STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120) END date_heure_deb_exec_job
,REPLICATE(''''0'''',6-LEN(CAST(T3.run_duration AS VARCHAR(8)))) + CAST(T3.run_duration AS VARCHAR(8))  AS run_duration_job,T3.run_status AS run_status_job 
,run_status_desc_job = CASE T3.run_status WHEN 1 THEN ''''Succeeded'''' ELSE  ''''Failed'''' END,T3.[message] AS message_erreur_job
,T2.step_name,T2.command,T4.instance_id AS instance_id_step_job,T4.run_date AS run_date_step_job
,REPLICATE(''''0'''', 6 - LEN(T4.run_time)) 
+ CAST(T4.run_time AS CHAR(6)) AS run_time_step_job
,CASE T4.run_date WHEN 0 THEN NULL ELSE
CONVERT(datetime, 
STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' + 
STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120) END date_heure_deb_exec_step_job
,REPLICATE(''''0'''',6-LEN(CAST(T4.run_duration AS VARCHAR(8)))) + CAST(T4.run_duration AS VARCHAR(8)) AS run_duration_step_job,T4.run_status AS run_status_step_job 
,run_status_desc_step_job = CASE T4.run_status WHEN 1 THEN ''''Succeeded'''' ELSE  ''''Failed'''' END,T4.[message] AS message_erreur_step_job
 
,T5.Chemin_Package,T5.Chemin_Projet,T5.execution_id,T5.event_message_id,T5.status_package,T5.status_desc_package
,T5.start_time_package,T5.end_time_package,T5.machine_name
,T5.event_name,T5.message_time
,T5.[message_erreur_package],T5.[message_source_name],T5.[subcomponent_name],T5.[execution_path]
,T6.CommandBackup'
 
SELECT @Requete =  @Requete + N' FROM msdb.dbo.sysjobs T1
JOIN msdb.dbo.[sysjobsteps] T2 ON T2.job_id=T1.job_id
JOIN msdb.dbo.sysjobhistory T3 ON T3.job_id=T1.job_id AND T3.step_id=0' 
 
+ N' AND CONVERT(datetime, 
STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' + 
STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>=''''' + @Date_DebutCast + ''''''
 
+ ' JOIN msdb.dbo.sysjobhistory T4 ON T4.job_id=T1.job_id AND T4.step_id=T2.step_id'
+ N' AND CONVERT(datetime, 
STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' + 
STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>=''''' + @Date_DebutCast + ''''''
 
+ N' AND CONVERT(datetime, 
STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' + 
STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>='
 
+ N'CONVERT(datetime, 
STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' + 
STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)' 
 
+ N' LEFT JOIN (
SELECT DISTINCT S5.event_message_id,S1.project_id,''''\SSISDB\''''+ S3.name +''''\Projets\'''' + S1.[name] + ''''\Packages\'''' + S2.[name] AS Chemin_Projet
,''''\SSISDB\''''+ S3.name +''''\'''' + S1.[name] + ''''\'''' + S2.[name] AS Chemin_Package,
S4.[execution_id],
S4.[status] AS status_package,
[status_desc_package]= CASE S4.[status] WHEN 1 THEN ''''Created'''' 
WHEN 2 THEN ''''Running''''
WHEN 3 THEN ''''Canceled''''
WHEN 4 THEN ''''Failed''''
WHEN 5 THEN ''''Pending''''
WHEN 6 THEN ''''Ended Unexpectedly''''
WHEN 7 THEN ''''Succeeded''''
WHEN 8 THEN ''''Stopping''''
WHEN 9 THEN ''''Completed ''''
 
ELSE ''''Inconnu''''
END'
 
SELECT @Requete =  @Requete + N',CONVERT (datetime, S4.[start_time]) AS start_time_package,
CONVERT (datetime, S4.[end_time]) AS end_time_package,
/* CONVERT(FLOAT,DATEDIFF(millisecond, S4.[start_time], ISNULL(S4.[end_time], SYSDATETIMEOFFSET())))/1000 AS duration_package, */
S4.[machine_name],
S5.event_name,CONVERT(DATETIME,S5.message_time) AS message_time,
CASE WHEN LEN(S5.[message]) <= 4096 THEN S5.[message] ELSE LEFT(S5.[message], 1024) + ''''...'''' END AS [message_erreur_package],
CASE WHEN LEN(S5.[message_source_name]) <= 1024 THEN S5.[message_source_name] ELSE LEFT(S5.[message_source_name], 1024) + ''''...'''' END AS [message_source_name],
CASE WHEN LEN(S5.[subcomponent_name]) <= 1024 THEN S5.[subcomponent_name] ELSE LEFT(S5.[subcomponent_name], 1024) + ''''...'''' END AS [subcomponent_name]
,CASE WHEN LEN(S5.[execution_path]) <= 1024 THEN S5.[execution_path] ELSE LEFT(S5.[execution_path], 1024) + ''''...'''' END AS [execution_path]
FROM [SSISDB].[internal].[projects] S1
JOIN [SSISDB].[internal].[packages] S2 ON S2.project_id=S1.project_id
JOIN [SSISDB].[internal].[folders] S3 ON S3.folder_id=S1.folder_id'
 
SELECT @Requete =  @Requete + N' JOIN [SSISDB].[catalog].[executions] S4 ON S4.folder_name=S3.name AND S4.project_name=S1.name AND S4.package_name=S2.name
JOIN [SSISDB].[catalog].[event_messages] S5 ON S5.operation_id=S4.execution_id
LEFT JOIN [SSISDB].[catalog].[extended_operation_info] S6 ON S6.info_id=S5.extended_info_id
WHERE S4.[start_time] >=''''' + @Date_DebutCast + ''''''
+ N') T5 ON T2.command LIKE ''''%'''' + Chemin_Package + ''''%'''' COLLATE French_CI_AS 
AND T5.start_time_package >= CASE T3.run_date WHEN 0 THEN NULL ELSE
CONVERT(datetime, 
STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' + 
STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120) END
AND T5.end_time_package <= DATEADD(SECOND,T3.run_duration,CASE T3.run_date WHEN 0 THEN NULL ELSE
CONVERT(datetime, 
STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' + 
STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120) END)'
+ N' CROSS APPLY ('
+ N'SELECT TOP 1 Command AS CommandBackup
FROM [msdb].[dbo].[sysjobsteps]
WHERE command LIKE ''''%%_BACKUP%'''''
+ N' ) T6'
+ ' WHERE T1.name NOT LIKE ''''%cyclique%'''' AND T4.run_status<>1 AND T3.run_status<>1 /*AND CONVERT(datetime, 
STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' + 
STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>=''''' + @Date_DebutCast + '''''*/'
 
END 
ELSE
BEGIN
IF NOT EXISTS (SELECT Valeur FROM #Objets WHERE Type_Val='DATABASE')
BEGIN
SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN N'SELECT * FROM OPENQUERY(' + QUOTENAME(@Serveur_Lie) + ',''' ELSE '' END
 
SELECT @Requete = @Requete + N'SELECT DISTINCT ''''' + @Serveur_Lie + ''''' AS Serveur_Lie,''''' + @ServerName + ''''' AS Nom_Serveur,T1.[name] AS Job_Name,T3.instance_id AS instance_id_job,T3.run_date AS run_date_job
,REPLICATE(''''0'''', 6 - LEN(T3.run_time)) 
+ CAST(T3.run_time AS CHAR(6)) AS run_time_job
,CASE T3.run_date WHEN 0 THEN NULL ELSE
CONVERT(datetime, 
STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' + 
STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120) END date_heure_deb_exec_job
,REPLICATE(''''0'''',6-LEN(CAST(T3.run_duration AS VARCHAR(8)))) + CAST(T3.run_duration  AS VARCHAR(8)) AS run_duration_job,T3.run_status AS run_status_job 
,run_status_desc_job = CASE T3.run_status WHEN 1 THEN ''''Succeeded'''' ELSE  ''''Failed'''' END,T3.[message] AS message_erreur_job
,T2.step_name,T2.command,T4.instance_id AS instance_id_step_job,T4.run_date AS run_date_step_job
,REPLICATE(''''0'''', 6 - LEN(T4.run_time)) 
+ CAST(T4.run_time AS CHAR(6)) AS run_time_step_job
,CASE T4.run_date WHEN 0 THEN NULL ELSE
CONVERT(datetime, 
STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' + 
STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120) END date_heure_deb_exec_step_job
,REPLICATE(''''0'''',6-LEN(CAST(T4.run_duration AS VARCHAR(8)))) + CAST(T4.run_duration  AS VARCHAR(8)) AS run_duration_step_job,T4.run_status AS run_status_step_job 
,run_status_desc_step_job = CASE T4.run_status WHEN 1 THEN ''''Succeeded'''' ELSE  ''''Failed'''' END,T4.[message] AS message_erreur_step_job
,NULL AS Chemin_Package,NULL AS Chemin_Projet,NULL AS execution_id,NULL AS event_message_id,NULL AS status_package,NULL AS status_desc_package
,NULL AS start_time_package,NULL AS end_time_package,NULL AS machine_name
,NULL AS event_name,NULL AS message_time
,NULL AS [message_erreur_package],NULL AS [message_source_name],NULL AS [subcomponent_name],NULL AS [execution_path]
,T5.CommandBackup'
SELECT @Requete = @Requete + N' FROM msdb.dbo.sysjobs T1
JOIN msdb.dbo.[sysjobsteps] T2 ON T2.job_id=T1.job_id
JOIN msdb.dbo.sysjobhistory T3 ON T3.job_id=T1.job_id AND T3.step_id=0'
 
+ ' AND CONVERT(datetime, 
STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' + 
STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>=''''' + @Date_DebutCast + ''''''
 
+ N' JOIN msdb.dbo.sysjobhistory T4 ON T4.job_id=T1.job_id AND T4.step_id=T2.step_id'
+ ' AND CONVERT(datetime, 
STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' + 
STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>=''''' + @Date_DebutCast + ''''''
 
+ N' AND CONVERT(datetime, 
STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' + 
STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>='
 
+ N'CONVERT(datetime, 
STUFF(STUFF(CAST(T3.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' + 
STUFF(STUFF(SUBSTRING(CAST(1000000 + T3.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)' 
 
+ N' CROSS APPLY ('
+ N'SELECT TOP 1 Command AS CommandBackup
FROM [msdb].[dbo].[sysjobsteps]
WHERE command LIKE ''''%LOG_BACKUP%'''' OR command LIKE ''''%%_BACKUP%'''''
+ N' ) T5'
+ ' WHERE T1.name NOT LIKE ''''%cyclique%'''' AND T4.run_status<>1 AND T3.run_status<>1 AND CONVERT(datetime, 
STUFF(STUFF(CAST(T4.run_date as nchar(8)), 7, 0, ''''-''''), 5, 0, ''''-'''') + N'''' '''' + 
STUFF(STUFF(SUBSTRING(CAST(1000000 + T4.run_time as nchar(7)), 2, 6), 5, 0, '''':''''), 3, 0, '''':''''), 120)>=''''' + @Date_DebutCast + ''''''
END
END
SELECT @Requete = CASE WHEN @LocalHot<>@Serveur_Lie THEN @Requete + N''')' ELSE REPLACE(@Requete,'''''','''') END
SELECT @Date_Debut_Requete = GETDATE()
 
 
INSERT INTO #ResultatTemp (Serveur_Lie,Nom_Serveur,Job_Name,instance_id_job,run_date_job,run_time_job,date_heure_deb_exec_job
,run_duration_job,run_status_job,run_status_desc_job,message_erreur_job
,step_name,command,instance_id_step_job,run_date_step_job,run_time_step_job,date_heure_deb_exec_step_job
,run_duration_step_job,run_status_step_job,run_status_desc_step_job,message_erreur_step_job,Chemin_Package,Chemin_Projet,execution_id,event_message_id,status_package,status_desc_package
,start_time_package,end_time_package,machine_name,event_name,message_time,message_erreur_package,message_source_name,subcomponent_name,execution_path,CommandBackup)
EXECUTE sp_executesql @Requete
 
SELECT @Date_Fin_Requete = GETDATE()
 
UPDATE #ResultatTemp
SET
Date_Debut_Requete   = @Date_Debut_Requete
,Date_Fin_Requete     = @Date_Fin_Requete
,Duree_Requete_Second = DATEDIFF(second,@Date_Debut_Requete,@Date_Fin_Requete)
,date_heure_fin_exec_job = DATEADD(SECOND,(3600 * SUBSTRING(run_duration_job,1,2)) + (SUBSTRING(run_duration_job,3,2) * 60) + SUBSTRING(run_duration_job,5,2),date_heure_deb_exec_job)
,date_heure_fin_exec_step_job = DATEADD(SECOND,(3600 * SUBSTRING(run_duration_step_job,1,2)) + (SUBSTRING(run_duration_step_job,3,2) * 60) + SUBSTRING(run_duration_step_job,5,2),date_heure_deb_exec_step_job)
WHERE Serveur_Lie=@Serveur_Lie
 
END
 
END TRY
BEGIN CATCH
INSERT INTO #ResultatTemp (Serveur_Lie,Job_Name,date_heure_deb_exec_job,message_erreur_exec,echec_collect) VALUES (@Serveur_Lie,'Ma Requete',GETDATE(), ERROR_MESSAGE(),1)
END CATCH
SELECT  @Num_Lig =   @Num_Lig + 1
 
END
 
IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[HistoJob]'))
BEGIN
DROP TABLE [tempdb]..[HistoJob]
END
 
INSERT INTO #ListeJob (Serveur_Lie,Job_Name,date_heure_deb_exec_job)
SELECT Serveur_Lie,Job_Name,MAX(date_heure_deb_exec_job) AS date_heure_deb_exec_job  FROM #ResultatTemp
GROUP BY Serveur_Lie,Job_Name
 
SELECT @Num_Lig=1,@Nbre_Lig=COUNT(Serveur_Lie) FROM #ListeJob
 
WHILE @Num_Lig <= @Nbre_Lig
BEGIN
 
INSERT INTO #Resultat (Num_Lig,Serveur_Lie,Nom_Serveur,Job_Name,instance_id_job,run_date_job,run_time_job,date_heure_deb_exec_job,date_heure_fin_exec_job
,run_duration_job,run_status_job,run_status_desc_job,message_erreur_job
,step_name,command,instance_id_step_job,run_date_step_job,run_time_step_job,date_heure_deb_exec_step_job,date_heure_fin_exec_step_job
,run_duration_step_job,run_status_step_job,run_status_desc_step_job,message_erreur_step_job,Chemin_Package,Chemin_Projet,execution_id,event_message_id,status_package,status_desc_package
,start_time_package,end_time_package,machine_name,event_name,message_time,message_erreur_package,message_source_name,subcomponent_name,execution_path,CommandBackup,Date_Debut_Requete
,Date_Fin_Requete,Duree_Requete_Second,echec_collect,message_erreur_exec)
SELECT TOP 10 T1.Num_Lig,T1.Serveur_Lie,Nom_Serveur,T1.Job_Name,instance_id_job,run_date_job,run_time_job,T1.date_heure_deb_exec_job,date_heure_fin_exec_job
,run_duration_job,run_status_job,run_status_desc_job,message_erreur_job
,step_name,command,instance_id_step_job,run_date_step_job,run_time_step_job,date_heure_deb_exec_step_job,date_heure_fin_exec_step_job
,run_duration_step_job,run_status_step_job,run_status_desc_step_job,message_erreur_step_job,Chemin_Package,Chemin_Projet,execution_id,event_message_id,status_package,status_desc_package
,start_time_package,end_time_package,machine_name,event_name,message_time,message_erreur_package,message_source_name,subcomponent_name,execution_path,CommandBackup,Date_Debut_Requete
,Date_Fin_Requete,Duree_Requete_Second,echec_collect,message_erreur_exec
FROM #ResultatTemp T1
JOIN #ListeJob T2 ON T2.Serveur_Lie=T1.Serveur_Lie AND T2.Job_Name=T1.Job_Name AND T2.date_heure_deb_exec_job=T1.date_heure_deb_exec_job AND T2.Num_Lig=@Num_Lig
WHERE (ISNULL(T1.event_name,'OnError')='OnError' OR ISNULL(T1.event_name,'OnWarning')='OnWarning')
 
 
SELECT @Num_Lig =  @Num_Lig + 1 
END
 
 
UPDATE #Resultat
SET
Environnement= CASE WHEN CommandBackup LIKE '%SAUVEGARDE_PRODUCTION%' THEN 'PRODUCTION'
WHEN CommandBackup LIKE '%SAUVEGARDE_HOMOLOGATION%' THEN 'HOMOLOGATION'
WHEN CommandBackup LIKE '%SAUVEGARDE_DEVELOPPEMENT%' THEN 'DEVELOPPEMENT'
ELSE 'INCONNU'
END
,destinataire_mail = CASE WHEN Job_Name LIKE '%application_1%' THEN 'Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.'
WHEN Job_Name LIKE '%application_2%' THEN 'Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.'
WHEN Job_Name LIKE '%application_3%' THEN 'Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.'
ELSE 'Destinataire Mail Inconnu'
END
WHERE Environnement IS NULL 
 
UPDATE #Resultat
SET
Environnement= CASE WHEN Serveur_Lie LIKE '%PRD%' THEN 'PRODUCTION'
WHEN Serveur_Lie LIKE '%HOM%' THEN 'HOMOLOGATION'
WHEN Serveur_Lie LIKE '%DEV%' THEN 'DEVELOPPEMENT'
ELSE 'INCONNU'
END
WHERE Environnement IS NULL
 
UPDATE T1
SET
message_erreur_synthese = T2.message_erreur_synthese
FROM #Resultat T1
JOIN (
SELECT environnement,Serveur_Lie,Job_Name,CAST(ROW_NUMBER() OVER(ORDER BY environnement,Serveur_Lie,Job_Name ASC) AS VARCHAR(3)) + ' ) ' + 
environnement + ', sur ' + Serveur_Lie + ' échec du job #' + Job_Name + '# au niveau de l''étape #' + step_name + '#' AS message_erreur_synthese
FROM (
SELECT distinct environnement,Serveur_Lie,Job_Name,step_name,run_status_step_job
FROM #Resultat
WHERE run_status_step_job<>1 AND environnement<>'INCONNU'
) R ) T2 ON T2.Environnement=T1.Environnement AND T2.Serveur_Lie=T1.Serveur_Lie AND T2.Job_Name=T1.Job_Name
 
SELECT Num_Lig,Environnement,Serveur_Lie,Nom_Serveur,Job_Name,instance_id_job,run_date_job,run_time_job,date_heure_deb_exec_job,date_heure_fin_exec_job
,CASE LEN(run_duration_job) WHEN 6 THEN STUFF(STUFF(run_duration_job,3,0,':'),6,0,':') ELSE run_duration_job END AS run_duration_job
,run_status_job,run_status_desc_job,message_erreur_job
,step_name,command,instance_id_step_job,run_date_step_job,run_time_step_job,date_heure_deb_exec_step_job,date_heure_fin_exec_step_job
,CASE LEN(run_duration_step_job) WHEN 6 THEN STUFF(STUFF(run_duration_step_job,3,0,':'),6,0,':') ELSE run_duration_step_job END AS run_duration_step_job
,run_status_step_job,run_status_desc_step_job,message_erreur_step_job,Chemin_Package,Chemin_Projet,execution_id,event_message_id,status_package,status_desc_package
,start_time_package,end_time_package,machine_name,event_name,message_time,message_erreur_package,message_source_name,subcomponent_name,execution_path,CommandBackup
,destinataire_mail, Date_Debut_Requete,Date_Fin_Requete,Duree_Requete_Second,message_erreur_synthese,message_erreur_exec
INTO [tempdb]..HistoJob
FROM #Resultat
WHERE date_heure_deb_exec_job>=@Date_Debut AND ((status_package=4 AND ISNULL(event_name,'OnError')='OnError')
OR (status_package=6 AND ISNULL(event_name,'OnWarning')='OnWarning')
OR run_status_job<>1
OR ISNULL(echec_collect,0)=1)
ORDER BY ROW_NUMBER() OVER( ORDER BY [message_time]) DESC
 
--DROP TABLE #Serveurs_LiesTemp
--DROP TABLE #Serveurs_Lies
--DROP TABLE #Objets
--DROP TABLE #Resultat
END
ELSE
BEGIN
SELECT Num_Lig,Environnement,Serveur_Lie,Nom_Serveur,Job_Name,instance_id_job,run_date_job,run_time_job,date_heure_deb_exec_job,date_heure_fin_exec_job,run_duration_job
,run_status_job,run_status_desc_job,message_erreur_job
,step_name,command,instance_id_step_job,run_date_step_job,run_time_step_job,date_heure_deb_exec_step_job,date_heure_fin_exec_step_job,run_duration_step_job
,run_status_step_job,run_status_desc_step_job
/*Supprimer de l'affichage le compte AD avec lequel a été lancée l'étape du job*/
,message_erreur_step_job = CASE WHEN message_erreur_step_job LIKE '%MEKTABA-INFO\%' THEN 
SUBSTRING(message_erreur_step_job,CHARINDEX('MEKTABA-INFO\',message_erreur_step_job,1) + 28,LEN(message_erreur_step_job)) 
WHEN message_erreur_step_job LIKE '%MEKTABA-FR\%' THEN 
SUBSTRING(message_erreur_step_job,CHARINDEX('MEKTABA-FR\',message_erreur_step_job,1) + 28,LEN(message_erreur_step_job))
ELSE message_erreur_step_job END 
 
,Chemin_Package,Chemin_Projet,execution_id,event_message_id,status_package,status_desc_package
,start_time_package,end_time_package,machine_name,event_name,message_time,message_erreur_package,message_source_name,subcomponent_name,execution_path,CommandBackup 
,destinataire_mail, Date_Debut_Requete,Date_Fin_Requete,Duree_Requete_Second,message_erreur_synthese,message_erreur_exec
FROM [tempdb]..HistoJob
WHERE  (ISNULL(event_name,'OnError')='OnError' OR ISNULL(event_name,'OnWarning')='OnWarning')
AND Serveur_Lie<>'ServeurLie_1'
AND Job_Name IS NOT NULL
AND Job_Name NOT LIKE '%MonJob_1%'
AND step_name<>'Etape Test'
AND ISNULL(destinataire_mail,'')=ISNULL(@destinataire_mail,'')
ORDER BY ROW_NUMBER() OVER( ORDER BY [message_time]) DESC
END
 
END
GO