Running SQL Queries against the Virtual Center Database

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

Guest Information

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

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.

Although you could use this approach to modify entries in the database, I would only ever feel comfortable using this to extract information.