Monitoring SQL Job Status using SCOM

Standard

If you are using the Microsoft System Center Management Pack for SQL Server and you want tot monitor the SQL Agent Job Status. You can use the following steps to accomplish this. The Microsoft System Center Management Pack for SQL can be downloaded from here.

As you can read in the “SQL Server Management Pack Guide” witch you can download from here, the “SQL Server Agent job’s” are not discovered by default. The following SQL Server Agent Jobs are not discovered by default:

  • SQL Server 2005 Agent Job
  • SQL Server 2008 Agent Job
  • SQL Server 2008 r2 Agent Job
  • SQL Server 2012 Agent Job

In this blog post I’m going to enable monitoring for SQL Server Agent Job’s with the specific description backup. In my environment I don’t have SQL Server 2005 running.  I Used the following steps to create the above option:

  1. Create a Management Pack
  2. Enable SQL Server 2008 Agent Job Discovery
  3. Enable SQL Server 2012 Agent Job Discovery

    1. Create a Management Pack

    1. Start the “System Center Operations Manager Console
    2. In the “System Center Operations Manager Console” click “Administration
    3. In the “Administration” workspace select “Management Pack” and from the “Tasks” pane click “Create Management PackCreate Management Pack
    4. In the “Create Management Pack” wizard, on the “General Properties” page, enter a “Name” and a “Description” and click “NextCreate Management Pack
    5. In the “Create a Management Pack” wizard, on the “Knowledge” page, click “Create

    2. Enable SQL Server 2008 Agent Job Discovery

    1. Start the “System Center Operations Manager Console
    2. In the “System Center Operations Manager Console” click, “Authoring
    3. In the “Authoring” workspace, expand “Management Pack Objects” and select “Object Discovery
      Object Discovery
    4. In the “Object Discovery” page, in the upper right corner click “Change Scoop
    5. In the “Scope Management Pack Objects” page, click “Clear All” and select “View all Targets”. In the “Look for” bar, type “SQL Server Agent Job” and select “SQL Server Agent Job” and click “OK
      Object Discovery Change Scoop
    6. In the “Object Discovery” page, right click “Discover SQL Server 2008 Agent Jobs” and select “Overrides”, “Override the Object Discovery”, “For all objects of class: SQL Server 2008 Agent
      SCOMObjectDiscovery3
    7. In the “Override Properties” page, check “Enable” and change the “Override Value” to “True”. Select the above created destination management Pack “SQL Job Monitoring” and click “Apply” and “Ok”      Object Discovery Override

    3. Enable SQL Server 2012 Agent Job Discovery

    1. Start the “System Center Operations Manager Console
    2. In the “System Center Operations Manager Console” click, “Authoring
    3. In the “Authoring” workspace, expand “Management Pack Objects” and select “Object Discovery
      Object Discovery
    4. In the “Object Discovery” page, in the upper right corner click “Change Scoop
    5. In the “Scope Management Pack Objects” page, click “Clear All” and select “View all Targets”. In the “Look for” bar, type “SQL Server 2008 Agent Job” and select “SQL Server 2012 Agent Job” and click “OK” 
      Object Discovery
    6. In the “Object Discovery” page, right click “Discover SQL Server 2012 Agent Jobs” and select “Overrides”, “Override the Object Discovery”, “For all objects of class: SQL Server 2012 Agent” 
      SCOM Object Discovery
    7. In the “Override Properties” page, check “Enable” and change the “Override Value” to “True”. Select the above created destination management Pack “SQL Job Monitoring” and click “Apply” and “Ok”      
      SCOM Object Discovery

    When you now op the “System Center Operations Manager Console” select “Monitoring” expand “Microsoft SQL”, “SQL Server Database Engines”, “SQL Server 2005/2008/2012”, “SQL Agent” and click on the view “SQL Agent Job State” you will see all SQL Agent Jobs that are now running on your SQL environment.
    SQLAgentJobs

    In my environment I have several Microsoft SQL Servers which all have the same name and the same tasks. I used the following steps to create a Group and view to monitor the SQL Jobs for the specific tasks.

    1. Create a System Center Operations Manager Group
    2. Create a State View

    1. Create a System Center Operations Manager Group

    1. Start the “System Center Operations Manager Console
    2. In the “System Center Operations Manager Console” click, “Authoring
    3. In the “Authoring” workplace, select “Groups” and from the “Tasks” click “Create a New Group
      Create Group
    4. In the “Create Group Wizard” on the “Enter a name and description for the new group” enter the following:
      • Enter the “Name” for the Group
      • Type the “Description” for the group
      • Select a destination “Management Pack” that you created above.
      • Click “Next
        Create Group
    5. In the ”Create Group Wizard” on the “Explicit Members” page, click “Next
    6. In the “Create Group Wizard” on the “Dynamic Members” page, click “Create/Edit Rules…
    7. In the “Create Group Wizard – Query Builder” in the “Select the desired Class and click the Add button to begin the formula” select “SQL Agent Job” and click “Add”. From the pull down menu under “Property” and select “Job Name”. From the pull down menu under “Operator” and select “Contains” Under the “Value” option enter the name of the SQL Job that you want to monitor. In my case is that “BackupSystemDB” and click “OK
      Create Group
    8. In the “Create Group Wizard” on the “Dynamic Members” page click “Next
    9. In the “Create Group Wizard” on the “Subgroup” page click “Next
    10. In the “Create Group Wizard” on the “Excluded Members” page click “Create” 

     2. Create a State View

    1. Start the “System Center Operations Manager Console
    2. In the “System Center Operations Manager Console” click, “Authoring
    3. Right click on the folder where you want to create the new state view and click “New” and select “State View
      Create State View
    4. Enter the “Name” for the Group
    5. In the “Criteria” box, in the “Show data contained in a specific group” select the above created group and click “OK
      Create State View
    6. The result should look something like this:
      Create State View  

    Hope this helps monitoring your Microsoft SQL Server Agent Jobs.