Send Jobs Status Report using DB Mail as HTML

It is really a herculean task to have a consolidated report for all the jobs status for one or more server. My much time is saved by this report

Steps to Create Report

Configure Database Mail Profile, which will be used to send mail.

  1. Create Table for Job History
    USE [msdb]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[ServerJobHistory]
    (
    [Job_ID] [uniqueidentifier] NOT NULL,
    [Job_Name] [sysname] NOT NULL,
    [Run_Date] [int] NOT NULL,
    [Run_Time] [int] NOT NULL,
    [Run_Duration] [int] NOT NULL,
    [Job_Outcome] [varchar](100) NOT NULL,
    [Step_Outcome] [varchar](250) NOT NULL
    
    )ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    
  2. Create Stored Procedure to pull Job Information from msdb Job Table into above created
    USE [msdb]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[sp_LoadJobHistory]
    AS
    BEGIN
    SET NOCOUNT ON
    
    DECLARE @JobID UNIQUEIDENTIFIER
    DECLARE @JobName SYSNAME
    DECLARE @RunDate INT
    DECLARE @RunTime INT
    DECLARE @RunDuration INT
    DECLARE @RunStatus INT
    DECLARE @FailedSteps INT
    DECLARE @JobOutcome VARCHAR(100)
    DECLARE @StepOutcome VARCHAR(250)
    DECLARE JobHistoryCursor CURSOR
    LOCAL READ_ONLY FOR
    
    SELECT J.job_id,J.[name],JH.[run_date],JH.[run_time],((JH.[run_duration]/10000)*3600)
    +(((JH.[run_duration]/100)-(JH.[run_duration]/10000)* 100)* 60)
    +(JH.[run_duration]%100) [run_duration (sec)],JH.[run_status]
    FROM dbo.sysjobhistory JH JOIN dbo.sysjobs J
    ON J.job_id = JH.job_id WHERE JH.[job_id] IN
    (SELECT [job_id] FROM dbo.sysjobs)
    AND
    CONVERT(VARCHAR(10), JH.[run_date])+CONVERT(VARCHAR(10), JH.[run_time])
    NOT IN
    (SELECT CONVERT(VARCHAR(10), IJH.[Run_Date])+CONVERT(VARCHAR(10), IJH.[Run_Time])
    FROM dbo.ServerJobHistory IJH WHERE IJH.Job_ID = JH.[job_id]
    )
    AND JH.[step_id] = 0
    
    OPEN JobHistoryCursor FETCH
    NEXT FROM JobHistoryCursor INTO @JobID, @JobName, @RunDate, @RunTime, @RunDuration, @RunStatus
    WHILE @@FETCH_STATUS= 0
    BEGIN
    SELECT @FailedSteps =COUNT(*) FROM dbo.sysjobhistory H
    WHERE H.job_id = @JobID AND H.run_date = @RunDate AND H.run_time/100 = @RunTime/100
    AND H.step_id > 0 AND H.run_status = 0
    
    IF (@RunStatus = 0)
    SELECT @JobOutcome ='Failed', @StepOutcome = 'One or more steps failed'
    ELSE
    IF (@RunStatus = 1 AND @FailedSteps > 0)
    SELECT @JobOutcome ='Succeeded (with errors)', @StepOutcome = 'One or more steps failed'
    ELSE
    IF (@RunStatus = 1 AND @FailedSteps = 0)
    SELECT @JobOutcome ='Succeeded', @StepOutcome = 'All steps succeeded'
    ELSE
    IF (@RunStatus = 3)
    SELECT @JobOutcome ='Cancelled by user', @StepOutcome = ''
    ELSE
    SELECT @JobOutcome ='Unknown or Retrying', @StepOutcome = 'Please check job history'
    INSERT INTO dbo.ServerJobHistory (Job_ID, Job_Name, Run_Date, Run_Time, Run_Duration, Job_Outcome, Step_Outcome)
    VALUES (@JobID, @JobName, @RunDate, @RunTime, @RunDuration, @JobOutcome, @StepOutcome)
    FETCH NEXT FROM JobHistoryCursor INTO @JobID, @JobName, @RunDate, @RunTime, @RunDuration, @RunStatus
    END
    CLOSE JobHistoryCursor
    DEALLOCATE JobHistoryCursor
    
    END
    

    Create Procedure to send Status Job in Mail using DB Mail

    USE [msdb]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE PROCEDURE [dbo].[DBA_sp_JobHistoryMailer]
    AS
    BEGIN
    DECLARE @tableHTML NVARCHAR(MAX)
    SET @tableHTML = N'<html><body><Font Face="Verdana" Size=4><b>Server Job History Report</b></Font><p>'
    +
    N'<Font Face="Verdana" Size=2>Report generated on: '
    +
    CONVERT(VARCHAR(11), GETDATE(), 113) + '</Font><br>'
    + N'<Font Face="Verdana" Size=2>This report shows yesterday''s history only.</Font><p>'
    + N'<table border="1"width="100%"><tr bgcolor="Black">'
    + N'<td><Font Face="Verdana" Size=2 Color="White"><b>Job Name</b></Font></td>'
    + N'<td><Font Face="Verdana" Size=2 Color="White"><b>Last Run Date</b></Font></td>'
    + N'<td><Font Face="Verdana" Size=2 Color="White"><b>Last Run Time</b></Font></td>'
    + N'<td><Font Face="Verdana" Size=2 Color="White"><b>Last Run Duration (sec)</b></Font></td>'
    + N'<td><Font Face="Verdana" Size=2 Color="White"><b>Last Run Status</b></Font></td>'
    + CAST ((SELECT td = '<Font Face="Verdana" Size=2 Color="Black">'
    + CONVERT(VARCHAR(250), Job_Name) + '</Font>','',
    td ='<Font Face="Verdana" Size=2 Color="Black">' + CONVERT(VARCHAR(250), Run_Date)
    + '</Font>','', td = '<Font Face="Verdana" Size=2 Color="Black">' + CONVERT(VARCHAR(250), Run_Time)
    + '</Font>','', td = '<Font Face="Verdana" Size=2 Color="Black">' + CONVERT(VARCHAR(250), Run_Duration)
    + '</Font>','', CASE Job_Outcome WHEN 'Succeeded' THEN '<Font Face="Verdana" Size=2 Color="Green">'
    + CONVERT(VARCHAR(250), Job_Outcome) + '</Font>' WHEN 'Failed' THEN '<Font Face="Verdana" Size=2 Color="Red">'
    + CONVERT(VARCHAR(250), Job_Outcome) + '</Font>' ELSE '<Font Face="Verdana" Size=2 Color="Orange">'
    + CONVERT(VARCHAR(250), Job_Outcome) + '</Font>' END as td FROM dbo.ServerJobHistory
    WHERE Run_Date = REPLACE(CONVERT(VARCHAR(10), DATEADD(dd, 0, GETDATE()), 121),'-', '') ORDER BY Job_Name
    FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) ) + N'</table></body></html>'
    SET @tableHtml = REPLACE(REPLACE(@tableHtml,'&gt;','>'),'&lt;','<')
    SELECT @tableHtml
    IF (ISNULL(@tableHtml,'NULL'))!= 'NULL'
    BEGIN
    EXEC msdb.dbo.sp_send_dbmail @profile_name ='DBProfileName',
    @recipients='xyz@xyz.com',
    @subject='Job History Report',
    @body=@tableHTML,
    @body_format='HTML'
    END
    
    END
    

    Report Looks as below

Leave a Reply

Your email address will not be published. Required fields are marked *