Try this one.
You will need to put in the DeviceID of the VCentre that you are checking the ESX hosts from. You can get this by opening All Devices, filter by VMware VC and hover the cursor over the link to the vCentre. The DeviceID will be in the target URL in the status bar of the browser. Replace *** (after fromId in the query) with the DeviceID of the vCentre.
SELECT esx_hosts.DeviceHostName as HostName,esx_hosts.DeviceIPAddress as Host_IP_Address,vm_count as Total_VMs,vm_running_count as VMs_Running, if (cpu_overall_usage_mhz > (cpu_core_count*cpu_mhz_average),100,cpu_overall_usage_mhz*100/(cpu_core_count*cpu_mhz_average)) CPU, if (mem_overall_usage > physical_mem_size_bytes,100,mem_overall_usage*100/physical_mem_size_bytes) RAM FROM devices esx_hosts inner join `device_to_agent_types` b on esx_hosts.deviceid = b.deviceid INNER JOIN vmware_relationships ON esx_hosts.DeviceId = toId AND type = 3 AND HostOS = 'VMware ESX' AND Retire = 0 AND fromId = *** INNER JOIN vmware_stats_host_current vah ON vah.DeviceId = esx_hosts.DeviceId ORDER BY CPU DESC LIMIT 10
Exactly what i was looking for