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.
- 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
- 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,'>','>'),'<','<') 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 ENDReport Looks as below
