Trend report

select
v'CPU Utilization' as "Value Description",
to_char(REGR_COUNT (total_cpu_percent, collection_time)) || ' Samples' "Sample Count",
REGR_AVGX (total_cpu_percent, collection_time) avg_x,
MIN (collection_time) MIN_X,
MAX (collection_time) MAX_X,
MIN (total_cpu_percent) MIN_Y,
MAX (total_cpu_percent) MAX_Y,
REGR_SLOPE (total_cpu_percent, collection_time) slope,
REGR_INTERCEPT (total_cpu_percent, collection_time) intercept,
round(REGR_R2 (total_cpu_percent, collection_time), 3) "R Squared",
to_char(round(REGR_SLOPE (total_cpu_percent, collection_time) * date_to_number(sysdate+7) +
REGR_INTERCEPT (total_cpu_percent, collection_time), 0)) || ' %' "7 Day Estimate",
to_char(round(REGR_SLOPE (total_cpu_percent, collection_time) * date_to_number(sysdate+30) +
REGR_INTERCEPT (total_cpu_percent, collection_time), 0)) || ' %' "30 Day Estimate",
to_char(round(REGR_SLOPE (total_cpu_percent, collection_time) * date_to_number(sysdate+120) +
REGR_INTERCEPT (total_cpu_percent, collection_time), 0)) || ' %' "120 Day Estimate",
to_char(min(current_cpu_percent)) || ' %' "Current Usage"
from
(
select
e1.collection_id,
date_to_number(max(e1.collection_time)) collection_time,
count(*),
avg(cpu_percent) total_cpu_percent,
max(current_time_query.current_cpu_percent) current_cpu_percent
from
host_utilization o1
join common_elements e1
on e1.collection_id = o1.collection_id,
(
select
avg(o1.cpu_percent) as current_cpu_percent
from
host_utilization o1
join common_elements e1
on e1.collection_id = o1.collection_id
where
e1.clarity_object_id = __ENTITY_ID__
and
e1.clarity_manager_id = '__MGR_ID__'
and
e1.collection_time = (
select
/*+ INDEX_DESC(e2 IX_COMMON_ELEMENTS_TIME) */ collection_time
from
host_utilization p2
join common_elements e2
on p2.collection_id = e2.collection_id
where
e2.clarity_object_id = __ENTITY_ID__
and
e2.collection_time <= to_date('__COLLECTION_TIME__', 'YYYY-MM-DD HH24:MI:SS')
and
e1.clarity_manager_id = '__MGR_ID__'
and
rownum = 1
)
) current_time_query
where
e1.clarity_object_id = __ENTITY_ID__
and
e1.clarity_manager_id = '__MGR_ID__'
group by
e1.collection_id
)
UNION ALL
select
'Memory Utilization' as "Value Description",
to_char(REGR_COUNT (total_mem_used, collection_time)) || ' Samples' "Sample Count",
REGR_AVGX (total_mem_used, collection_time) avg_x,
MIN (collection_time) MIN_X,
MAX (collection_time) MAX_X,
MIN (total_mem_used) MIN_Y,
MAX (total_mem_used) MAX_Y,
REGR_SLOPE (total_mem_used, collection_time) slope,
REGR_INTERCEPT (total_mem_used, collection_time) intercept,
round(REGR_R2 (total_mem_used, collection_time), 3) "R Squared",
to_char(round(REGR_SLOPE (total_mem_used, collection_time) * date_to_number(sysdate+7) +
REGR_INTERCEPT (total_mem_used, collection_time), 0)) || ' KB' "7 Day Estimate",
to_char(round(REGR_SLOPE (total_mem_used, collection_time) * date_to_number(sysdate+30) +
REGR_INTERCEPT (total_mem_used, collection_time), 0)) || ' KB' "30 Day Estimate",
to_char(round(REGR_SLOPE (total_mem_used, collection_time) * date_to_number(sysdate+120) +
REGR_INTERCEPT (total_mem_used, collection_time), 0)) || ' KB', "120 Day Estimate",
to_char (max(current_mem_used)) || ' KB' "Current Usage"
from
(
select
e1.collection_id,
date_to_number(max(e1.collection_time)) collection_time,
count(*),
sum(mem_used) total_mem_used,
max(current_time_query.current_mem_used) current_mem_used
from
host_utilization o1
join common_elements e1
on e1.collection_id = o1.collection_id,
(
select
sum(o1.mem_used) as current_mem_used
from
host_utilization o1
join common_elements e1
on e1.collection_id = o1.collection_id
where
e1.clarity_object_id = __ENTITY_ID__
and
e1.clarity_manager_id = '__MGR_ID__'
and
e1.collection_time = (
select
/*+ INDEX_DESC(e2 IX_COMMON_ELEMENTS_TIME) */ collection_time
from
host_utilization p2
join common_elements e2
on p2.collection_id = e2.collection_id
where
e2.clarity_object_id = __ENTITY_ID__
and
e2.collection_time <= to_date('__COLLECTION_TIME__', 'YYYY-MM-DD HH24:MI:SS')
and
e1.clarity_manager_id = '__MGR_ID__'
and
rownum = 1
)
) current_time_query
where
e1.clarity_object_id = __ENTITY_ID__
and
e1.clarity_manager_id = '__MGR_ID__'
group by
e1.collection_id
)