script to collect the all servers jobs information?

Question

Hi All,

Greetings of the day,

i want to gauther the all servers jobs information like result,last successful run date, next run time,status etc..and get that result to my mail periodically and automatically...

please guide me how can i fullfil my goal....

Thanks in advance,

---

rup

Answer 1

Check this blog post

http://bradsruminations.blogspot.co.il/2011/04/documenting-your-sql-agent-jobs.html

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Try like this :-

1. Create linked server in your cenrtal SQL server for all SQL server you want to monitor
2. Add or remove columns in below query as you want
3. Use UNION ALL & with use same commands for each linked server like below

select T1.[Name],T2.Step_name, (case T2.Run_Status
        when 0 then 'Failed'
        when 1 then 'Succeeded'
        when 2 then 'Retry' else
        'Canceled' end) Last_Run_Status, SUBSTRING(RTRIM(CONVERT(varchar(10), T2.Run_Date ,101)), 1, 4)
 + '/'+SUBSTRING(RTRIM(CONVERT(varchar(10), T2.Run_Date ,101)), 5, 2)
 + '/'+ SUBSTRING(RTRIM(CONVERT(varchar(10), T2.Run_Date ,101)),7, 2)
 + ' '+ SUBSTRING(RIGHT('00000'+RTRIM(CONVERT(char,T2.Run_Time)),6),1,2)
 +':'+ SUBSTRING(RIGHT('00000'+RTRIM(CONVERT(char,T2.Run_Time)),6),3,2)
 +':'+ SUBSTRING(RIGHT('00000'+RTRIM(CONVERT(char,T2.Run_Time)),6),5,2) Run_Time from
[Linked Server Name].msdb.dbo.sysjobs T1
Inner Join
[Linked Server Name].msdb.dbo.sysjobhistory T2
ON T1.Job_id = T2.Job_Id
where T1.[enabled] =1 and T2.instance_id IN (SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory where Step_id <>0 Group by Job_id)

UNION ALL

select T1.[Name],T2.Step_name, (case T2.Run_Status
        when 0 then 'Failed'
        when 1 then 'Succeeded'
        when 2 then 'Retry' else
        'Canceled' end) Last_Run_Status, SUBSTRING(RTRIM(CONVERT(varchar(10), T2.Run_Date ,101)), 1, 4)
 + '/'+SUBSTRING(RTRIM(CONVERT(varchar(10), T2.Run_Date ,101)), 5, 2)
 + '/'+ SUBSTRING(RTRIM(CONVERT(varchar(10), T2.Run_Date ,101)),7, 2)
 + ' '+ SUBSTRING(RIGHT('00000'+RTRIM(CONVERT(char,T2.Run_Time)),6),1,2)
 +':'+ SUBSTRING(RIGHT('00000'+RTRIM(CONVERT(char,T2.Run_Time)),6),3,2)
 +':'+ SUBSTRING(RIGHT('00000'+RTRIM(CONVERT(char,T2.Run_Time)),6),5,2) Run_Time from
[Linked Server Name].msdb.dbo.sysjobs T1
Inner Join
[Linked Server Name].msdb.dbo.sysjobhistory T2
ON T1.Job_id = T2.Job_Id
where T1.[enabled] =1 and T2.instance_id IN (SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory where Step_id <>0 Group by Job_id)

---

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!