Exalogic capacity Planning

Exalogic capacity Planning: how to get the # of cpu/memory of your vServers in Exalogic:

We have several Oracle Exalogic/Exadata machines in house, one of my recent project is to get the cpu/memory number allocated to each vServer we created in all our Exalogic machines. I know these data are somewhere available in our OEM repo but I couldn't find any official document. Took me a while to figure it out but in the end, it's pretty cool considering I can pull all these data into one place(I create an Apex report for this) so they can see how our Exalogic Machines are using.

Below is the sql I was using, keep in mind that our Exalogic Node/vServer has a name convention(we name it exalxxxx and that's part of the logic of my sql, your may be difference.

CREATE OR REPLACE FORCE VIEW "EXA_CPU_MEM" ("HOST", "SERVER_NAME", "CPU", "MEM") AS
  select substr(a.host_name,1,instr(a.host_name,'-')-1) host,a.target_name
  server_name,to_char(b.cpu_count) CPU,to_char(b.alloc_mem_mb/1024) MEM
from  MGMT$VT_VM_CONFIG b,
(
    select host_name,target_name,target_guid from mgmt$target where host_name
    in
    (select target_name from mgmt$target where instr(target_name,'exal')!=0 and target_type='host')
and target_type='oracle_vm_guest'
and instr(target_name,'zsl')!=0
) a
where
a.target_guid=b.target_guid