Use central management server to check job failures on all registered servers and email same

  • caz100

    SSCommitted

    Points: 1609

    Hi

    I am looking for help on how to use CMS to check job failures on all the registered servers and email the results.

    Thanks in Advance!

  • Sue_H

    SSC Guru

    Points: 90287

    caz100 - Saturday, September 29, 2018 3:31 AM

    Hi

    I am looking for help on how to use CMS to check job failures on all the registered servers and email the results.

    Thanks in Advance!

    The functionality of querying multiple servers with a single query using CMS is a SSMS feature, not a SQL Server feature so it's likely not doable in the way you might be thinking. You can use your CMS to get a list of the servers to query in Powershell and use that to execute the same query against the servers instead of using a server list. That approach is explained in this article:
    Central Management Server

    Is there a reason you wouldn't just want a failed jobs report type of job on each of the servers? Having a job like that on your servers is fairly common.

    Sue

  • caz100

    SSCommitted

    Points: 1609

    Hi Sue

    There are 100's of servers and the request is for one email containing all failed jobs.

    It seems powershell is the only way to achieve it really.

    I posted twice as no-one was responding to this one:

    https://www.sqlservercentral.com/Forums/1997956/Using-Central-Management-server-to-schedule-and-email-report-on-failed-jobs?Update=1#bm1998031

  • Sue_H

    SSC Guru

    Points: 90287

    caz100 - Monday, October 1, 2018 12:54 PM

    Hi Sue

    There are 100's of servers and the request is for one email containing all failed jobs.

    It seems powershell is the only way to achieve it really.

    I posted twice as no-one was responding to this one:

    https://www.sqlservercentral.com/Forums/1997956/Using-Central-Management-server-to-schedule-and-email-report-on-failed-jobs?Update=1#bm1998031

    If you want just one email then yes you'd need to go down the powershell route to do this. I didn't even notice the two posts until you mentioned it...oops, my bad.
    Anyway, look at that first link I posted and you'll get the idea of how to use CMS for the server list. You could look at something like dumping the results from powershell into some central table and then grab the results from that table to send the email.
    You may want to just get it working for one server. Adding the others servers from CMS is the easier part to add.

    Sue

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply