tier report for all servers

[fthivier]

This is an example of tier report that i use to show server utilization by tier
-----------------------------------------------------------------------------------------
select grouping(nom),
nom,
sum(tier10)/1024/1024 tier10,
sum(tier20)/1024/1024 tier20,
sum(tier30)/1024/1024 tier30,
sum(tier40)/1024/1024 tier40,
sum(tier50)/1024/1024 tier50,
sum(tier110)/1024/1024 tier110,
sum(tier120)/1024/1024 tier120
from (
select distinct(a.disk_id),
a.host_name nom,
/* this is our tier10 USPV with 146 GB */
case when c.group_name like '%-%-%' and d.total_capacity_gb < 1200 and
d.array_name = 'USP_V@1' then
a.disk_size_kb
when c.group_name like '%-%-%' and d.total_capacity_gb < 1200 and
d.array_name = 'USP_V2' then
a.disk_size_kb
else 0
end tier10,
/* this is our tier20 USPV with 300 GB */
case when c.group_name like '%-%-%' and d.total_capacity_gb > 1200 and
d.total_capacity_gb < 2200 and
d.array_name = 'USP_V@1' then
a.disk_size_kb
when c.group_name like '%-%-%' and d.total_capacity_gb > 1200 and
d.total_capacity_gb < 2200 and
d.array_name = 'USP_V@2' then
a.disk_size_kb
else 0
end tier20,
/* this is our tier110 (remote) with 146 GB */
case when c.group_name like '%-%-%' and d.total_capacity_gb < 1200 and
d.array_name = 'USP_V@3' then
a.disk_size_kb
else 0
end tier110,
/* this is our tier120 (remote) with 400 GB */
case when c.group_name like '%-%-%' and d.total_capacity_gb > 2200 and
d.total_capacity_gb < 3200 and
d.array_name = 'USP_V@3' then
a.disk_size_kb
else 0
end tier120,
/* this is our tier30 an AMS1000 virtualize under USP with 300 GB */
case when c.group_name like 'E103%' then a.disk_size_kb
else 0
end tier30,

/* this is our tier40 an AMS500 virtualize under USP with 400 GB */

case when c.group_name like 'E104%' then a.disk_size_kb
when c.group_name like 'E54%' then a.disk_size_kb
when c.group_name like 'E484%' then a.disk_size_kb
else 0
end tier40,

/* this is our tier30 an AMS500 virtualize under USP with 500 GB sata*/

case when c.group_name like 'E55%' then a.disk_size_kb
else 0
end tier50

FROM aps_v_file_system_path a, aps_v_lun_path b,
aps_v_logical_unit c, aps_v_array_group d
where a.host_disk_id = b.host_disk_id and
b.logical_unit_id = c.logical_unit_id and
c.array_group_id = d.array_group_id and
a.hba_device_name not like 'vmhba%')

group by (nom)