Show Job Run Snapshot

CREATE PROCEDURE dbo.spShowJobRunSnapshot
    @dtStart DATETIME = NULL,
    @dtEnd   DATETIME = NULL
AS
/***********************************************************************************************************
 Purpose:      Show a snapshot of all of the jobs running in a proscribed period of time.
 
 Created Date: 8/1/2012
 Written by:   Monte Kottman

 Proc Name:    spShowJobRunSnapshot

 Owner:        

 Inputs:       dtStart - Starting date/time of the period.
               dtEnd   - Ending date/time of the period.

 Outputs:      recordset.

 Dependencies: none

 Tested on:    SQL Server 2000, 2005, 2008, 2012

 Usage:        Standalone
 
 Example:      EXEC dbo.spShowJobRunSnapshot '9/16/2012', '9/16/2012 23:59'

***********************************************************************************************************/SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
                                                          /***********************
                                                          ** Declare supporting data structures
                                                         ***********************/SET NOCOUNT ON

DECLARE 
   @iReturn     INT,
   @sTask       VARCHAR(2000),
   @idCurrent   UNIQUEIDENTIFIER,
   @iRowCnt     INT,
   @iMaxRows    INT 

DECLARE @Hist TABLE 
  ( 
  iRowNum   INT IDENTITY (1, 1) PRIMARY KEY NOT NULL , 
  idCurrent UNIQUEIDENTIFIER
  ) 

CREATE TABLE #tblRunDate 
   (
   idJob        UNIQUEIDENTIFIER, 
   iInstance    INT, 
   sMessage     VARCHAR(200), 
   iRunDate     INT, 
   iRunTime     INT, 
   iRunDuration INT,
   dtRunStart   DATETIME,
   dtRunEnd     DATETIME
   )

SELECT @iRowCnt = 1 

IF LEN(ISNULL(@dtStart,'')) = 0 
   SET @dtStart = (SELECT SUBSTRING(CONVERT(VARCHAR(8),MIN(run_date)),5,2) + '/' +        
   SUBSTRING(CONVERT(VARCHAR(8),MIN(run_date)),7,2) + '/' +        
   SUBSTRING(CONVERT(VARCHAR(8),MIN(run_date)),1,4) FROM msdb.dbo.sysjobhistory)

IF LEN(ISNULL(@dtEnd,'')) = 0 
   SET @dtEnd = (SELECT SUBSTRING(CONVERT(VARCHAR(8),MAX(run_date)),5,2) + '/' +        
   SUBSTRING(CONVERT(VARCHAR(8),MAX(run_date)),7,2) + '/' +        
   SUBSTRING(CONVERT(VARCHAR(8),MAX(run_date)),1,4) FROM msdb.dbo.sysjobhistory)

INSERT INTO @Hist (idCurrent) 
    SELECT 
       O.job_id
    FROM 
       msdb.dbo.sysjobs AS O
       
SELECT @iMaxRows = COUNT(*) FROM @Hist 

WHILE @iRowCnt <= @iMaxRows 
BEGIN 
    SELECT @idCurrent = idCurrent FROM @Hist WHERE iRowNum = @iRowCnt 

    SET @sTask  = ' INSERT #tblRunDate (idJob, iInstance, sMessage, iRunDate, iRunTime, iRunDuration)'
              + ' SELECT TOP 1000 job_id, instance_id, SUBSTRING(message,1,CHARINDEX(' + '''.'''+ ',message,1)), run_date, run_time, run_duration'
              + ' FROM msdb.dbo.sysjobhistory'
              + ' WHERE step_id = 0 AND CONVERT(VARCHAR(100),job_id) = ''' + CONVERT(VARCHAR(100),@idCurrent) + ''''
              + ' ORDER BY instance_id DESC'

    EXECUTE (@sTask)

    SELECT @iRowCnt = @iRowCnt + 1 
END

UPDATE #tblRunDate 
SET dtRunStart = 
      SUBSTRING(CONVERT(VARCHAR(8),iRunDate),5,2) + '/' +        
      SUBSTRING(CONVERT(VARCHAR(8),iRunDate),7,2) + '/' +        
      SUBSTRING(CONVERT(VARCHAR(8),iRunDate),1,4)+ '  ' + 
      ISNULL(SUBSTRING(CONVERT(VARCHAR(7),iRunTime+1000000),2,2) + ':' +        
      SUBSTRING(CONVERT(VARCHAR(7),iRunTime+1000000),4,2) + ':' +        
      SUBSTRING(CONVERT(VARCHAR(7),iRunTime+1000000),6,2),'')

UPDATE #tblRunDate 
   SET dtRunEnd =  dateadd(ss,CAST(SUBSTRING(CONVERT(VARCHAR(7),iRunDuration+1000000),6,2) AS INT),dtRunStart)
WHERE 
   SUBSTRING(CONVERT(VARCHAR(7),iRunDuration+1000000),6,2) IS NOT NULL

UPDATE #tblRunDate 
   SET dtRunEnd =  dateadd(mi,CAST(SUBSTRING(CONVERT(VARCHAR(7),iRunDuration+1000000),4,2) AS INT),dtRunEnd)
WHERE 
   SUBSTRING(CONVERT(VARCHAR(7),iRunDuration+1000000),4,2) IS NOT NULL

UPDATE #tblRunDate 
   SET dtRunEnd =  dateadd(hh,CAST(SUBSTRING(CONVERT(VARCHAR(7),iRunDuration+1000000),2,2) AS INT),dtRunEnd)
WHERE 
   SUBSTRING(CONVERT(VARCHAR(7),iRunDuration+1000000),2,2) IS NOT NULL

SELECT 
   O.name AS [Name], 
   dtRunStart AS [Start Date], 
   ISNULL(SUBSTRING(CONVERT(VARCHAR(7),T.iRunDuration+1000000),2,2) + ':' +        
      SUBSTRING(CONVERT(VARCHAR(7),T.iRunDuration+1000000),4,2) + ':'+        
      SUBSTRING(CONVERT(VARCHAR(7),T.iRunDuration+1000000),6,2),'') AS [Duration], 
   ISNULL(T.sMessage,'') AS [Status],
   dtrunend AS [End Date]
FROM 
   msdb.dbo.sysjobs AS O
   LEFT JOIN #tblRunDate AS T ON O.Job_id = T.idJob
WHERE 
   (dtRunStart BETWEEN @dtStart AND @dtEnd) OR
   (dtRunEnd BETWEEN @dtStart AND @dtEnd) OR 
   (dtRunStart < @dtStart AND dtRunEnd > @dtEnd) OR
   (dtRunStart < @dtStart AND dtRunEnd BETWEEN @dtStart AND @dtEnd) OR
   (dtRunEnd > @dtEnd AND dtRunStart BETWEEN @dtStart AND @dtEnd)   
ORDER BY 
   dtRunStart,
   O.name,
   T.iInstance DESC

DROP TABLE #tblRunDate