Version 1

    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;