Here is an example that list all VMs in a pool. Note that in ESX 2.5 all VMs will end up in the pool called "resources".
select
e2.name as datacenter_name,
e.name as vm_name,
e3.name as resource_pool_name,
vm.guest_os,
vm.mem_size_mb,
vm.num_vcpu
from
vpx_vm vm
inner join vpx_entity e on vm.id = e.id
inner join vpx_datacenter d on d.id = vm.datacenter_id
inner join vpx_entity e2 on d.id = e2.id
left outer join vpx_resource_pool rp on vm.resource_group_id = rp.id
inner join vpx_entity e3 on rp.id = e3.id
where
is_template = 0
order by
e3.name, e2.name, e.name
Here is another query that will give you some details about each pool (in ESX 3.0) and their current resource usage. rp.* returns all info but you can select the colums you want like rp.allocated_mem etc.
select
e.name as pool_name,
cast(rp.allocated_cpu as int) + cast(rp.available_pool_cpu as int) as pool_max_cpu,
rp.*
from
vpx_resource_pool rp
inner join vpx_entity e on rp.id = e.id
inner join vpx_entity e2 on e.parent_id = e2.id
where
e2.name = 'Resources'
order by
e2.name, e.name
You can remove the where filter (e2.name = 'Resources') and add e2.name in the column list to see what it looks like on ESX 2.5.
