→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Artur_Mucha
Engaged Sweeper II
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
1 REPLY 1
Artur_Mucha
Engaged Sweeper II
Anyone could help me here?