# encoding: ascii
# api: powershell
# title: VCenter Reporting
# description: where it can generate final out put excel file?
# version: 0.1
# type: script
# author: Steve Jarvi
# license: CC0
# x-poshcode-id: 5153
# x-archived: 2014-05-15T04:57:53
# x-published: 2014-05-07T12:34:00
#
#
<# .SYNOPSIS
Collects statistics and settings for Hosts, Datastores, and Virtual Machines
from multiple VCenters.
.DESCRIPTION
Creates Excel spreadsheet report with separate worksheets for Hosts, Datastores, and
Virtual Machines by VCenter.
.PARAMETER
none
.INPUTS
.OUTPUTS
.NOTES
Name: Get-SCE_VCenterStatisticsAndSettings.ps1
Author: Steve Jarvi
DateCreated: 10 Jan 2013
.EXAMPLE
#>
#Load PowerCLI snap-ins
Add-PSSnapin Vmware.VIMAutomation.Core
Write-Host "Getting first and last days of previous months..."
#Get the first and last days for the previous months.
$startdate = (($currentdate = get-date).addmonths(-1) | % {$_.AddDays(-($_.day -1))}).ToString("d")
$enddate = (($currentdate = get-date) | % {$_.adddays(-($_.day))}).ToString("d")
Write-Host "Start date is $startdate."
Write-Host "End date is $enddate."
#VCenters list:
$VCenters = "vcenter1","vcenter2","vcenter3"
#New Excel ComObject, add workbook:
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$workbook = $excel.Workbooks.Add()
#Loop through VCenters.
foreach ($VC in $VCenters){
#Connect to VCenter.
connect-viserver $VC
#Add worksheet named Guest by VCenter.
$currentsheet = $workbook.worksheets.add()
$currentsheet.name = "Guest - " + "$VC"
#Get virtual machines in current VCenter.
$VMs = Get-VM | ?{$_.powerstate -eq "PoweredOn"}
#Create multidimensional array sized by number of VMs, plus 1 for header, and number of columns:
$MDArray = New-Object 'object[,]' ($vms.count+1),14
$row = 1
$col = 0
#Enter column names for first row:
$MDArray[0,0] = "VM Name"
$MDArray[0,1] = "Resource Pool"
$MDArray[0,2] = "Allocated CPU"
$MDArray[0,3] = "Avg. CPU (MHz)"
$MDArray[0,4] = "Allocated Mem (MB)"
$MDArray[0,5] = "Avg. Mem Usage (%)"
$MDArray[0,6] = "Mem Usage (MB)"
$MDArray[0,7] = "VMDK(s) / Capacity / Type"
$MDArray[0,8] = "VMDK Avg. (KBps)"
$MDArray[0,9] = "Nic Type"
$MDArray[0,10] = "Network Avg. (KBps)"
$MDArray[0,11] = "Hrdwr Ver."
$MDArray[0,12] = "VMTools Ver."
$MDArray[0,13] = "VM Operating System"
$vmnumber = 1
$vmcount = $vms.count
#Loop through VMs in current VCenter:
foreach ($vm in $VMs){
#Progress update:
Write-Host "Currently building $vm's statistics into table."
Write-Host "Working $vmnumber of $vmcount VMs."
$vmnumber++
#Use PowerCLI's "Get-Stat" to pull VM statistics for the previous month.
$vmstats = Get-Stat -Entity $vm.name -Start $startdate -Finish $enddate -EA SilentlyContinue
#Set variables for statistics by type used:
$cpuuse = $vmstats | ?{$_.metricid -eq "cpu.usagemhz.average"}
$memuse = $vmstats | ?{$_.metricid -eq "mem.usage.average"}
$dskuse = $vmstats | ?{$_.metricid -eq "disk.usage.average"}
$netuse = $vmstats | ?{$_.metricid -eq "net.usage.average"}
#Build table of VMs' statistics and settings into multidimensional array:
$MDArray[$row,$col] = [string]($vm.name)
$col++
$MDArray[$row,$col] = [string]($vm.resourcepool.name)
$col++
$MDArray[$row,$col] = [string]($vm.NumCpu)
$col++
$MDArray[$row,$col] = [string]([Math]::Round((($cpuuse | Measure-Object Value -Average).Average),2))
$col++
$MDArray[$row,$col] = [string]$vm.MemoryMB
$col++
$MDArray[$row,$col] = [string]([Math]::Round((($memuse | Measure-Object Value -Average).Average),2))
$col++
$MDArray[$row,$col] = [string]$([math]::round((([Math]::Round((($memuse | Measure-Object Value -Average).Average),2))/100*($vm.memoryMB)),2))
$col++
$HDDSize = Get-HardDisk $vm.name | Sort Name
$HDDList = $null
foreach ($HD in $HDDSize) {
$DiskType = $HD.StorageFormat
$DiskName = $HD.Name
$DiskSize = $HD.CapacityKB / 1048576
$DiskSize = "{0:N2}" -f $DiskSize
$HDDList += "$DiskName"+"/ "+ "$DiskSize" + "/ " + "$DiskType`r`n"
}
$MDArray[$row,$col] = [string]$HDDList
$col++
$MDArray[$row,$col] = [string]([Math]::Round((($dskuse | Measure-Object Value -Average).Average),2))
$col++
$MDArray[$row,$col] = [string]$vm.NetworkAdapters.type
$col++
$MDArray[$row,$col] = [string]([Math]::Round((($netuse | Measure-Object Value -Average).Average),2))
$col++
$MDArray[$row,$col] = [string]$vm.version
$col++
Try {
$MDArray[$row,$col] = ($vm.ExtensionData.guest.ToolsVersion.tostring())
}
Catch {
$MDArray[$row,$col] = "Not Installed"
}
$col++
$MDArray[$row,$col] = [string]$vm.ExtensionData.guest.GuestFullName
$col = 0
$row++
}
#Create Excel interop object for aligning cell content:
[reflection.assembly]::loadWithPartialname("Microsoft.Office.Interop.Excel") | out-Null
$xlConstants = "microsoft.office.interop.excel.Constants" -as [type]
#Create range of Excel cells to match multidimensional array
#and add array to sheet:
$cells = "A1:N" + ($vms.count+1)
$sheetname = "Guest - " + "$VC"
$worksheet = $workbook.Worksheets.Item("$sheetname")
$range = $worksheet.Range("$cells")
$range.Value2 = $MDArray
#Align and autofit cells:
$range.HorizontalAlignment = $xlConstants::xlCenter
$range.VerticalAlignment = $xlConstants::xlCenter
$range.Rows.AutoFit() | Out-Null
$range.Columns.AutoFit() | Out-Null
#Get Hosts for current VCenter:
$VMHosts = Get-VMHost
#New worksheet for Hosts at current VCenter:
$currentsheet = $workbook.worksheets.add()
$currentsheet.name = "Host - " + "$VC"
#Create multidimensional array sized by number of Hosts, plus 1 for header, and number of columns:
$MDArray = New-Object 'object[,]' ($vms.count+1),7
$row = 1
$col = 0
$MDArray[0,0] = "Host Name"
$MDArray[0,1] = "CPU (MHz)"
$MDArray[0,2] = "CPU Used (MHz)"
$MDArray[0,3] = "% CPU Used"
$MDArray[0,4] = "Mem (GB)"
$MDArray[0,5] = "Host Memory(GB)"
$MDArray[0,6] = "% Mem Used"
$vmhostnum = $vmhosts.count
$count = 1
#Loop through Hosts at current VCenter:
foreach ($vmhost in $vmhosts){
$name = $vmhost.name.split(".")[0]
#Progress update:
Write-Host "Currently building $name's statistics into table."
Write-Host "Working $count of $vmhostnum Hosts."
$hoststats = Get-Stat -Entity $vmhost -Start $startdate -Finish $enddate -EA SilentlyContinue
$cpuuse = $hoststats | ?{$_.metricid -eq "cpu.usagemhz.average"}
$cpupcnt = $hoststats | ?{$_.metricid -eq "cpu.usage.average"}
$memuse = $hoststats | ?{$_.metricid -eq "mem.usage.average"}
$MDArray[$row,$col] = [string]$vmhost.name.split(".")[0]
$col++
$MDArray[$row,$col] = [string]$vmhost.cputotalmhz
$col++
$MDArray[$row,$col] = [string]$vmhost.cpuusagemhz
$col++
$MDArray[$row,$col] = [string]([Math]::Round((($cpuuse | Measure-Object Value -Average).Average),2))
$col++
$MDArray[$row,$col] = [string]([math]::Round((($vmhost.MemoryUsageMB)/1024),2))
$col++
$MDArray[$row,$col] = [string]([math]::Round((($vmhost.MemoryTotalMB)/1024),2))
$col++
$MDArray[$row,$col] = [string]([Math]::Round((($memuse | Measure-Object Value -Average).Average),2))
$col = 0
$row++
$count++
}
[reflection.assembly]::loadWithPartialname("Microsoft.Office.Interop.Excel") | out-Null
$xlConstants = "microsoft.office.interop.excel.Constants" -as [type]
$cells = "A1:G" + ($vmhosts.count+1)
$sheetname = "Host - " + "$VC"
$worksheet = $workbook.Worksheets.Item("$sheetname")
$range = $worksheet.Range("$cells")
$range.Value2 = $MDArray
$range.HorizontalAlignment = $xlConstants::xlCenter
$range.VerticalAlignment = $xlConstants::xlCenter
$range.Rows.AutoFit() | Out-Null
$range.Columns.AutoFit() | Out-Null
$datastores = get-datastore
$currentsheet = $workbook.worksheets.add()
$currentsheet.name = "Datastores - " + "$VC"
$MDArray = New-Object 'object[,]' ($datastores.count+1),4
$row = 1
$col = 0
$MDArray[0,0] = "Datastore"
$MDArray[0,1] = "Total Space (GB)"
$MDArray[0,2] = "Free Space (GB)"
$MDArray[0,3] = "% Free"
foreach ($DS in $datastores){
$MDArray[$row,$col] = [string]$DS.name
$col++
$MDArray[$row,$col] = [string]([math]::round((($DS.capacitymb)/1024),2))
$col++
$MDArray[$row,$col] = [string]([math]::round((($DS.freespacemb)/1024),2))
$col++
$MDArray[$row,$col] = [string]([math]::round(($DS.freespacemb/$DS.capacitymb),2)*100) + " %"
$col = 0
$row++
}
[reflection.assembly]::loadWithPartialname("Microsoft.Office.Interop.Excel") | out-Null
$xlConstants = "microsoft.office.interop.excel.Constants" -as [type]
$cells = "A1:D" + ($datastores.count+1)
$sheetname = "Datastores - " + "$VC"
$worksheet = $workbook.Worksheets.Item("$sheetname")
$range = $worksheet.Range("$cells")
$range.Value2 = $MDArray
$range.HorizontalAlignment = $xlConstants::xlCenter
$range.VerticalAlignment = $xlConstants::xlCenter
$range.Rows.AutoFit() | Out-Null
$range.Columns.AutoFit() | Out-Null
Disconnect-VIServer $VC -Confirm:$false
}