Environment at a glace report (outputs to excel) with color highlighting

Environment at a glace report (outputs to excel) with color highlighting

$initalTime = Get-Date
$filepath = "C:\tmp"
$filename = "forecast.xlsx"
$date = Get-Date ($initalTime) -uformat %Y%m%d
$time = Get-Date ($initalTime) -uformat %H%M

$VCList = @("vctest01")

function Get-VMClusterHostsNames ($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) {
    ([http://System.Runtime.InteropServices.Marshal|http://System.Runtime.InteropServices.Marshal]::ReleaseComObject([http://System.__ComObject|http://System.__ComObject]$ref) -gt 0)
    [http://System.GC|http://System.GC]::Collect()
    [http://System.GC|http://System.GC]::WaitForPendingFinalizers() 
}
#-----------------------------------------------------

[http://reflection.assembly|http://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() | out-Null
    }
}

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 = [regex]::Matches($VC, "(^[^.]+)")
        Write-Host "$(Get-Date -uformat %H:%M:%S) - $($i) of $(($VCList | Measure-Object).Count) - $($VC)"
        #setup columns
        $clusterColumns = @("Cluster Name", "Num Guests", "Num Hosts", "Total Cpu Capacity GHz", "Total Cpu Demand GHz", "% CPU Demand", "Total Memory Capacity GB", "Total Effective Memory GB", "Allocated Memory GB", "% Allocated", "% Allocated N-1", "Avg Allocated Guest Ram GB", "Estimated Allocated More Guests", "Host Memory Usage GB", "% Host Memory Used", "% Host Memory Used N-1", "Avg Host Used Guest Ram GB", "Estimated Host Used More Guests")
        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++
                #ClusterName
                $objWorksheet.Cells.Item($rowCount,1).Value() = "$($VMCluster.Name)"
                #NumHosts
                $objWorksheet.Cells.Item($rowCount,3).Value() = "$($VMCluster.Summary.NumHosts)"
                #TotalCpuCapacityGHz
                $objWorksheet.Cells.Item($rowCount,4).Formula = "=($($VMCluster.Summary.TotalCpu)/1000)"
                #TotalMemoryCapacityGB
                $objWorksheet.Cells.Item($rowCount,7).Formula = "=($($VMCluster.Summary.TotalMemory)/(1024^3))"
                #TotalEffectiveMemoryGB
                $objWorksheet.Cells.Item($rowCount,8).Formula = "=($($VMCluster.Summary.EffectiveMemory)/1024)"
                $VMHostNameList = Get-VMClusterHostsNames $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 Config | Select-Object -ExpandProperty Hardware | Measure-Object -Property MemoryMB -Sum).Sum
                        $VMClusterHostMemoryUsage = ($VMClusterGuestList | Select-Object -ExpandProperty Summary | Select-Object -ExpandProperty QuickStats | Measure-Object -Property HostMemoryUsage -Sum).Sum
                        #$VMClusterGuestMemoryUsage = ($VMClusterGuestList | Select-Object -ExpandProperty Summary | Select-Object -ExpandProperty QuickStats | Measure-Object -Property GuestMemoryUsage -Sum).Sum
                        #NumGuests
                        $objWorksheet.Cells.Item($rowCount,2).Value() = "$(($VMClusterGuestList | Measure-Object).Count)"
                        #TotalCpuDemandGHz
                        $objWorksheet.Cells.Item($rowCount,5).Formula = "=($($VMClusterOverallCpuDemand)/(1000))"
                        #AllocatedMemoryGB
                        $objWorksheet.Cells.Item($rowCount,9).Formula = "=($($VMClusterAllocatedGuestMemoryMB)/(1024))"
                        #HostMemoryUsageGB
                        $objWorksheet.Cells.Item($rowCount,14).Formula = "=($($VMClusterHostMemoryUsage)/(1024))"
                    } else {
                        $objWorksheet.Cells.Item($rowCount,2).Value() = "0"
                        $objWorksheet.Cells.Item($rowCount,5).Value() = "0"
                        $objWorksheet.Cells.Item($rowCount,9).Value() = "0"
                        $objWorksheet.Cells.Item($rowCount,14).Value() = "0"
                    }
                } else {
                    $objWorksheet.Cells.Item($rowCount,2).Value() = "0"
                    $objWorksheet.Cells.Item($rowCount,5).Value() = "0"
                    $objWorksheet.Cells.Item($rowCount,9).Value() = "0"
                    $objWorksheet.Cells.Item($rowCount,14).Value() = "0"
                }
                #% CPU Demand
                $objWorksheet.Cells.Item($rowCount,6).Formula = "=IF(D$rowCount=0, 0, E$rowCount/D$rowCount)"
                #% Allocated
                $objWorksheet.Cells.Item($rowCount,10).Formula = "=IF(H$rowCount=0, 0, I$rowCount/H$rowCount)"
                #% Allocated N-1
                $objWorksheet.Cells.Item($rowCount,11).Formula = "=IF(H$rowCount=0, 0, I$rowCount/(H$rowCount-(H$rowCount/C$rowCount)))"
                #Avg Allocated Guest Ram GB
                $objWorksheet.Cells.Item($rowCount,12).Formula = "=IF(B$rowCount=0, 0, I$rowCount/B$rowCount)"
                #Estimated Allocated More Guests
                $objWorksheet.Cells.Item($rowCount,13).Formula = "=IF(B$rowCount=0, 0, ((((H$rowCount-(H$rowCount/C$rowCount))*(0.9))-I$rowCount)/L$rowCount))"
                #% Host Memory Used
                $objWorksheet.Cells.Item($rowCount,15).Formula = "=IF(H$rowCount=0, 0, N$rowCount/H$rowCount)"
                #% Host Memory Used N-1
                $objWorksheet.Cells.Item($rowCount,16).Formula = "=IF(H$rowCount=0, 0, N$rowCount/(H$rowCount-(H$rowCount/C$rowCount)))"
                #Avg Host Used Guest Ram GB
                $objWorksheet.Cells.Item($rowCount,17).Formula = "=IF(B$rowCount=0, 0, N$rowCount/B$rowCount)"
                #Estimated Host Used More Guests
                $objWorksheet.Cells.Item($rowCount,18).Formula = "=IF(B$rowCount=0, 0, ((((H$rowCount-(H$rowCount/C$rowCount))*(0.9))-N$rowCount)/Q$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 = "=SUM(E2:E$endRow)"
        $objWorksheet.Cells.Item($rowCount,6).Formula = "=IF(D$rowCount=0, 0, E$rowCount/D$rowCount)"
        $objWorksheet.Cells.Item($rowCount,7).Formula = "=SUM(G2:G$endRow)"
        $objWorksheet.Cells.Item($rowCount,8).Formula = "=SUM(H2:H$endRow)"
        $objWorksheet.Cells.Item($rowCount,9).Formula = "=SUM(I2:I$endRow)"
        $objWorksheet.Cells.Item($rowCount,10).Formula = "=IF(H$rowCount=0, 0, I$rowCount/H$rowCount)"
        $objWorksheet.Cells.Item($rowCount,11).Formula = "=IF(H$rowCount=0, 0, I$rowCount/(H$rowCount-(H$rowCount/C$rowCount)))"
        $objWorksheet.Cells.Item($rowCount,12).Formula = "=IF(B$rowCount=0, 0, I$rowCount/B$rowCount)"
        $objWorksheet.Cells.Item($rowCount,13).Formula = "=IF(B$rowCount=0, 0, ((((H$rowCount-(H$rowCount/C$rowCount))*(0.9))-I$rowCount)/L$rowCount))"
        $objWorksheet.Cells.Item($rowCount,14).Formula = "=SUM(N2:N$endRow)"
        $objWorksheet.Cells.Item($rowCount,15).Formula = "=IF(H$rowCount=0, 0, N$rowCount/H$rowCount)"
        $objWorksheet.Cells.Item($rowCount,16).Formula = "=IF(H$rowCount=0, 0, N$rowCount/(H$rowCount-(H$rowCount/C$rowCount)))"
        $objWorksheet.Cells.Item($rowCount,17).Formula = "=IF(B$rowCount=0, 0, N$rowCount/B$rowCount)"
        $objWorksheet.Cells.Item($rowCount,18).Formula = "=IF(B$rowCount=0, 0, ((((H$rowCount-(H$rowCount/C$rowCount))*(0.9))-N$rowCount)/Q$rowCount))"

        $endRow = $objWorksheet.UsedRange.Rows.Count

        $objWorksheet.range("B2:B$endRow").NumberFormat = "0"
        $objWorksheet.range("C2:C$endRow").NumberFormat = "0"
        $objWorksheet.range("D2:D$endRow").NumberFormat = "0"
        $objWorksheet.range("E2:E$endRow").NumberFormat = "0"
        $objWorksheet.range("F2:F$endRow").NumberFormat = "0%"
        $objWorksheet.range("G2: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%"
        $objWorksheet.range("K2:K$endRow").NumberFormat = "0%"
        $objWorksheet.range("L2:L$endRow").NumberFormat = "0.00"
        $objWorksheet.range("M2:M$endRow").NumberFormat = "0"
        $objWorksheet.range("N2:N$endRow").NumberFormat = "0"
        $objWorksheet.range("O2:O$endRow").NumberFormat = "0%"
        $objWorksheet.range("P2:P$endRow").NumberFormat = "0%"
        $objWorksheet.range("Q2:Q$endRow").NumberFormat = "0.00"
        $objWorksheet.range("R2:R$endRow").NumberFormat = "0"

        $objWorksheet.range("1:1").VerticalAlignment = $xlConstants::xlBottom
        $objWorksheet.range("1:1").WrapText = $True
        $objWorksheet.range("1:1").Orientation = "90"
        $objWorksheet.range("1:1").ReadingOrder = $xlConstants::xlContext

        $objWorksheet.range("K2:K$endRow").FormatConditions.AddColorScale(3)  | out-Null
        $objWorksheet.range("K2:K$endRow").FormatConditions.Item($objWorksheet.range("K2:K$endRow").FormatConditions.Count).SetFirstPriority  | out-Null
        $objWorksheet.range("K2:K$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(1).Type = 0
        $objWorksheet.range("K2:K$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(1).Value = .6
        $objWorksheet.range("K2:K$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(1).FormatColor.Color = 8109667
        $objWorksheet.range("K2:K$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(1).FormatColor.TintAndShade = 0
        $objWorksheet.range("K2:K$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(2).Type = 0
        $objWorksheet.range("K2:K$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(2).Value = .75
        $objWorksheet.range("K2:K$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(2).FormatColor.Color = 8711167
        $objWorksheet.range("K2:K$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(2).FormatColor.TintAndShade = 0
        $objWorksheet.range("K2:K$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(3).Type = 0
        $objWorksheet.range("K2:K$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(3).Value = .9
        $objWorksheet.range("K2:K$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(3).FormatColor.Color = 7039480
        $objWorksheet.range("K2:K$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(3).FormatColor.TintAndShade = 0

        $objWorksheet.range("P2:P$endRow").FormatConditions.AddColorScale(3)  | out-Null
        $objWorksheet.range("P2:P$endRow").FormatConditions.Item($objWorksheet.range("P2:P$endRow").FormatConditions.Count).SetFirstPriority  | out-Null
        $objWorksheet.range("P2:P$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(1).Type = 0
        $objWorksheet.range("P2:P$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(1).Value = .6
        $objWorksheet.range("P2:P$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(1).FormatColor.Color = 8109667
        $objWorksheet.range("P2:P$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(1).FormatColor.TintAndShade = 0
        $objWorksheet.range("P2:P$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(2).Type = 0
        $objWorksheet.range("P2:P$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(2).Value = .75
        $objWorksheet.range("P2:P$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(2).FormatColor.Color = 8711167
        $objWorksheet.range("P2:P$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(2).FormatColor.TintAndShade = 0
        $objWorksheet.range("P2:P$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(3).Type = 0
        $objWorksheet.range("P2:P$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(3).Value = .9
        $objWorksheet.range("P2:P$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(3).FormatColor.Color = 7039480
        $objWorksheet.range("P2:P$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(3).FormatColor.TintAndShade = 0

        $VMDatastoreList = Get-View -Server $VC -ViewType Datastore -Filter @{"Summary.Type"="VMFS"; "Summary.MultipleHostAccess"="True"} | Sort-Object Name
        if (($VMDatastoreList | Measure-Object).Count -gt 0) {
            $rowCount = $rowCount+4
            $datastoreRow = $rowCount +1
            $datastoreColumns = @("DatastoreName", "TotalCapacityGB", "FreeSpaceGB", "% Free")
            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.Capacity)/(1024^3))"
                $objWorksheet.Cells.Item($rowCount,3).Formula = "=($($VMDatastore.Summary.FreeSpace)/(1024^3))"
                $objWorksheet.Cells.Item($rowCount,4).Formula = "=(C$($rowCount)/B$($rowCount))"
            }
            $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 = "=SUM(C$($datastoreRow):C$($endRow))"
            $objWorksheet.Cells.Item($rowCount,4).Formula = "=(C$($rowCount)/B$($rowCount))"
            $endRow = $objWorksheet.UsedRange.Rows.Count
            $objWorksheet.range("C$($datastoreRow):C$($endRow)").NumberFormat = "0"
            $objWorksheet.range("D$($datastoreRow):D$($endRow)").NumberFormat = "0%"
            $objWorksheet.range("B$($datastoreRow):B$($endRow)").NumberFormat = "0"

            $objWorksheet.range("D$($datastoreRow):D$($endRow)").FormatConditions.AddColorScale(3)  | out-Null
            $objWorksheet.range("D$($datastoreRow):D$($endRow)").FormatConditions.Item($objWorksheet.range("D2:D$endRow").FormatConditions.Count).SetFirstPriority  | out-Null
            $objWorksheet.range("D$($datastoreRow):D$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(1).Type = 0
            $objWorksheet.range("D$($datastoreRow):D$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(1).Value = .1
            $objWorksheet.range("D$($datastoreRow):D$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(1).FormatColor.Color = 7039480
            $objWorksheet.range("D$($datastoreRow):D$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(1).FormatColor.TintAndShade = 0
            $objWorksheet.range("D$($datastoreRow):D$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(2).Type = 0
            $objWorksheet.range("D$($datastoreRow):D$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(2).Value = .15
            $objWorksheet.range("D$($datastoreRow):D$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(2).FormatColor.Color = 8711167
            $objWorksheet.range("D$($datastoreRow):D$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(2).FormatColor.TintAndShade = 0
            $objWorksheet.range("D$($datastoreRow):D$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(3).Type = 0
            $objWorksheet.range("D$($datastoreRow):D$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(3).Value = .2
            $objWorksheet.range("D$($datastoreRow):D$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(3).FormatColor.Color = 8109667
            $objWorksheet.range("D$($datastoreRow):D$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(3).FormatColor.TintAndShade = 0
        }
        $objWorksheet.UsedRange.EntireColumn.HorizontalAlignment = $xlConstants::xlRight
        $objWorksheet.Cells.Item(1,1).EntireColumn.HorizontalAlignment = $xlConstants::xlLeft
        $objWorksheet.UsedRange.EntireColumn.AutoFit() | out-Null
    }
}

$objWorkbook.SaveAs("$($filepath)\$($date)$($time)-$($filename)")

$objExcel.Visible = $True
Release-Ref($objWorksheet) | out-Null
Release-Ref($objWorkbook) | out-Null
Release-Ref($objExcel) | out-Null

$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"

Comments

Great script.

Is it possible to attach the script as a file ?

The forum SW doesn't like square brackets Smiley Sad

____________

Blog: LucD notes

Twitter: lucd22

Yes please, can you attach the script?

what do i need to do to run that script ? delete the square brackets or replace them?

i'm still beginner.

Did anyone get this script?  I have tried cleaning it up with no luck so far.

Version history
Revision #:
1 of 1
Last update:
‎07-01-2010 06:01 AM
Updated by: