In a previous post, I mentioned that without the VI Toolkit there is no real way of extracting information from the VC database.
This isn’t entirely true. Before I discovered the toolkit, I was using SQL to query the Virtualcenter database directly.
List XP Machines and their RAM allocation
|SELECT VPX_VM.GUEST_OS, VPX_VM.MEM_SIZE_MB FROM VC_DB.dbo.VPX_VM VPX_VM WHERE(VPX_VM.GUEST_OS='winXPProGuest')
List Information about Templates
|SELECT VPX_VM.LOCAL_FILE_NAME, VPX_VM.GUEST_OS, VPX_VM.IS_TEMPLATE, VPX_VM.MEM_SIZE_MB, VPX_VM.HOST_ID, VPX_VM.ID FROM VC_DB.dbo.VPX_VM VPX_VM WHERE(VPX_VM.IS_TEMPLATE=1)ORDERBY VPX_VM.LOCAL_FILE_NAME
Host names and memory
|SELECT VPX_HOST.ID, VPX_HOST.DNS_NAME,CAST(VPX_HOST.MEM_SIZE ASBIGINT)FROM VC_DB.dbo.VPX_HOST VPX_HOST ORDERBY VPX_HOST.DNS_NAME
These were based on ideas from Wayne’s World of IT. While it’s a lot less friendly to work with, the advantage is that it’s a lot quicker than the VI Toolkit’s Get- commands, and I still use them from time-to-time.
|SELECT VPXVM.LOCALFILENAME, VPXVM.DNSNAME, VPXVM.GUESTOS, VPXVM.ISTEMPLATE, VPXVM.IPADDRESS, VPXVM.MEMSIZEMB, VPXVM.GUESTSTATE, VPXVM.POWERSTATE, VPXVM.HOSTID, VPXVM.ID, VPXVM.BOOTTIME FROM VCDB.dbo.VPXVM VPXVM WHERE(VPXVM.ISTEMPLATE<>;1)ORDERBY VPXVM.DNSNAME, VPXVM.LOCALFILE_NAME
Although you could use this approach to modify entries in the database, I would only ever feel comfortable using this to extract information.