This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Custom Report - Metric Values

Description

The query below will provide detailed metric values for any metric in the instance, e.g. the metrics shown on the Resources tab in DPA>

Query

DECLARE @DBID varchar(100), @MetricName varchar(50), @SQL varchar(1000);

-- change the instance name here, matches the name shown in the Home page

SELECT @DBID = ID FROM ignite.COND WHERE NAME = '<instance name>';

-- get a list of metric names from CON_METRIC_NAMES_XX

SET @MetricName = 'SQL Disk Read Latency';

-- CON_METRICS_DETAIL_XX holds the detailed collections of the metric value

-- CON_METRICS_HOUR_XX hold hourly summaries, and includes V_AVG, V_MIN, V_MAX values for the hour

-- CON_METRICS_TEN_MINUTE_XX holds 10 min summaries, and includes V_AVG, V_MIN, V_MAX values for the 10 min

SET @SQL = 'SELECT mn.NAME AS "Metric Name", md.D as "Timestamp", md.V AS "Metric Value"

FROM ignite.CON_METRICS_DETAIL_'+@DBID+' md

INNER JOIN ignite.CON_METRICS_'+@DBID+' m ON m.ID = md.METRICS_ID

INNER JOIN ignite.CON_METRICS_NAMES_'+@DBID+' mn ON mn.ID = m.METRIC_NAME_ID

-- adjust the timeframe here

WHERE md.D >= DATEADD(hour, -24, CURRENT_TIMESTAMP)

AND mn.NAME = '''+@MetricName+'''

ORDER BY md.D';

EXEC (@SQL);

  • Custom Metric MetricName: 

    Unlike DPA out of the box metrics, the MetricName in the script for custom metrics will not be the display name. Custom metric names are assigned an internal "System ID" name of CustomMetric-# - e.g., CustomMetric-1, CustomMetric-2, ... To find the System ID assigned to a custom metric, edit the custom metric at Options -> "REPORT, METRICS & ALERTS" and clicking on "Custom Metrics". In the upper right corner you will see System ID: <MetricName>