$initalTime = Get-Date
$filepath = "C:\tmp"
$filename = "forecast"
$date = Get-Date ($initalTime) -uformat %Y%m%d
$time = Get-Date ($initalTime) -uformat %H%M
$VCList = $global:defaultviservers
function Get-VMClusterHosts ($VMCluster) {
$VMClusterHostNames = Get-View -Server $VC -ViewType ClusterComputeResource -Filter @{"Name"=$VMCluster.Name} | Select-Object -ExpandProperty Host | Select-Object -ExpandProperty Value
$VMHostNameLoopCount = 0
$VMHostNameList = ""
if (($VMClusterHostNames | Measure-Object).Count -gt 0) {
foreach ($VMHostName in $VMClusterHostNames) {
$VMHostNameLoopCount++
if ($VMHostNameLoopCount -gt 1) {
$VMHostNameList += "|^"+$VMHostName+"$"
} else {
$VMHostNameList += "^"+$VMHostName+"$"
}
}
}
return $VMHostNameList
}
Write-Host "$(Get-Date ($initalTime) -uformat %H:%M:%S) - Starting"
#-----------------------------------------------------
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
#-----------------------------------------------------
[reflection.assembly]::loadWithPartialname("Microsoft.Office.Interop.Excel") | out-Null
$xlConstants = "microsoft.office.interop.excel.Constants" -as [type]
$objExcel = new-object -comobject excel.application
#$objExcel.Visible = $True
$objWorkbook = $objExcel.Workbooks.Add()
while ($objWorkbook.Worksheets.Count -ne $VCList.Count) {
if ($objWorkbook.Worksheets.Count -gt $VCList.Count) {
$objWorkbook.Worksheets.Item($objWorkbook.Worksheets.Count).Delete()
} elseif ($objWorkbook.Worksheets.Count -lt $VCList.Count) {
$objWorkbook.Worksheets.Add()
}
}
Write-Host "$(Get-Date -uformat %H:%M:%S) - $(($VCList | Measure-Object).Count) VC(s) acquired"
$i = 0
if ((($VCList | Measure-Object).Count) -gt 0) {
ForEach ($VC in ($VCList | Sort-Object)) {
$i++
$rowCount = 1
$objWorksheet = $objWorkbook.Worksheets.Item($i)
$objWorksheet.Name = $VC
Write-Host "$(Get-Date -uformat %H:%M:%S) - $($i) of $(($VCList | Measure-Object).Count) - $($VC)"
#setup columns
$clusterColumns = @("ClusterName", "NumHosts", "TotalCpuCapacityGHz", "TotalCpuDemandGHz", "% CPU Demand", "TotalMemoryCapacityGB", "AllocatedMemoryGB", "% Allocated", "NumGuests", "AvgGuestRamGB")
foreach ($clusterColumn in $clusterColumns) {
$j = 0..($clusterColumns.length - 1) | ? {$clusterColumns[$_] -eq $clusterColumn}
$objWorksheet.Cells.Item($rowCount,($j+1)).Value() = "$clusterColumn"
}
$VMClusterList = Get-View -Server $VC -ViewType ClusterComputeResource | Sort-Object Name
if (($VMClusterList | Measure-Object).Count -gt 0) {
ForEach ($VMCluster in $VMClusterList) {
$rowCount++
$objWorksheet.Cells.Item($rowCount,1).Value() = "$($VMCluster.Name)"
$objWorksheet.Cells.Item($rowCount,2).Value() = "$($VMCluster.Summary.NumHosts)"
$objWorksheet.Cells.Item($rowCount,3).Formula = "=($($VMCluster.Summary.TotalCpu)/1000)"
$objWorksheet.Cells.Item($rowCount,6).Formula = "=($($VMCluster.Summary.TotalMemory)/(1024^3))"
$VMHostNameList = Get-VMClusterHosts $VMCluster
if ($VMHostNameList.Length -gt 0) {
$VMClusterGuestList = Get-View -Server $VC -ViewType VirtualMachine -Filter @{"Runtime.Host"=$VMHostNameList; "Runtime.PowerState"="poweredOn"; "Config.Template"="False"} | Sort-Object Name
if (($VMClusterGuestList | Measure-Object).Count -gt 0) {
$VMClusterOverallCpuDemand = ($VMClusterGuestList | Select-Object -ExpandProperty Summary | Select-Object -ExpandProperty QuickStats | Measure-Object -Property OverallCpuDemand -Sum).Sum
#$VMClusterAllocatedGuestMemoryMB = ($VMClusterGuestList | Select-Object -ExpandProperty Summary | Select-Object -ExpandProperty QuickStats | Measure-Object -Property HostMemoryUsage -Sum).Sum
$VMClusterAllocatedGuestMemoryMB = ($VMClusterGuestList | Select-Object -ExpandProperty Config | Select-Object -ExpandProperty Hardware | Measure-Object -Property MemoryMB -Sum).Sum
$objWorksheet.Cells.Item($rowCount,4).Formula = "=($($VMClusterOverallCpuDemand)/(1000))"
$objWorksheet.Cells.Item($rowCount,7).Formula = "=($($VMClusterAllocatedGuestMemoryMB)/(1024))"
$objWorksheet.Cells.Item($rowCount,9).Value() = "$(($VMClusterGuestList | Measure-Object).Count)"
} else {
$objWorksheet.Cells.Item($rowCount,4).Value() = "0"
$objWorksheet.Cells.Item($rowCount,7).Value() = "0"
$objWorksheet.Cells.Item($rowCount,9).Value() = "0"
}
} else {
$objWorksheet.Cells.Item($rowCount,4).Value() = "0"
$objWorksheet.Cells.Item($rowCount,7).Formula = "0"
$objWorksheet.Cells.Item($rowCount,9).Value() = "0"
}
$objWorksheet.Cells.Item($rowCount,5).Formula = "=IF(C$rowCount=0, 0, D$rowCount/C$rowCount)"
$objWorksheet.Cells.Item($rowCount,8).Formula = "=IF(F$rowCount=0, 0, G$rowCount/F$rowCount)"
$objWorksheet.Cells.Item($rowCount,10).Formula = "=IF(I$rowCount=0, 0, G$rowCount/I$rowCount)"
}
}
$endRow = $objWorksheet.UsedRange.Rows.Count
$rowCount = $rowCount+2
$objWorksheet.Cells.Item($rowCount,1).Value() = "Totals"
$objWorksheet.Cells.Item($rowCount,2).Formula = "=SUM(B2:B$endRow)"
$objWorksheet.Cells.Item($rowCount,3).Formula = "=SUM(C2:C$endRow)"
$objWorksheet.Cells.Item($rowCount,4).Formula = "=SUM(D2:D$endRow)"
$objWorksheet.Cells.Item($rowCount,5).Formula = "=IF(C$rowCount=0, 0, D$rowCount/C$rowCount)"
$objWorksheet.Cells.Item($rowCount,6).Formula = "=SUM(F2:F$endRow)"
$objWorksheet.Cells.Item($rowCount,7).Formula = "=SUM(G2:G$endRow)"
$objWorksheet.Cells.Item($rowCount,8).Formula = "=IF(F$rowCount=0, 0, G$rowCount/F$rowCount)"
$objWorksheet.Cells.Item($rowCount,9).Formula = "=SUM(I2:I$endRow)"
$objWorksheet.Cells.Item($rowCount,10).Formula = "=IF(I$rowCount=0, 0, G$rowCount/I$rowCount)"
$endRow = $objWorksheet.UsedRange.Rows.Count
$objWorksheet.range("C2:D$endRow").NumberFormat = "0"
$objWorksheet.range("E2:E$endRow").NumberFormat = "0%"
$objWorksheet.range("F2:G$endRow").NumberFormat = "0"
$objWorksheet.range("H2:H$endRow").NumberFormat = "0%"
$objWorksheet.range("I2:I$endRow").NumberFormat = "0"
$objWorksheet.range("J2:J$endRow").NumberFormat = "0.00"
$VMDatastoreList = Get-View -Server $VC -ViewType Datastore -Filter @{"Summary.Type"="VMFS"; "Summary.MultipleHostAccess"="True"}
if (($VMDatastoreList | Measure-Object).Count -gt 0) {
$rowCount = $rowCount+4
$datastoreRow = $rowCount +1
$datastoreColumns = @("DatastoreName", "FreeSpaceGB", "% Free", "TotalCapacityGB")
ForEach ($datastoreColumn in $datastoreColumns) {
$j = 0..($datastoreColumns.length - 1) | ? {$datastoreColumns[$_] -eq $datastoreColumn}
$objWorksheet.Cells.Item($rowCount,($j+1)).Value() = "$datastoreColumn"
}
ForEach ($VMDatastore in $VMDatastoreList) {
$rowCount++
$objWorksheet.Cells.Item($rowCount,1).Value() = "$($VMDatastore.Name)"
$objWorksheet.Cells.Item($rowCount,2).Formula = "=($($VMDatastore.Summary.FreeSpace)/(1024^3))"
$objWorksheet.Cells.Item($rowCount,3).Formula = "=(B$($rowCount)/D$($rowCount))"
$objWorksheet.Cells.Item($rowCount,4).Formula = "=($($VMDatastore.Summary.Capacity)/(1024^3))"
}
$endRow = $objWorksheet.UsedRange.Rows.Count
$rowCount = $rowCount+2
$objWorksheet.Cells.Item($rowCount,1).Value() = "Totals"
$objWorksheet.Cells.Item($rowCount,2).Formula = "=SUM(B$($datastoreRow):B$($endRow))"
$objWorksheet.Cells.Item($rowCount,3).Formula = "=(B$($rowCount)/D$($rowCount))"
$objWorksheet.Cells.Item($rowCount,4).Formula = "=SUM(D$($datastoreRow):D$($endRow))"
$endRow = $objWorksheet.UsedRange.Rows.Count
$objWorksheet.range("B$($datastoreRow):B$($endRow)").NumberFormat = "0"
$objWorksheet.range("C$($datastoreRow):C$($endRow)").NumberFormat = "0%"
$objWorksheet.range("D$($datastoreRow):D$($endRow)").NumberFormat = "0"
}
$objWorksheet.UsedRange.EntireColumn.HorizontalAlignment = $xlConstants::xlRight
$objWorksheet.Cells.Item(1,1).EntireColumn.HorizontalAlignment = $xlConstants::xlLeft
$objWorksheet.UsedRange.EntireColumn.AutoFit() | out-Null
}
}
$objExcel.Visible = $True
$a = Release-Ref($objWorksheet)
$a = Release-Ref($objWorkbook)
$a = Release-Ref($objExcel)
$conclusionTime = Get-Date
Write-Host "$(Get-Date ($conclusionTime) -uformat %H:%M:%S) - Finished"
$totalTime = New-TimeSpan $initalTime $conclusionTime
Write-Host "$($totalTime.Hours):$($totalTime.Minutes):$($totalTime.Seconds) - Total Time"
