DESCRIPTION
This custom report will list hourly averages for a specified metric and timeframe. To use this report, run it from SQL*Plus while connected to the Ignite repository database.
SQL*PLUS SCRIPT
set verify off
set pagesize 999
set linesize 132
column metric_name format a40
column name format a60
select id, name from cond order by name;
prompt Which Database ID
accept DBID
select id, name from CON_METRICS_NAMES_&DBID order by name;
prompt Which Metric ID to report
accept METRICID
prompt Enter Begin Date (mm/dd/yy)
accept BeginDate
prompt Enter End Date (mm/dd/yy)
accept EndDate
alter session set nls_date_format='mm/dd/yy hh24';
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 to_date('&BeginDate','mm/dd/yy') and to_date('&EndDate','mm/dd/yy')
and n.id = &METRICID
order by n.name,d.d
/
