First of all, I prepared the data in SQL, this is the statement:
--===============================================================
--===============================================================
--===============================================================
--===============================================================
USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_DBAWEB_SQLJob]
@StartDateRef date = null,
@EndDateRef date = null,
@IsOnlyLastMinute bit = 1 -- Yes
As
/*****************************************************************************************************************
Name: dbo.sp_DBAWEB_SQLJob
View all the SQL Job status.
*****************************************************************************************************************
Change History
*****************************************************************************************************************
Date: Author: Description:
----- ------- -------------------
10 Oct 2018 Leandro Buffone Created Initial Version
*****************************************************************************************************************
Usage:
set statistics io on
set statistics time on
Exec sp_DBAWEB_SQLJob @IsOnlyLastMinute = 1
*****************************************************************************************************************/
Declare @StartDateRefID int = dbo.fnDateKey(IsNull(@StartDateRef, Getdate())),
@EndDateRefID int = dbo.fnDateKey(IsNull(@EndDateRef,Getdate())),
@ServerName varchar(100) = @@ServerName,
@PerformanceIndicatorCategoryName varchar(100) = 'SQL Job',
@PerformanceIndicatorName varchar(100) = 'SQL Job',
@IsOperational bit = (Case When (datepart(hour, getdate()) Between 8 and 18) Then 1 Else 0 End);
With BaseSQLJob
As
(
Select
ServerName = @@SERVERNAME
, JobName = j.name
, JobDescription = j.description
, JobLastRunDate = msdb.dbo.agent_datetime(run_date,run_time)
, JobStatus = Case h.run_status
When 0 Then 'Failed'
When 1 Then 'Successful'
When 3 Then 'Cancelled'
When 4 Then 'In Progress'
End
,PerformanceIndicatorValue = Case h.run_status
When 0 Then 1 --'Failed'
When 1 Then 0 --'Successful'
When 3 Then 0 --'Cancelled'
When 4 Then 0 --'In Progress'
End
,[OrderOfRun] = Rank() Over(Partition By j.job_id Order By h.run_date Desc,h.run_time Desc, h.run_status Desc)
,JobDateID = dbo.fnDateKey(getdate())
,ModifiedDateOn = Getdate()
,CreatedDateOn = Getdate()
From
msdb.dbo.sysjobhistory h
Left join msdb.dbo.sysjobs j On j.job_id = h.job_id
Where h.step_id=0 --only look @ Job Outcome step
)
Merge dbo.ServerSQLJob d
Using (
Select ServerName = @ServerName,
PerformanceIndicatorCategoryName = @PerformanceIndicatorCategoryName,
PerformanceIndicatorName = @PerformanceIndicatorName,
PerformanceAVGIndicatorValue = 0,
PerformanceIndicatorValue,
IsOperational = @IsOperational,
JobName,
JobDescription,
JobLastRunDate,
JobStatus,
JobDateID,
ModifiedDateOn,
CreatedDateOn
From BaseSQLJob
Where
(OrderOfRun = @IsOnlyLastMinute)
) s on s.JobName = d.JobName
when matched
then
update
set
JobDescription = s.JobDescription,
JobLastRunDate = s.JobLastRunDate,
PerformanceIndicatorValue = s.PerformanceIndicatorValue,
JobStatus = s.JobStatus,
JobDateID = s.JobDateID,
ModifiedDateOn = s.ModifiedDateOn,
CreatedDateOn = s.CreatedDateOn
when not matched then
Insert ( ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName,PerformanceAVGIndicatorValue,PerformanceIndicatorValue, IsOperational,JobName,JobDescription,JobLastRunDate,JobStatus,JobDateID,ModifiedDateOn,CreatedDateOn)
values ( ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName,PerformanceAVGIndicatorValue,PerformanceIndicatorValue, IsOperational,JobName,JobDescription,JobLastRunDate,JobStatus,JobDateID,ModifiedDateOn,CreatedDateOn)
when not matched by source then
delete;
--===============================================================
--===============================================================
--===============================================================
--===============================================================