Script:

/****************The Lytic Grouyp, copyright, 2021*****************/

/****************Published March 17, 2021 *****************/

/*********from http://www.lyticgroup.com by Edward Heraux*****************/


/************************************************************************************************

Run this script all the way through to create a user-defined function (ufnServerInfo), 

and a stored procedure (uspJobsSummary) to feed the report included with this script 

in the .zip file you downloaded (JobsSummary.rdl or JobsSummary2005/2008).

 *******************************************************************************************/



/************************************************************************************************

Return some basic information about your SQL Server 

and the edition & service pack you're running.

Used in the header of the included reports.

 *******************************************************************************************/

 

 

IF OBJECT_ID (N'dbo.ufnServerInfo', N'IF') IS NOT NULL

    DROP FUNCTION dbo.ufnServerInfo

GO


CREATE FUNCTION dbo.ufnServerInfo()

RETURNS TABLE

AS

RETURN

--Grab server name and version info

SELECT SERVERPROPERTY('servername') AS ServerName

, @@Version as SQLServerVersion

, CONVERT(VARCHAR(100), serverproperty('Edition')) AS Edition

, SERVERPROPERTY('productversion')AS Version


, SERVERPROPERTY ('productlevel') AS ProductLevel

,CONVERT(VARCHAR(100), SERVERPROPERTY ('LicenseType')) AS LicenseType


GO



/************************************************************************************************

Pass any 2 date parameters to this stored procedure and it will produce a list of 

all essential info about all jobs, on all your databases, that ran in between those dates.

 *******************************************************************************************/


IF OBJECT_ID (N'dbo.uspJobSummary', N'IF') IS NOT NULL

    DROP PROC dbo.uspJobSummary

GO

CREATE PROC [dbo].[uspJobSummary] @StartDate datetime=NULL

, @EndDate datetime = NULL --getdate

AS



SET NOCOUNT ON

--Setting the default @StartDate to 1/1/1900

SET @StartDate=ISNULL(@StartDate,'19000101 12:00:00 AM' )


--Setting the default @EndDate to the end of the day, either on the date passed or today if there was none

SET @EndDate= CAST(CAST(@EndDate  AS char(12))+ ' 23:59:50.999' AS datetime)

SET @EndDate=ISNULL(@EndDate,CAST(CAST(getdate() AS char(12)) + ' 23:59:59.999' AS datetime))


BEGIN TRY

--Create a table variable  to hold the data generated by sp_help_jobhistory


declare @JobHistory table(

    instance_id int null, 

    job_id uniqueidentifier null, 

    job_name sysname null, 

    step_id int null ,

    step_name sysname null, 

    sql_message_id int null, 

    sql_severity int null, 

    message nvarchar(4000) null, 

    run_status int null, 

    run_date int null, 

    run_time int null, 

    run_duration int null, 

    operator_emailed sysname null, 

    operator_netsent sysname null, 

    operator_paged sysname null, 

    retries_attempted int null, 

    server sysname null,

     run_duration_formatted  AS RIGHT('000000' + CONVERT(varchar(6), run_duration), 6),

     run_time_formatted AS RIGHT('000000' + CONVERT(varchar(6), run_time), 6), 

     run_date_formatted AS LEFT(CAST(run_date AS varchar(10)),4) + '-'

+ SUBSTRING(CAST(run_date AS varchar(10)),5,2) + '-'

+ RIGHT(CAST(run_date AS varchar(10)),2) 

)


--Populate the table variable with job history data

insert into @JobHistory 

exec msdb.dbo.sp_help_jobhistory @mode='Full'


--Show us the job history within the passed parameters

SELECT H.job_name AS JobName, S.database_name  AS DatabaseName 

, CASE H.run_status      

WHEN 0 THEN 'Failed'

WHEN 1 THEN 'Success'  

WHEN 3 THEN 'Cancelled'  

WHEN 5 THEN 'Unknown'  

ELSE 'Other'  

END AS RunStatus

,LEFT(H.run_duration_formatted,2) + ':' + SUBSTRING(H.run_duration_formatted,3,2)+':' + Right(H.run_duration_formatted,2)  AS RunDuration

,H.run_date_formatted AS RunDate, H.run_time_formatted AS RunTime

--concatenate two of the computed columns from the Table variable into a proper run date & time

,CONVERT(datetime,H.run_date_formatted + ' ' + LEFT(H.run_time_formatted,2) + ':' + SUBSTRING(H.run_time_formatted,3,2)+':' + RIGHT(H.run_time_formatted,2)) AS RunDateTime


FROM @JobHistory H

INNER JOIN (SELECT job_id, MAX(database_name) AS database_Name

FROM msdb.dbo.sysjobsteps 

GROUP BY job_id) S

ON H.job_id=S.job_id

WHERE H.step_id=0 

AND CONVERT(datetime,H.run_date_formatted + ' ' + LEFT(H.run_time_formatted,2) + ':' + SUBSTRING(H.run_time_formatted,3,2)+':' + RIGHT(H.run_time_formatted,2)) 

BETWEEN @StartDate and CAST(@EndDate AS char(12)) + ' 23:59:59.999'


ORDER BY H.run_date_formatted desc


END TRY

BEGIN CATCH

--We're going to refer to these variables in a RAISERROR command

DECLARE @ErrorMessage NVARCHAR(4000)

DECLARE @ErrorSeverity INT

DECLARE @ErrorState INT


SELECT @ErrorMessage = 'Error number ' + CAST(ERROR_NUMBER() AS varchar(10)) +': ' + ERROR_MESSAGE(),

  @ErrorSeverity = ERROR_SEVERITY(),

  @ErrorState = ERROR_STATE()

           

RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState) 

END CATCH


GO