/****************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