Custom metric walkthrough

July 10, 2012 by | 0 comments | 1,494 views
Categories:

VN:F [1.9.17_1161]
Rating: 5.0/5 (2 votes cast)

    You’ve found a metric that you want to use in your SQL Server environment. What should you do next? Getting a metric working in SQL Monitor is easy, and we’ve provided this short walkthrough to help you get started.

    In this scenario, you’ve found TJay Belt’s Disabled backup jobs example metric. After reading through the summary, you’ve decided it’s a metric that you want to start monitoring in SQL Monitor v3.2. Here’s what you need to do:

    1. Install cookies – If you want to download metrics automatically, you must install cookies and make sure cookies are enabled in your Internet browser. If you arrived at this site directly from a link in SQL Monitor, you’ve installed cookies already. If you didn’t, go to the Configuration > Custom metrics page in SQL Monitor, and click on one of the links to sqlmonitormetrics.red-gate.com.
    2. Install metric – Click the Install button near the top of this page. SQL Monitor’s Create custom metric wizard is displayed in a new tab, with fields automatically populated with the example metric settings.
    3. Update settings – Certain settings in this metric must be tuned to match your SQL Server environment, and notes are provided below each setting that needs updating:
      • Take a look at the Enter the T-SQL used to collect data setting. There’s a note below the query explaining that specific job names must be replaced with names appropriate to your system. So replace: 
        '%backup%'

        with the name of your own backup jobs, and: 

        'Daily Backups.Subplan_1'

        with the name of any jobs you want to exclude.

      • Now look at the note for the Select instances to collect from setting. As advised, turn on the check boxes for the instances you want to run the query against.
      • There is also a note for the Define alert thresholds setting. Every alert on this site contains a note about how the thresholds settings are intended as guideling values. For TJay’s metric, he suggests raising a high alert once disabled backup jobs are detected, but you might prefer to raise this at a lower alert level if you don’t believe it deserves top priority.
    4. Test metric – To make sure the updated metric will work as expected, click the Test metric collection… button. The dialog displays a list of the MSDB databases for the instances you’ve selected. Click the Test metric collection button. SQL Monitor runs the query once against each selected database. If the test does not complete successfully, see the section called Test metric collection on Red Gate’s Support Center.
    5. Create metric – In the Create custom metric wizard, click Next until the Summary page is displayed. Now click the Create metric and alert button. The new metric is listed on the custom metrics page: <screenshot>

    You’ve now got this example metric working in your environment, and can check the graph on the Analysis page in SQL Monitor to see collected values plotted as data points.

    Custom metric walkthrough, 5.0 out of 5 based on 2 ratings

    Metric definition

    Metric Name:
    Disabled backup jobs
    Description:

    This metric investigates backup jobs, searching for any that may be disabled inappropriately.

    If any of your backup jobs have been disabled inappropriately, you may not be aware of their state. This may cause you grief if backups do not occur properly. Ensuring that your backup jobs are functioning as desired is paramount. Monitoring them is vital to the health of your topology.

    Enter the T-SQL query that will collect data:
    SELECT 
      COUNT(*) AS [Count]
    FROM msdb.dbo.sysjobs sj
      left outer join ( 
                        SELECT 
                            MAX(CASE 
                                  WHEN run_date is NULL THEN ''
                                  else
                                    substring ( CONVERT( VARCHAR, sh.run_date), 1, 4) + '-'
                                      + substring ( CONVERT( VARCHAR, sh.run_date), 5, 2) + '-'
                                      + substring ( CONVERT( VARCHAR, sh.run_date), 7, 2)
                                end) AS [run_date], 
                            job_id
                          FROM msdb.dbo.sysjobhistory sh
                            WHERE Step_id = 0
                            GROUP BY job_id
                      ) AS sh on sh.job_id = sj.job_id
      WHERE sj.name LIKE '%backup%'
        AND sj.name NOT IN ( 'Daily Backups.Subplan_1')
AND sj.[enabled] <> 1

    Note: This query looks for jobs with ‘backup’ in the name. It also excludes specific jobs that are acceptable to be disabled. Replace the following terms with appropriate values for your system and needs, ensuring that they match your naming convention:

    [%backup%]

    ['Daily Backups.Subplan_1']
    Select instances to collect from:
    Manually select instances containing the databases you’re interested in
    Note: Turn on the relevant check boxes.
    Choose databases to collect from:
    Specify databases and type
    MSDB
    Set collection frequency:
    Collect data every day
    Use collected or calculated values:
    Leave the Use a calculated rate of change between collections check box turned off
    Metric collection is:
    Enabled

    Alert definition

    Alert name:
    Disabled backup jobs
    Description:

    This alert is raised when named backup jobs are in a disabled state. It keeps a vigilant eye on your systems, ensuring that the appropriate jobs remain in an enabled state, and allowing their functionality to continue executing properly.

    Execute the following query to see status of all backup jobs:

    SELECT 
        @@ServerName AS [ServerName],
        sj.[Name], 
        CASE  
          WHEN sj.[enabled] = 1 THEN 'Enabled'
          ELSE 'Not Enabled - Please Investigate'
        END AS [Enabled],
        ISNULL( run_date, '') AS [Run_Date]
      FROM msdb.dbo.sysjobs sj
        left outer join ( 
                          SELECT 
                              MAX(CASE 
                                    WHEN run_date is NULL THEN ''
                                    else
                                      substring ( CONVERT( VARCHAR, sh.run_date), 1, 4) + '-'
                                        + substring ( CONVERT( VARCHAR, sh.run_date), 5, 2) + '-'
                                        + substring ( CONVERT( VARCHAR, sh.run_date), 7, 2)
                                  end) AS [run_date], 
                              job_id
                            FROM msdb.dbo.sysjobhistory sh
                            where Step_id = 0
                            GROUP BY job_id
                        ) AS sh on sh.job_id = sj.job_id
      WHERE sj.name LIKE '%backup%'
        AND sj.name NOT in ('Daily Backups.Subplan_1')
      ORDER BY [run_date] DESC, sj.[name]
    Raise an alert when the metric value goes:
    Above the defined thresholds
    Default threshold values:
    Set the thresholds as follows:
    High:0
    Medium:Turn off check box
    Low:Turn off check box
    Note: These thresholds are intended as guideline values. If they seem too high or too low for your environment, replace them with values more suited to your server performance.
    Raise an alert when the threshold is passed for:
    Enter 1 collection
    This alert is:
    Enabled
    VN:F [1.9.17_1161]
    Rating: 5.0/5 (2 votes cast)