cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

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);

Labels (1)
Version history
Revision #:
1 of 1
Last update:
‎12-11-2018 10:33 AM
Updated by: