Information
Space banner
This version of the product has reached end of support. The documentation is available for your convenience. However, you must be logged in to access it. You will not be able to leave comments.
Trend report
An example trend report is as follows:
Information
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
)
Example
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
)