Monday, February 22, 2010

Query to map basic host to cluster information VMware 3.5

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).

Quick Script:

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
(
hostname nvarchar(100),
Clustername nvarchar(50),
Clusterid int,
hostid int,
hoststatid nvarchar(50),
HostCPU bigint,
HostMemory bigint
)

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
from
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

No comments:

Post a Comment