We pull this data directly from the the VC database via a SQL query - we have a custom view we created in the VC database (it get around some issues with DNS name versus VM name mismatches, missing DNS names, and chops the DNS suffix off but you don't have to do this). We then link this view to our CMDB so it has a live feed of which host a VM is on.
See below - should give you the basic idea......it effectively lists VMs against hosts......
SELECT TOP (100) PERCENT UPPER(SUBSTRING(dbo.VPXV_HOSTS.NAME, 1, CHARINDEX('.', dbo.VPXV_HOSTS.NAME) - 1)) AS HOST_NAME,
UPPER(SUBSTRING(dbo.VPX_VM.DNS_NAME, 1, (CASE WHEN CHARINDEX('.', dbo.VPX_VM.DNS_NAME) = 0 THEN 1 ELSE CHARINDEX('.',
dbo.VPX_VM.DNS_NAME) END) - 1)) AS VM_NAME, dbo.VPXV_VMS.NAME AS VM_Description, LOWER(dbo.VPX_VM.DNS_NAME) AS DNS_NAME
FROM dbo.VPXV_VMS INNER JOIN
dbo.VPXV_HOSTS ON dbo.VPXV_VMS.HOSTID = dbo.VPXV_HOSTS.HOSTID INNER JOIN
dbo.VPX_VM ON dbo.VPXV_VMS.VMID = dbo.VPX_VM.ID
WHERE (ISNULL(CHARINDEX('.', dbo.VPX_VM.DNS_NAME), 0) <> 0)
ORDER BY dbo.VPXV_HOSTS.NAME
