Brief Description: A quick way to show vital stats on hosts in a cluster.
Problem: The virtual center database is kind of ugly in the way it maps performance statistics to hosts (it appends a host- to the beginning of the ID, stores the clustername one level up from the host etc).
The script is intended to map the basic stats for all hosts in a cluster. It's part of a bigger reporting services dashboard I'm building out, but I think it's helpfull on its own. As always please feel free to post corrections.
--Map hosts to basic cluster information
declare @ClustertoHost table
insert into @Clustertohost (hostname, clustername, clusterid, hostid, hoststatid, HostCpu, HostMemory)
select h.name, c.name, c.id, h.id, 'host-' + CONVERT (Varchar, h.ID) AS statID, (convert( bigint, vh.cpu_hz) * vh.cpu_core_count)/1024/1024/1024 as CPUTotal, (convert( bigint, vh.mem_size))/1024/1024/1024 as Memory
vpx_entity h join vpx_entity c
on h.parent_id = c.id
join vpx_host vh on
vh.id = h.id
where h.type_id = 1
--select * from @clustertohost