<Please see attached file for image>
select convert(varchar, time, 120) time,severity,message
from NAS_TRANSACTION_LOG
where nimts between dateadd(ss, ${startTime}/1000,'1970-01-01 00:00:00:000')
and dateadd(ss, ${endTime}/1000,'1970-01-01 00:00:00:000') order by time
select entity_name as "Virtual Machine",
util_value as "Avg. Util.",
max_util as "Max Util",
trunc(num_cpus,0) num_cpus
from ( select /*+ ordered */
entity_name,
trunc(util_value,2) util_value,
trunc(max_util,2) max_util,
num_cpus,
round(total_memory/(1024*1024)) total_memory,
trunc(100-util_value,2) unused_util_value,
physical_host
from ( select guid,
avg(case when rollup_id = 1 then metric_value else null end) as util_value,
max(case when rollup_id = 9 then metric_value else null end) as max_util
from entity_day_fact a
where day_id between
to_char((to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(${startTime}/1000,'SECOND')),'YYYYMMDD')
and
to_char((to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(${endTime}/1000,'SECOND')),'YYYYMMDD')
and metric_id in ( select metric_dim_id from config_metric_dim
where std_name in ('GBL_LS_VIRT_CPU_UTIL'))
and rollup_id in (1,9)
group by guid ) a,
(select guid,
entity_name,
online_virt_cpu num_cpus,
online_memory total_memory,
c.physical_host
from entity a,
server_dim b,
server_virtual_dim c
where a.entity_name = b.host_name and b.physical_host is not null
and ( lower(c.virtualization_solution) like 'esx%' or lower(c.virtualization_solution) like 'vmware%')
and c.end_date is null
and b.server_dim_id = c.server_dim_id) b
where a.guid = b.guid)
order by util_value desc