Find count of successful jobs with in range SP
General Description:
The following procedure called usp_GetCountJobSuccessInRange gets a job name , number of days and a run status parameter that corresponds to the following run status values :
|
0
|
Failed
|
|
1
|
Succeeded
|
|
2
|
Retry (step only)
|
|
3
|
Canceled
|
|
4
|
In-progress message
|
|
5
|
Unknown
|
The procedure checks the count of jobs that fall in the run status category and that where executed within the days range
(until the current day from current days - number days given)
The procedure uses the msdb.dbo.sp_help_jobhistory system stored procedure that Provides information about the jobs for servers in the multiserver administration domain.
Here is the procedure source code:
Create proc usp_GetCountJobSuccessInRange (@jobName sysname, @dys int, @runStat smallint)
as
begin
-- create a temporary table to hold the results
Create table #tempHist
(jobId uniqueidentifier,
jobname sysname,
runstat int,
rundate int,
runtime int,
duration int,
operatormailed bit,
operatornetsent bit,
operatornetpage bit,
retries int,
srv sysname)
-- exec job history procedure and fill temp table
insert #tempHist
exec msdb.dbo.sp_help_jobhistory @job_name = @jobName , @run_status = @runStat
-- check count within the range
select count(*)
from #tempHist
where rundate
between (DatePart(yyyy, getDate() - @dys ) * 10000 +
DatePart(mm, getDate() - @dys) * 100 +
DatePart(dd, getDate() - @dys))
AND
(DatePart(yyyy, getDate() ) * 10000 +
DatePart(mm, getDate() ) * 100 +
DatePart(dd, getDate()))
DROP table #tempHist
end
go
Here is how to execute it: (check count of all successfull executions of job 'BackupUserDB' (assuming there is such a job) within the last 3 days)
exec usp_GetCountJobSuccessInRange @jobName = 'BackupUserDB' , @dys =3 , @runStat = 1
