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:
- Create a Management Pack
- Enable SQL Server 2008 Agent Job Discovery
- Enable SQL Server 2012 Agent Job Discovery
1. Create a Management Pack
- Start the “System Center Operations Manager Console”
- In the “System Center Operations Manager Console” click “Administration”
- In the “Administration” workspace select “Management Pack” and from the “Tasks” pane click “Create Management Pack”
- In the “Create Management Pack” wizard, on the “General Properties” page, enter a “Name” and a “Description” and click “Next”
- In the “Create a Management Pack” wizard, on the “Knowledge” page, click “Create”
2. Enable SQL Server 2008 Agent Job Discovery
- Start the “System Center Operations Manager Console”
- In the “System Center Operations Manager Console” click, “Authoring”
- In the “Authoring” workspace, expand “Management Pack Objects” and select “Object Discovery”
- In the “Object Discovery” page, in the upper right corner click “Change Scoop”
- 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”
- 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”
- 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”
3. Enable SQL Server 2012 Agent Job Discovery
- Start the “System Center Operations Manager Console”
- In the “System Center Operations Manager Console” click, “Authoring”
- In the “Authoring” workspace, expand “Management Pack Objects” and select “Object Discovery”
- In the “Object Discovery” page, in the upper right corner click “Change Scoop”
- 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”
- 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”
- 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”
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.
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.
- Create a System Center Operations Manager Group
- Create a State View
1. Create a System Center Operations Manager Group
- Start the “System Center Operations Manager Console”
- In the “System Center Operations Manager Console” click, “Authoring”
- In the “Authoring” workplace, select “Groups” and from the “Tasks” click “Create a New Group”
- In the “Create Group Wizard” on the “Enter a name and description for the new group” enter the following:
- In the ”Create Group Wizard” on the “Explicit Members” page, click “Next”
- In the “Create Group Wizard” on the “Dynamic Members” page, click “Create/Edit Rules…”
- 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”
- In the “Create Group Wizard” on the “Dynamic Members” page click “Next”
- In the “Create Group Wizard” on the “Subgroup” page click “Next”
- In the “Create Group Wizard” on the “Excluded Members” page click “Create”
2. Create a State View
- Start the “System Center Operations Manager Console”
- In the “System Center Operations Manager Console” click, “Authoring”
- Right click on the folder where you want to create the new state view and click “New” and select “State View”
- Enter the “Name” for the Group
- In the “Criteria” box, in the “Show data contained in a specific group” select the above created group and click “OK”
- The result should look something like this:
Hope this helps monitoring your Microsoft SQL Server Agent Jobs.