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