SQL query to list all VMs and HW version

February 22, 2011 Ed Goad

I had a need to find all of my VMware VMs of a certain hardware version in order to compare to another list. Since I have production, test, and DR virtual machines, it was also important to know the path in the VMware hierarchy so I could easily identify the VM types.

use VCenter
declare @VMs table (path varchar(500),name varchar(255), ver varchar(50), ID int, Parent int)

insert @VMs
select vci.name, vci.name, vci.version, vci.ID,ve.Parent_IDfrom vpx_vm_config_info vci
INNER JOIN vpx_entity ve on vci.ID=ve.ID

declare @counter int
set @counter=0
while @counter < 8
begin
update @VMs 
set [path]=(select top 1 name from vpx_entity ve where ve.ID=Parent) + ''+ [path]
, Parent=(select top 1 Parent_ID from vpx_entity ve where ve.ID=Parent)
where Parent is not null
set @counter = @counter + 1
end

select [path],name,ver from @VMs
order by path