To Create Daily Job Monitor to Check Status of
automation #agent-job#automation#health-check#monitoring
USE [MASTER]
GO
/****** OBJECT: STOREDPROCEDURE [DBO].[JOBSTATUS_REPORT] SCRIPT DATE: 5/4/2023 5:21:11 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [DBO].[JOBSTATUS_REPORT]
AS BEGIN
SET NOCOUNT ON
SELECT
--[SJOB].[JOB_ID] AS [JOBID],
[SJOB].[NAME] AS [JOBNAME]
, CASE
WHEN [SJOBH].[RUN_DATE] IS NULL OR [SJOBH].[RUN_TIME] IS NULL THEN NULL
ELSE CAST(
CAST([SJOBH].[RUN_DATE] AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST([SJOBH].[RUN_TIME] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME)
END AS [LASTRUNDATETIME]
, CASE [SJOBH].[RUN_STATUS]
WHEN 0 THEN 'FAILED'
WHEN 1 THEN 'SUCCEEDED'
WHEN 2 THEN 'RETRY'
WHEN 3 THEN 'CANCELLED'
WHEN 4 THEN 'RUNNING' -- IN PROGRESS
END AS [LASTRUNSTATUS]
, STUFF(
STUFF(RIGHT('000000' + CAST([SJOBH].[RUN_DURATION] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS [LASTRUNDURATION],-- (HH:MM:SS)],
-- [SJOBH].[MESSAGE] AS [LASTRUNSTATUSMESSAGE],
CASE WHEN ENABLED = 1 THEN 'ENABLED' ELSE 'DISABLED' END AS [JOBSTATUS]
, CASE [SJOBSCH].[NEXTRUNDATE]
WHEN 0 THEN NULL
ELSE CAST(
CAST([SJOBSCH].[NEXTRUNDATE] AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST([SJOBSCH].[NEXTRUNTIME] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME)
END AS [NEXTRUNDATETIME]
--DROP TABLE #AGENT_JOBSTAUS
INTO #AGENT_JOBSTAUS
FROM
[MSDB].[DBO].[SYSJOBS] AS [SJOB]
LEFT JOIN (
SELECT
[JOB_ID]
, MIN([NEXT_RUN_DATE]) AS [NEXTRUNDATE]
, MIN([NEXT_RUN_TIME]) AS [NEXTRUNTIME]
FROM [MSDB].[DBO].[SYSJOBSCHEDULES]
GROUP BY [JOB_ID]
) AS [SJOBSCH]
ON [SJOB].[JOB_ID] = [SJOBSCH].[JOB_ID]
LEFT JOIN (
SELECT [JOB_ID]
, [RUN_DATE]
, [RUN_TIME]
, [RUN_STATUS]
, [RUN_DURATION]
, [MESSAGE]
, ROW_NUMBER() OVER (
PARTITION BY [JOB_ID]
ORDER BY [RUN_DATE] DESC, [RUN_TIME] DESC
) AS ROWNUMBER
FROM [MSDB].[DBO].[SYSJOBHISTORY]
WHERE [STEP_ID] = 0
) AS [SJOBH]
ON [SJOB].[JOB_ID] = [SJOBH].[JOB_ID]
AND [SJOBH].[ROWNUMBER] = 1
ORDER BY [JOBNAME]
DECLARE @HTML NVARCHAR(MAX)
SET @HTML = ''
SET @HTML = '<HTML><BODY>
<TABLE BORDER = 2 CELLPADDING =5>
<TR BGCOLOR = PINK>
<TH NOWRAP><FONT SIZE=3 FACE="ARIAL NARROW">JOBNAME </FONT></TH>
<TH NOWRAP><FONT SIZE=3 FACE="ARIAL NARROW">LASTRUNDATETIME</FONT></TH>
<TH NOWRAP><FONT SIZE=3 FACE="ARIAL NARROW">LASTRUNSTATUS</FONT></TH>
<TH NOWRAP><FONT SIZE=3 FACE="ARIAL NARROW">LASTRUNDURATION</FONT></TH>
<TH NOWRAP><FONT SIZE=3 FACE="ARIAL NARROW">JOBSTATUS</FONT></TH>
<TH NOWRAP><FONT SIZE=3 FACE="ARIAL NARROW">NEXTRUNDATETIME</FONT></TH>
</TR> '
--+ @HTML
SELECT @HTML = @HTML +
'<TR><TD>' + LTRIM(RTRIM(CONVERT(NVARCHAR(256),JOBNAME ))) + '</FONT></TD>' +
'<TD >' + ISNULL(LTRIM(RTRIM(CONVERT(NVARCHAR(256),LASTRUNDATETIME ))),'NEVER') + '</FONT></TD>'+
CASE WHEN LTRIM(RTRIM(CONVERT(NVARCHAR(256),LASTRUNSTATUS)))='FAILED' THEN '<TD VALIGN="TOP" BGCOLOR = RED><B>'+'FAILED'+' </B></TD>' ELSE
'<TD VALIGN="TOP" BGCOLOR =LIGHTGREEN ><B><FONT SIZE=2 FACE="CALIBRI">'+ ISNULL(LTRIM(RTRIM(CONVERT(NVARCHAR(256),LASTRUNSTATUS))),'NEVER') + '</FONT></TD>'
END + '<TD>' + ISNULL(LTRIM(RTRIM(CONVERT(NVARCHAR(256),LASTRUNDURATION ))),'NEVER') + '</FONT></TD>'+
'<TD>' + LTRIM(RTRIM(CONVERT(NVARCHAR(256),JOBSTATUS ))) + '</FONT></TD>'+
'<TD>' + ISNULL(LTRIM(RTRIM(CONVERT(NVARCHAR(256),NEXTRUNDATETIME ))),'NEVER') + '</FONT></TD></TR>'
FROM #AGENT_JOBSTAUS
WHERE LASTRUNSTATUS = 'FAILED'
AND JOBSTATUS NOT IN ('DISABLED')
ORDER BY JOBSTATUS ASC ,LASTRUNDATETIME
SELECT @HTML = @HTML +
'<TR><TD>' + LTRIM(RTRIM(CONVERT(NVARCHAR(256),JOBNAME ))) + '</FONT></TD>' +
'<TD >' + ISNULL(LTRIM(RTRIM(CONVERT(NVARCHAR(256),LASTRUNDATETIME ))),'NEVER') + '</FONT></TD>'+
CASE WHEN LTRIM(RTRIM(CONVERT(NVARCHAR(256),LASTRUNSTATUS)))='FAILED' THEN '<TD VALIGN="TOP" BGCOLOR = RED><B>'+'FAILED'+' </B></TD>' ELSE
'<TD VALIGN="TOP" BGCOLOR =LIGHTGREEN ><B><FONT SIZE=2 FACE="CALIBRI">'+ ISNULL(LTRIM(RTRIM(CONVERT(NVARCHAR(256),LASTRUNSTATUS))),'NEVER') + '</FONT></TD>'
END + '<TD>' + ISNULL(LTRIM(RTRIM(CONVERT(NVARCHAR(256),LASTRUNDURATION ))),'NEVER') + '</FONT></TD>'+
'<TD>' + LTRIM(RTRIM(CONVERT(NVARCHAR(256),JOBSTATUS ))) + '</FONT></TD>'+
'<TD>' + ISNULL(LTRIM(RTRIM(CONVERT(NVARCHAR(256),NEXTRUNDATETIME ))),'NEVER') + '</FONT></TD></TR>'
FROM #AGENT_JOBSTAUS
WHERE LASTRUNSTATUS <> 'FAILED' OR LASTRUNSTATUS IS NULL
AND JOBSTATUS NOT IN ('DISABLED')
ORDER BY JOBSTATUS DESC, LASTRUNSTATUS DESC,LASTRUNDATETIME
SELECT @HTML = @HTML + ' </TABLE></BODY></HTML>'
PRINT @HTML
--DECLARE @CC AS NVARCHAR(256)
--SET @CC = MUSICANDR@GMAIL.COM'
IF EXISTS(SELECT TOP 1 * FROM #AGENT_JOBSTAUS)
--SET @CC = 'MUSCICANDR@GMAIL.COM;MUSICNADR@GMAIL.COM'
EXEC MSDB.DBO.SP_SEND_DBMAIL @PROFILE_NAME = 'OUTLOOK'
,@RECIPIENTS = 'MUSICANDR@GMAIL.COM'
,@BODY = @HTML
,@SUBJECT ='DAILY JOB MONITOR REPORT'
,@BODY_FORMAT = 'HTML'
DROP TABLE #AGENT_JOBSTAUS
END
GO