REM $Id: cpu_trend.sql,v 1.4 2013/06/20 07:08:42 ewan Exp $

column target_name new_value def_t
select target_name from mgmt_targets where target_type='host'
order by lower(target_name)
/

set verify off

accept host prompt "host [&def_t]> " default '&def_t'

column target_guid new_value t_guid
select target_guid from mgmt_targets where target_name='&host';

accept res prompt "aggregation (y, m or d) [d]> " default 'd'

column format new_value form
select
  case
    when '&res'='y' then 'yyyy'
    when '&res'='m' then 'yyyy-mm'
    else 'yyyy-mm-dd'
  end as format
from dual
/

alter session set nls_date_format='&form';

accept sd prompt "show days (wd 'weekday', we 'weekend' or a 'all') [a]> " default 'a'

column days new_value dy
select
  case
    when '&sd'='wd' then 'in (''Mon'',''Tue'',''Wed'',''Thu'',''Fri'')'
    when '&sd'='we' then 'in (''Sat'',''Sun'')'
    else 'is not null'
  end as days
from dual
/

column dstart new_value def_d1
column dfinish new_value def_d2
select current_date - interval '12' month as dstart, current_date as dfinish
from dual
/

accept start prompt "start (&form) [&def_d1]> " default '&def_d1'
accept finish prompt "finish (&form) [&def_d2]> " default '&def_d2'

set linesize 80 pagesize 0 feedback off
set numformat 99999999
column tablespace format a32

define out=cpu_trend_&res._&host._&start._&finish
spool &out..txt

select
  to_date(to_char(cpu.timestamp,'&form'),'&form') as stamp,
  round(min(cpu.min_util),2) as min_cpu,
  round(avg(cpu.avg_util),2) as avg_cpu,
  round(max(cpu.max_util),2) as max_cpu
from
  (
    select
      m.rollup_timestamp as timestamp,
      avg(m.average) as avg_util,
      min(m.minimum) as min_util,
      max(m.maximum) as max_util
    from
      mgmt$metric_daily m
      inner join mgmt$target_type t
        on m.target_guid=t.target_guid and m.metric_guid=t.metric_guid
    where
      t.target_guid=hextoraw('&t_guid')
      and t.target_type='host'
      and t.metric_name='Load'
      and t.metric_column='cpuUtil'
      and m.rollup_timestamp >= to_date('&start')
      and m.rollup_timestamp <= to_date('&finish')
      and to_char(m.rollup_timestamp, 'Dy') &dy
    group by
      m.rollup_timestamp
  ) cpu
group by
  to_char(cpu.timestamp,'&form')
order by
  to_char(cpu.timestamp,'&form')
/

spool off

spool &out..gp

prompt set datafile separator whitespace
prompt set terminal pdfcairo
prompt set output "&out..pdf"
prompt set term pdfcairo enhanced font "Arial, 8"
prompt 
prompt set title "&host CPU history"
prompt 
prompt set xlabel "Date"
prompt set xdata time
prompt set timefmt "%Y-%m-%d"
prompt set xtics format "%m/%Y"
prompt 
prompt set ylabel "Utilisation (%)"
prompt set yrange [0:100]
prompt 
prompt set pointsize 0.2
prompt set style data linespoints
prompt 
prompt plot "&out..txt" using 1:4 title "Max", \
prompt      "&out..txt" using 1:3 title "Avg", \
prompt      "&out..txt" using 1:2 title "Min"

spool off

set pagesize 14 verify on feedback on