select  b.IP_ADDRESS 宿主机IP,count(1) 虚拟机总数,b.host_model 宿主机型号,
COUNT(CASE WHEN GUEST_STATE='Running' THEN 1 ELSE NULL END ) AS 在运行虚拟机数目,
COUNT(CASE WHEN GUEST_STATE='notRunning' THEN 1 ELSE NULL END ) AS 未运行虚拟机数目

 from [10.24.15.15].[vcenter].dbo.VPX_VM a join [10.24.15.15].[vcenter].dbo.VPX_HOST b    on a.host_id = b.id    
 group by  b.IP_ADDRESS order by 1 asc