IF (SELECT COUNT(*) FROM SYS.OBJECTS WHERE NAME LIKE 'HourlyAvgMetric') = 1
DROP PROC HourlyAvgMetric
GO
-- HourlyAvgMetric
--
-- PARMS '@BEGIN_DATE', '@END_DATE', '@DBNAME' ,'@METRIC_NAME'
-- EXAMPLE:
--
-- EXEC HourlyAvgMetric '2010-07-03 00:00','2010-07-05 00:00','CHSQL01','Total User Sessions'
--
-- If you want to get another metric (not 'Total User Sessions'),
-- you will need to issue the following queries manually in the Ignite Repo DB:
--
-- 'select id, name from cond' -- where name equals database instance
-- 'select id, name form con_metrics_names_@ID' -- where @ID equals id from first query
--
--
Create PROC HourlyAvgMetric
(@BEGINDATE VARCHAR(16), @ENDDATE VARCHAR(16), @DBNAME VARCHAR(50),@METRIC_NAME VARCHAR(100))
AS
DECLARE
@SQL VARCHAR(4000),
@GETVAR VARCHAR(4000),
@DBID VARCHAR(3),
@METRICID VARCHAR(10)
CREATE TABLE #METRIC
(METRICID VARCHAR(10))
BEGIN
SELECT @DBID = ID FROM COND WHERE NAME = @DBNAME
SELECT @GETVAR = 'INSERT INTO #METRIC SELECT ID FROM CON_METRICS_NAMES_' + @DBID +
' WHERE UPPER(NAME) = UPPER(' + '''' + @METRIC_NAME + '''' + ')'
exec (@GETVAR)
SELECT @METRICID = METRICID FROM #METRIC;
SELECT @SQL = 'SELECT N.NAME METRIC_NAME, D.D HOUR,D.V_AVG HOURLY_AVERAGE ' +
'FROM CON_METRICS_HOUR_' + @DBID + ' D, CON_METRICS_' + @DBID + ' M, CON_METRICS_NAMES_' + @DBID + ' N ' +
'WHERE M.METRIC_NAME_ID = N.ID ' +
'AND M.ID = D.METRICS_ID ' +
'AND D.D BETWEEN ' +
'CONVERT(DATETIME,' + '''' + @BEGINDATE + '''' + ', 101) AND ' +
'CONVERT(DATETIME,' + '''' + @ENDDATE + '''' + ', 101) ' +
'AND N.ID = ' + @METRICID +
' ORDER BY N.NAME,D.D'
EXEC (@SQL)
END
GO