VM ESX host allocation for Vcenter

Milton.Harris

I would like to see this query become a canned report under Reports > Quick Reports > Configuration section >  and maybe should be entitled something  to the effect, 'VM Host Resource to agent mapping' :

select b.devicehostname, count(a.devicehostname), sum(c.vm_count), sum (c.vm_running_count) from devices a inner join devices b on a.parentdeviceid=b.deviceid inner join vmware_stats_discovery_host c on a.deviceid=c.deviceid and c.time >= (CURTIME()*1000 - 86400000) where a.hostos like '%vmware esx%' and a.retire=0 group by b.devicehostname; 

Alternatively for now, you can do http://ip address of your STM server:9000//RunScriptServlet and issue the same query above and it outputs a nice table showing you how many esx hosts you have assigned to which agent as resource. (Works great if you have alot of ESX Hosts in your Vcenter and you're having a hard time determining which has 700 esx hosts assigned whereas another may have 70 and you need to load balance allocation and divvy them out evenly)

Milton.Harris

select b.devicehostname, count(a.devicehostname), sum(c.vm_count), sum(c.vm_running_count) from devices a inner join devices b on a.parentdeviceid=b.deviceid inner join vmware_stats_discovery_host c on a.deviceid=c.deviceid and c.time >= (CURTIME()*1000 - 86400000) where a.hostos like '%vmware esx%' and a.retire=0 group by b.devicehostname;