Custom metric walkthrough

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: [sql]’%backup%'[/sql] with the name of your own backup jobs, and: [sql]’Daily Backups.Subplan_1′[/sql] 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.

Metric definition

Name

Description

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

Alert name

Description

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

Default threshold values

Raise an alert when the threshold is passed for

Alert is