Pages: [1]
« previous next »
Print
Author Topic: Environment at a glace report (outputs to excel) with color highlighting  (Read 354 times)
esarakaitis
Administrator
Full Member
*****
Posts: 241


8223109 sack57@hotmail.com littleking57 rootinfortwayne
View Profile WWW Email
« on: June 02, 2010, 02:27:20 PM »
Code:
$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","vctest02")

foreach ($VC in $VCList) {
Connect-viserver -server $VC
}

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", "% Allocated N-1", "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,10).Value() = "$(($VMClusterGuestList | Measure-Object).Count)"
                    } else {
                        $objWorksheet.Cells.Item($rowCount,4).Value() = "0"
                        $objWorksheet.Cells.Item($rowCount,7).Value() = "0"
                        $objWorksheet.Cells.Item($rowCount,10).Value() = "0"
                    }
                } else {
                    $objWorksheet.Cells.Item($rowCount,4).Value() = "0"
                    $objWorksheet.Cells.Item($rowCount,7).Formula = "0"
                    $objWorksheet.Cells.Item($rowCount,10).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,9).Formula = "=IF(F$rowCount=0, 0, G$rowCount/(F$rowCount-(F$rowCount/B$rowCount)))"
                $objWorksheet.Cells.Item($rowCount,11).Formula = "=IF(J$rowCount=0, 0, G$rowCount/J$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 = "=IF(F$rowCount=0, 0, G$rowCount/(F$rowCount-((F$rowCount/B$rowCount)*($($endRow - 1)))))"
        $objWorksheet.Cells.Item($rowCount,10).Formula = "=SUM(J2:J$endRow)"
        $objWorksheet.Cells.Item($rowCount,11).Formula = "=IF(J$rowCount=0, 0, G$rowCount/J$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"
        $objWorksheet.range("K2:K$endRow").NumberFormat = "0.00"
        
        $objWorksheet.range("I2:I$endRow").FormatConditions.AddColorScale(3)  | out-Null
        $objWorksheet.range("I2:I$endRow").FormatConditions.Item($objWorksheet.range("I2:I$endRow").FormatConditions.Count).SetFirstPriority  | out-Null
        $objWorksheet.range("I2:I$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(1).Type = 0
        $objWorksheet.range("I2:I$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(1).Value = .6
        $objWorksheet.range("I2:I$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(1).FormatColor.Color = 8109667
        $objWorksheet.range("I2:I$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(1).FormatColor.TintAndShade = 0
        $objWorksheet.range("I2:I$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(2).Type = 0
        $objWorksheet.range("I2:I$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(2).Value = .75
        $objWorksheet.range("I2:I$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(2).FormatColor.Color = 8711167
        $objWorksheet.range("I2:I$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(2).FormatColor.TintAndShade = 0
        $objWorksheet.range("I2:I$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(3).Type = 0
        $objWorksheet.range("I2:I$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(3).Value = .9
        $objWorksheet.range("I2:I$endRow").FormatConditions.Item(1).ColorScaleCriteria.Item(3).FormatColor.Color = 7039480
        $objWorksheet.range("I2:I$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", "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.range("C$($datastoreRow):C$($endRow)").FormatConditions.AddColorScale(3)  | out-Null
            $objWorksheet.range("C$($datastoreRow):C$($endRow)").FormatConditions.Item($objWorksheet.range("I2:I$endRow").FormatConditions.Count).SetFirstPriority  | out-Null
            $objWorksheet.range("C$($datastoreRow):C$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(1).Type = 0
            $objWorksheet.range("C$($datastoreRow):C$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(1).Value = .1
            $objWorksheet.range("C$($datastoreRow):C$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(1).FormatColor.Color = 7039480
            $objWorksheet.range("C$($datastoreRow):C$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(1).FormatColor.TintAndShade = 0
            $objWorksheet.range("C$($datastoreRow):C$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(2).Type = 0
            $objWorksheet.range("C$($datastoreRow):C$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(2).Value = .15
            $objWorksheet.range("C$($datastoreRow):C$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(2).FormatColor.Color = 8711167
            $objWorksheet.range("C$($datastoreRow):C$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(2).FormatColor.TintAndShade = 0
            $objWorksheet.range("C$($datastoreRow):C$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(3).Type = 0
            $objWorksheet.range("C$($datastoreRow):C$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(3).Value = .2
            $objWorksheet.range("C$($datastoreRow):C$($endRow)").FormatConditions.Item(1).ColorScaleCriteria.Item(3).FormatColor.Color = 8109667
            $objWorksheet.range("C$($datastoreRow):C$($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

$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"
Logged
pcli
Newbie
*
Posts: 8


View Profile
« Reply #1 on: July 05, 2010, 11:13:05 PM »
Hi ,

The script Stops with the error mentioned below. I have office 2007 installed on Win XP with PS 2.0

Exception setting "HorizontalAlignment": "Unable to set the HorizontalAlignment property of the Range class"
At :line:186 char:45
+         $objWorksheet.UsedRange.EntireColumn. <<<< HorizontalAlignment = $xlConstants::xl
Logged
esarakaitis
Administrator
Full Member
*****
Posts: 241


8223109 sack57@hotmail.com littleking57 rootinfortwayne
View Profile WWW Email
« Reply #2 on: July 06, 2010, 04:33:23 PM »
I too am using office 2007 on win xp with PS 2.0, are you able to re-run?
Logged
pcli
Newbie
*
Posts: 8


View Profile
« Reply #3 on: July 07, 2010, 01:34:55 AM »
Quote from: esarakaitis on July 06, 2010, 04:33:23 PM
I too am using office 2007 on win xp with PS 2.0, are you able to re-run?


Its the same error

Exception setting "HorizontalAlignment": "Unable to set the HorizontalAlignment property of the Range class"
At :line:186 char:45
+         $objWorksheet.UsedRange.EntireColumn. <<<< HorizontalAlignment = $xlConstants::xlRight

Thanks
Logged
esarakaitis
Administrator
Full Member
*****
Posts: 241


8223109 sack57@hotmail.com littleking57 rootinfortwayne
View Profile WWW Email
« Reply #4 on: July 07, 2010, 07:12:09 AM »
I re-ran on my pc at home, and another PC here at the office, I'm unable to duplicate it Sad

here is the exact code that I used:

Code:
$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) {
    ([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() | 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"
Logged
ewannema
Administrator
Newbie
*****
Posts: 30


View Profile WWW
« Reply #5 on: July 07, 2010, 10:21:27 AM »
This may be a problem with the Excel installation on your computer. Try the following scaled down code to see if you get an error. I get an error similar to yours if line 3 has a problem. You will get an error message with this code if the Excel Constants can not be created.

Also, if possible, try it on another computer.

Code:
$objExcel = new-object -comobject excel.application
$objExcel.Visible = $True
$xlConstants = "microsoft.office.interop.excel.Constants" -as [type]
if (!$xlConstants) { throw "Error getting xlConstants"}

$objWorkbook = $objExcel.Workbooks.Add()
$objWorksheet = $objWorkbook.Worksheets.Item(1)
$objWorksheet.Cells.Item(1,1) = "TestData00"
$objWorksheet.Cells.Item(1,2) = "TestData01"
$objWorksheet.Cells.Item(2,1) = "TestData02"
$objWorksheet.Cells.Item(2,2) = "TestData03"

$objWorksheet.UsedRange.EntireColumn.HorizontalAlignment = $xlConstants::xlRight
#$objWorkbook.SaveAs("c:\temp.xlsx")
Logged
ewannema
Administrator
Newbie
*****
Posts: 30


View Profile WWW
« Reply #6 on: July 07, 2010, 10:23:07 AM »
ES

You should probably include Disconnect-ViServer code in here if you are going to do the Connect. Otherwise you will leave older connections just hanging around until they time out.

Also, I had problems with the filename in the save on this one. Not sure why.
Logged
Pages: [1]
Print
« previous next »
Jump to: