Here is an easy way to get list of jobs ran in the past with status. Do you know of a better or simpler way to get the output? If so, please use the comment area to share your code.
1: CREATE PROCEDURE [dbo].[pr_check_JobHistory] @dateparam DATETIME = NULL
2:
3: AS
4:
5: BEGIN
6: IF @dateparam IS NULL
7: SET @dateparam = GETDATE();
8:
9: SELECT sysjobhistory.server,
10: sysjobs.name
11: AS
12: job_name,
13: CASE sysjobhistory.run_status
14: WHEN 0 THEN 'Failed'
15: WHEN 1 THEN 'Succeeded'
16: ELSE '???'
17: END
18: AS
19: run_status,
20: Isnull(Substring(CONVERT(VARCHAR(8), run_date), 1, 4) + '-' +
21: Substring(CONVERT(VARCHAR
22: (8), run_date), 5, 2) + '-' +
23: Substring(CONVERT(VARCHAR(
24: 8), run_date), 7, 2), '')
25: AS
26: [Run DATE],
27: Isnull(Substring(CONVERT(VARCHAR(7), run_time+1000000), 2, 2) + ':'
28: +
29: Substring(CONVERT(VARCHAR(7), run_time+1000000), 4, 2
30: )
31: +
32: ':' +
33: Substring(CONVERT(VARCHAR(7), run_time+1000000), 6, 2), '')
34: AS
35: [Run TIME],
36: Isnull(Substring(CONVERT(VARCHAR(7), run_duration+1000000), 2, 2) +
37: ':' +
38: Substring(CONVERT(VARCHAR(7), run_duration+1000000),
39: 4,
40: 2)
41: + ':' +
42: Substring(CONVERT(VARCHAR(7), run_duration+1000000), 6, 2),
43: ''
44: ) AS
45: [Duration],
46: sysjobhistory.step_id,
47: sysjobhistory.step_name,
48: sysjobhistory.MESSAGE
49: FROM msdb.dbo.sysjobhistory
50: INNER JOIN msdb.dbo.sysjobs
51: ON msdb.dbo.sysjobhistory.job_id = msdb.dbo.sysjobs.job_id
52: WHERE sysjobhistory.run_date = Datepart(yyyy, @dateparam) * 10000 +
53: Datepart(mm, @dateparam) * 100 +
54: Datepart
55: (
56: dd, @dateparam)
57: --AND sysjobs.name = @JobName --remove this line if you want to show all jobs for the specified day
58: ORDER BY instance_id DESC
59: END