Job History Collection

--TABLE
 USE [DBACollection]
GO

/****** Object:  Table [dbo].[JobHistoryCollection]    Script Date: 11/26/2013 10:30:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[JobHistoryCollection](
    [ServerName] [nvarchar](128) NULL,
    [JobName] [sysname] NULL,
    [Enabled] [tinyint] NULL,
    [CurrentStatus] [varchar](9) NULL,
    [CurrentStepNbr] [int] NULL,
    [LastRunTime] [datetime] NULL,
    [LastRunOutcome] [varchar](11) NULL,
    [LastRunDuration] [int] NULL,
    [CollectionTime] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[JobHistoryCollection] ADD  CONSTRAINT [DF_JobHistoryCollection_CollectionTime]  DEFAULT (getdate()) FOR [CollectionTime]
GO


--PROC
USE [DBACollection]
GO
/*

exec pr_JobHistoryCollection

select * from JobHistoryCollection order by collectiontime desc


select jobname, COUNT(*) as cc, MAX(lastrunduration) mx,Min(lastrunduration) mn,Avg(lastrunduration) av
from JobHistoryCollection
where collectiontime < GETDATE() and collectiontime > DATEADD(dd, -7, GETDATE())
group by jobname
order by mx desc

*/
alter proc pr_JobHistoryCollection
as
begin

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

if(object_id('tempdb..#tmp')>1)
    drop table #tmp

create table #tmp (job_id uniqueidentifier NOT NULL,last_run_date nvarchar (20) NOT NULL,last_run_time nvarchar (20) NOT NULL,next_run_date nvarchar (20) NOT NULL,next_run_time nvarchar (20) NOT NULL, next_run_schedule_id INT NOT NULL,requested_to_run INT NOT NULL,request_source INT NOT NULL,request_source_id sysname COLLATE database_default NULL,    running INT NOT NULL,current_step INT NOT NULL,current_retry_attempt INT NOT NULL,job_state INT NOT NULL);

insert into #tmp  EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'CSODProd\SQLClusterLA4SW1';


UPDATE #tmp SET last_run_time = right ('000000' + last_run_time, 6), next_run_time    = right ('000000' + next_run_time, 6);

--select * from #tmp


insert into JobHistoryCollection
(ServerName,JobName,Enabled,CurrentStatus,CurrentStepNbr,LastRunTime,LastRunOutcome,LastRunDuration)

select ServerName,JobName,Enabled,CurrentStatus,CurrentStepNbr,LastRunTime,LastRunOutcome,LastRunDuration
from
(
SELECT @@servername as ServerName , j.name AS JobName, j.enabled AS Enabled,
Case x.running WHEN 1 THEN 'Running' Else Case h.run_status WHEN 2 THEN 'Inactive' WHEN 4 THEN 'Inactive' Else 'Completed' End End AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE WHEN x.last_run_date > 0 THEN convert (datetime, substring (x.last_run_date, 1, 4)+ '-' + substring (x.last_run_date, 5, 2)+ '-'+ substring (x.last_run_date, 7, 2)+ ' '+ substring (x.last_run_time, 1, 2) + ':' + substring (x.last_run_time, 3, 2)+ ':' + substring (x.last_run_time, 5, 2)+ '.000',121) Else  NULL End AS LastRunTime,
Case h.run_status WHEN 0 THEN 'Fail'WHEN 1 THEN 'Success' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancel' WHEN 4 THEN 'In progress' End AS LastRunOutcome,
CASE WHEN h.run_duration > 0 THEN (h.run_duration / 1000000) * (3600 * 24) + (h.run_duration / 10000 % 100) * 3600 + (h.run_duration / 100 % 100) * 60 + (h.run_duration % 100) Else NULL End AS LastRunDuration ,
h.run_duration
FROM #tmp x
Left Join msdb.dbo.sysjobs j ON x.job_id = j.job_id
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON x.job_id = h.job_id AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time AND h.step_id = 0
where x.running != 1
) a
except
select ServerName,JobName,Enabled,CurrentStatus,CurrentStepNbr,LastRunTime,LastRunOutcome,LastRunDuration
from JobHistoryCollection



end
--JOB
USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'AUDIT - JobHistoryCollection',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=2,
        @notify_level_netsend=2,
        @notify_level_page=2,
        @delete_level=0,
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'AUDIT - JobHistoryCollection', @server_name = @@servername
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'AUDIT - JobHistoryCollection', @step_name=N'exec sp',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_fail_action=2,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'exec sp_JobHistoryCollection',
        @database_name=N'DBACollection',
        @flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'AUDIT - JobHistoryCollection',
        @enabled=1,
        @start_step_id=1,
        @notify_level_eventlog=0,
        @notify_level_email=2,
        @notify_level_netsend=2,
        @notify_level_page=2,
        @delete_level=0,
        @description=N'',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa',
        @notify_email_operator_name=N'',
        @notify_netsend_operator_name=N'',
        @notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'AUDIT - JobHistoryCollection', @name=N'Every 15 min',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=4,
        @freq_subday_interval=15,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20131126,
        @active_end_date=99991231,
        @active_start_time=0,
        @active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO