SSRS-Job Monitoring

When one is working in production support handling montoring of jobs, one of the key things that is required is notification of failures/errors. Once notified the next important aspect would be how quickly can the errors be resolved. In today's world of business trying to be lean/agile and execute quickly, turn around times can be very important. Let us for example take a scenario where one is monitoring jobs (which can have mutliple steps ranging from SQL Scripts,SSIS) and there are notifications when there are failures. There are managed environments where only DBA's would have access to job failures in such cases one would have to depend on the DBA's get detailed information about failures, of course building in error logic would help. The developer's would then need to act on the failures and put in the appropriate fixes. One of the ways the trunaround times can be improved is by using SSRS reports that can use the jobs tables present in msdb database. The first SSRS report would be a summary report which would provide summary information of the jobs. The data elements for this report would range from Job Name,Frequency to getting NextRunDateandTime. This report can be generated by using a SELECT statement querying the sysjobs table and sysjobschedule table. One of the important steps that need to be performed during the design of this report is to have a drilldown on the JobName column, the purpose being to get details of the job along with step and failure information. The second report that needs to be built is the Jobs Detail report which would provide step information within the job along with messages associated with the step, this would provide valuable information for the developer to trouble shoot the problem. The main tables that would be involved are:

msdb.dbo.sysjobs

msdb.dbo.sysjobhistory
msdb.dbo.sysjobsteps
msdb.dbo.sysjobschedules (this focusses on the next run date and time for each job)
msdb.dbo.sysschedules (this has schedules for all the jobs)

One of the key tables involved here is sysjobhistory, the message column in this table would provide useful information, this table is connect to sysjobs and sysjobsteps based on job id and step id. The sysjobhistory also contains run date,run time and run duration. The date, time and duration would need to be formatted based on user needs. Once the query is built out and attached to the report, the detail report needs to be referenced in the hyperlink for the Job id in the first report. The detail report would take the Job Name as the parameter. The reports can then be deployed to the SSRS server for the developers to use. One of my team members bought up this idea and it has benefitted our dev team immensely.