This custom report will return VMware metric details for the metric name provided in the @MetricName variable and the database name provided in @DBName. The report will also be limited to the date range specified in the @BeginDate and @EndDate variables.
DECLARE @DBName varchar(400) = '<DB Name shown on Home Page>', @VMID varchar(5), @SQL nvarchar(max), @SQLParms nvarchar(max);
DECLARE @BeginDate datetime = '10/5/2018 00:00', @EndDate datetime = '10/6/2018 00:00';
-- need to pass in a metric name, to get a list of the names execute this against the DPA repository
-- these names will loosely match the titles in the Resources page
-- SELECT DISTINCT METRIC_KEY FROM ignite.CONV_METRICS
DECLARE @MetricName varchar(100) = '<VM Metric Name>';
-- the Ignite metric data is stored in VM specific tables, and we need the VMID to get there
SELECT @VMID=VMID FROM ignite.COND WHERE NAME = @DBName;
-- query the VM metric tables for whatever date range is passed in
SET @SQL = N'SELECT '''+@MetricName+';'' AS "MetricName", D AS "Timestamp", V AS "Value"
FROM ignite.CONV_METRICS m
INNER JOIN ignite.CONV_METRIC_DETAIL_'+@VMID+'; md ON md.METRIC_ID = m.ID
WHERE m.METRIC_KEY = @MetricName
AND md.D BETWEEN @BeginDate AND @EndDate
ORDER BY m.METRIC_KEY, md.D';
-- execute the query and pass in parameters needed
SET @SQLParms = N'@MetricName varchar(100), @BeginDate datetime, @EndDate datetime';
EXECUTE sp_executesql @SQL, @SQLParms, @MetricName=@MetricName, @BeginDate=@BeginDate, @EndDate=@EndDate;
