finding orphaned vmware templates or vmtx hell

what happens when templates and parent directories haven't consistent names or reside in 
directories that share the same name, and of course, are all over the place? you sigh.  

then you "open the book".  and when i say "open the book" i mean it the way
the portuguese mean it. think sailor talk, only worse.

vmware isn't very nice in that via vsphere you can't figure out where your "assets" reside. 

that's *okay*. gui fail.  
 
various powershell cli scripts are losers, too. what you need to do is actually query
the sql db that the vcenter uses.

so, off i go to vmware's support site; and i find this:  
http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=2004139  

COOL. install more junk on my system. since this is running sql server 2008 express,   
i get this:  
http://www.microsoft.com/en-us/download/details.aspx?id=7593  

flock. i go through the totally non-intuitive install for sql server 2008 studio express.   
just install "a new instance". you're not really installing sql server 2008. i cancelled   
when i saw that under installation. i read up.  and the i went for it.  no worries. 

and then you run the tool.  

of course you've kept the name of your db and passwords handy, right?   
well, if you went with the vmware defaults, just to a network browse, connect to your   
local sql server express db. it should look like: YOUR_VCENTER\VIM_SQLEXP  
and do local auth. it is okay.  

you run this query that vmware support gave you:  

select VPX_ENTITY.NAME as "VM Name", VPX_VM.FILE_NAME as "File Name / Path"  
From VPX_VM inner join VPX_Entity   
where VPX_VM.FILE_NAME LIKE '%VMNAME%'  
on vpx_vm.ID = vpx_entity.ID   
order by VPX_entity.name;  

it barfs. holy heck. re-write the query and get expected results:  

use VIM_VCDB;  
select VPX_ENTITY.NAME as "VM Name", VPX_VM.FILE_NAME as "File Name / Path"  
From VPX_VM inner join VPX_Entity   
on vpx_vm.ID = vpx_entity.ID   
order by VPX_entity.name;  

and there you go. of course the datastores are completely unfriendly,   
but you can ssh into your client boxes and figure it out pretty quick.   
of course you can.  

 go get some coffee. you're awesome.