How can I find servers which don't match the Template?

There are some servers where I have change the Alert Configuration from the default template, I.E. unticking the "CLR Enabled" so that it no-longer alerts for this condition. Is there a way to find all servers that have had these changes made?

  • There's not a way to perform this kind of comparison within the desktop application itself. You can, however, query the SQL DM repository database to find out which metrics aren't set to the settings that you'd expect. There are going to be three tables in the repository database that you are going to be interested in.

    1. MetricInfo
      This table contains the various metrics that are monitored by SQL Server. It's just a holding place for the details of the metrics (e.g. Name Description, ID, etc).
    2. MetricThresholds
      This table basically stores the settings that are defined for each metric for each monitored instance.
    3. MonitoredSQLServers
      This table contains the SQL Server instances that are being monitored by SQL Diagnostic Manager.

    Using the "CLR Enabled" metric as an example, I can query MetricInfo and see that it has a metric ID of 49. Using that info alone, I can then use the following query to list the instances that have this particular metric disabled.

    SELECT MSS.InstanceName
    FROM MetricThresholds AS MT JOIN MonitoredSQLServers AS MSS ON MT.SQLServerID=MSS.SQLServerID
    WHERE MT.Metric=49 AND MT.Enabled=0

    I hope that helps!

Reply Children
No Data