If you use VMWare for your virtualisation environment all virtual machines are managed by one or more vCentre management servers. These servers can use a SQL Server database in the back end. By default the database is called VCDB but could be called anything really.
Todays script assumes that you know which server is you vCentre management server, that you have access to it and that the back end is of course SQL Server. The script will return all the information about your hosts from a view called databasename.dbo.vpxv_vms. This I have found usefull for many things, as all our SQL Servers have SQL in the name I can keep count of how many virtual SQL servers I have what OS version they are running on what resources are allocated to them etc etc. All you need to do is change the database context.
/*
-----------------------------------------------------------------
Hosts on vCentre
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.com
-----------------------------------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-----------------------------------------------------------------
*/
-- Change db context
USE DatabaseNameHere;
GO
-- Get hosts on vCentre
SELECT
VMID,
NAME,
VMGROUPID,
HOSTID,
CONFIGFILENAME,
VMUNIQUEID,
RESOURCE_GROUP_ID,
MEM_SIZE_MB,
NUM_VCPU,
BOOT_TIME,
SUSPEND_TIME,
POWER_STATE,
GUEST_OS,
GUEST_FAMILY,
GUEST_STATE,
MEMORY_RESERVATION,
MEMORY_OVERHEAD,
CPU_RESERVATION,
DNS_NAME,
IP_ADDRESS,
VMMWARE_TOOL,
Tools_Version,
NUM_NIC,
NUM_DISK,
IS_TEMPLATE,
[DESCRIPTION],
ANNOTATION,
SUSPEND_INTERVAL,
AGGR_COMMITED_STORAGE_SPACE,
AGGR_UNCOMMITED_STORAGE_SPACE,
AGGR_UNSHARED_STORAGE_SPACE,
STORAGE_SPACE_UPDATED_TIME
FROM
dbo.vpxv_vms
ORDER BY NAME ASC;
GO
Enjoy!
Chris
