Detailed Virtual Machine Information along with Virtual Host.
select
h.device_fk
,h.device_name host_device_name
,h.total_cpus host_cpu_count
,h.ram_mb host_ram_gb
,h.os_name host_os_name
,h.all_ips host_all_ips
,vmm.device_name vm_manager
,h.device_type host_hardware_type
,h.os_architecture host_os_arch
,h.os_version host_os_version
,h.os_version_number host_os_version_number
,case when h.threads_per_core > 1
then 'yes' else 'no'
end host_is_hyperthreaded
,h.core_per_cpu host_cpu_cores
,h.cpu_speed host_cpu_speed_ghz
/* additional host info */
,h.threads_per_core host_core_threads
,case when h.core_per_cpu is null
then h.total_cpus
when h.threads_per_core > 1
then h.total_cpus * h.core_per_cpu * h.threads_per_core
else h.total_cpus * h.core_per_cpu
end host_compute_power
/* assemble guest information */
,g.device_fk guest_device_id
,g.device_name guest_name
,g.all_ips guest_ip_addresses
,g.os_name guest_os_name
,g.in_service guest_is_in_service
,g.os_architecture guest_os_arch
,g.os_version guest_os_version
,g.os_version_number guest_os_version_number
,case
when g.threads_per_core > 1
then 'yes' else 'no'
end guest_is_hyperthreaded
,g.total_cpus guest_cpu_count
,g.core_per_cpu guest_cpu_cores
,g.cpu_speed guest_cpu_speed_ghz
,g.ram_mb guest_ram_gb
,g.local_disk_count guest_disk_count
/* additonal info available - for hosts and guests */
,g.threads_per_core guest_core_threads
,case when g.core_per_cpu is null
then g.total_cpus
when g.threads_per_core > 1
then g.total_cpus * g.core_per_cpu * g.threads_per_core
else g.total_cpus * g.core_per_cpu
end guest_compute_power
,h.last_discovered host_last_update
,g.last_discovered guest_last_update
/* get the hosts that have virtual_host flag on */
from (select * from view_dbb_compute_v2 hsq
where hsq.virtual_host /*
and hsq.os_name NOT IN ('f5','netscaler' */ )) h
/* get the virtual devices that are not part of the network OSs */
left join (select * from view_dbb_compute_v2 gsq
where gsq.device_type = 'virtual') g
on g.virtual_host_device_fk = h.device_fk
left join view_dbb_compute_v2 vmm
on vmm.device_fk = h.vm_manager_device_fk
order by h.device_name asc
NOTES
Use as part of your resource management.