Get Job Status of SQL Jobs of Multiple Instances into a Single Excel File

  • Article

This is a standard proceedure to Monitor the SQL Jobs of Multiple Instnaces into a Single Excel File.

Event though we have SCOM Setup to monitor the SQL Jobs, we didnt want to rely on it, so went for this Monitoring of the SQL jobs through an excel file.

So how we performed is

  1. We created a SQL JOB in all the SQL Instances we want to monitor.

  2. The script we want to execute as a Job in each SQL Instance is

Use msdb
go
select distinct @@ServerName as ServerName, '   ' as TAB,j.Name as "Job Name", '    ' as TAB1, h.run_date as LastStatusDate, '     ' as TAB2,
case h.run_status
when 0 then 'Failed'
when 1 then 'Successful'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as JobStatus
from sysJobHistory h, sysJobs j
where j.job_id = h.job_id and h.run_date =
(select max(hi.run_date) from sysJobHistory hi where h.job_id = hi.job_id)
order by 1

  1. We schedule this job in every SQL server to run every day at 12:30 PM.
  2. In the Job Properties we have set the output to a textfile/xls file at a specific location. And the Next SQL server is going to Append the same file.

So Ex:  Server1, creates a output.xls Server2,Server3.. all the Subsequent SQL servers appends it.

  1. If your SQLs are installed over different Disks and if all the Servers cannot append a single file. Generate an individual file with each instance and Schedule a windows Task which is going to Append all these files (As windows can access all the disks).

Note: Schedule this Windows Task to run after the last Job of a SQL server runs.

  1. Then you can open this excel file in your terminal server where Excel is installed, just apply a filter over the 4th column we will able to filter the Jobs using the jobs status with the first column giving the SQL server Name.