if exists ( select 1
from dbo.sysobjects
where id = object_id(N'dbo.rpt_ITSQLAgentJobs')
and objectproperty(id, N'IsProcedure') = 1 )
drop procedure dbo.rpt_ITSQLAgentJobs
go
set quoted_identifier on
go
set ansi_nulls on
go
create proc dbo.rpt_ITSQLAgentJobs
(
@From datetime
, @To datetime
)
as
/*
----------------------------------------------------------------------
Version: 1.0
Date: 13/05/2009
----------------------------------------------------------------------
*/
set nocount on
declare @FromInt int
, @ToInt int
-- In sysjobhistory Run Date is an integer in the form YYYYMMDD.
set @FromInt = cast((convert(varchar(8),@From,112)) as int)
set @ToInt = cast((convert(varchar(8),@To,112)) as int)
-- I tried a union but the servers aren't all on the same collation so that doesn't work.
-- The temp table approach gets round that.
----------------------------------------------------------------------
-- server1
----------------------------------------------------------------------
-- Create the temp table from the first server's results.
select sjh.run_date
, sjh.run_time
, sjh.server
, sj.name as job_name
, sjh.step_name
, sjh.message
, sjh.run_status
into #t
from server1.msdb.dbo.sysjobhistory sjh with (nolock)
inner join server1.msdb.dbo.sysjobs_view sj with (nolock) on sj.job_id = sjh.job_id
where sjh.step_id = 0 --(Job outcome)
and sjh.run_date >= @FromInt
and sjh.run_date <= @ToInt
----------------------------------------------------------------------
-- server2
----------------------------------------------------------------------
begin try
insert #t
select sjh.run_date
, sjh.run_time
, sjh.server collate Latin1_General_CI_AS
, sj.name as job_name
, sjh.step_name
, sjh.message
, sjh.run_status
from server2.dbo.sysjobhistory sjh with (nolock)
inner join server2.msdb.dbo.sysjobs_view sj with (nolock) on sj.job_id = sjh.job_id
where sjh.step_id = 0 --(Job outcome)
and sjh.run_date >= @FromInt
and sjh.run_date <= @ToInt
end try
begin catch
insert #t
(run_date
, run_time
, [server]
, job_name
, step_name
, [message]
, run_status)
select 99999999 --sjh.run_date
, 0 --sjh.run_time
, 'server2' --sjh.server collate Latin1_General_CI_AS
, '' --sj.name as job_name
, '' --sjh.step_name
, error_message() --sjh.message
, 0 -- sjh.run_status
end catch
-- Repeat the server2 bit for every other server you want to monitor
--Return results
select *
, case run_status
when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
when 4 then 'In progress'
else 'Unknown (' + cast(run_status as varchar(3)) + ')'
end as status_desc
from #t
order by run_date desc
, run_time desc
, [server]
go