Problems with Vmware: VMhost available capacity report

[Artur_Mucha]

Hi,

I've prepared report which shows me on which VMware ESXi host I have free resources to provision another VM. Code of this report is below:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblVmwareInfo.Version,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  Cast(Round(tblAssets.Memory / 1024, 2) As decimal(10,2)) As [VMhost RAM],
  tblVmwareInfo.numCpuThreads As [VMhost CPUs],
  Cast(Round((Sum(tblVmwareDisk.TotalSpace) / Count(tblVmwareGuest.GuestID)) /
  (1024 * 1024 * 1024), 2) As decimal(10,2)) As [VMhost HDD],
  Count(tblVmwareGuest.GuestID) As [Provisioned VMs],
  Cast(Round(Sum(tblVmwareGuest.Memory) / 1024, 2) As decimal(10,2)) As
  [Provisioned RAM],
  Sum(tblVmwareGuest.CpuCount) As [Provisioned CPUs],
  Cast(Round((tblAssets.Memory - Sum(tblVmwareGuest.Memory)) / 1024,
  2) As decimal(10,2)) As [Free RAM],
  tblVmwareInfo.numCpuThreads - Sum(tblVmwareGuest.CpuCount) As [Free CPUs],
  Cast(Round((Sum(tblVmwareDisk.FreeSpace) / Count(tblVmwareGuest.GuestID)) /
  (1024 * 1024 * 1024), 2) As decimal(10,2)) As [Free HDD],
  Case
    When (tblAssets.Memory - Sum(tblVmwareGuest.Memory) > 0 And
    (tblVmwareInfo.numCpuThreads - Sum(tblVmwareGuest.CpuCount)) > 0 And
    Sum(tblVmwareDisk.FreeSpace) > 0) Then '#ccffcc' End As backgroundcolor
From tblAssets
  Inner Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
  Inner Join tblVmwareDisk On tblAssets.AssetID = tblVmwareDisk.AssetID
  Inner Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
  Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
    And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where tblVmwareGuest.IsRunning Like '%poweredOn%'
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tblVmwareInfo.Version,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblVmwareInfo.numCpuThreads,
  tblAssets.Memory,
  tblAssets.IPNumeric
Order By tblAssets.IPNumeric

But I have one problem with this report: it shows only ESXis with provisioned VMs. If I have new, empty ESXi without any VM provisioned it would not be shown in report until first VM will appear.

Is there any way to fix it?

(I find this report very useful, feel free to use it) 🙂

Best Regards,
Artur Mucha

[Artur_Mucha]

Anyone could help me here?