I have now updated my first powershell script, this enables you to open a pre-configured spreadsheet with graphs and fill in the cells needed to populate the graphs and produce a nice 2 minute report on the state of the infrastructure, this is currently customised to my particular clusters but can be easily modified…
For a copy of the spreadsheet drop me a mail or a comment on this post.
Get-VIServer yourservername
$Excel = New-Object -Com Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Open("C:\Resource.xls")
$Sheet = $Excel.WorkSheets.Item(2)$intRow = 11
$colItems = Get-Datastore | Select-Object -property "Name","FreeSpaceMB","CapacityMB" | Sort-Object Name
$totalcapacity = 0foreach ($objItem in $colItems)
{
$Sheet.Cells.Item($intRow,2) = $objItem.Name
$used = $objItem.CapacityMB – $objItem.FreeSpaceMB
$Sheet.Cells.Item($intRow,3) = $used
$totalcapacity = $totalcapacity + $objItem.CapacityMB$intRow = $intRow + 1
}
$Sheet = $Excel.WorkSheets.Item(1)
$Sheet.Cells.Item(5,4) = $totalcapacity$Sheet = $Excel.WorkSheets.Item(3)
$Sheet.Cells.Item(4,3) = "Done"
$Sheet = $Excel.WorkSheets.Item(1)$intRow = 17
foreach ($objItem in $colItems)
{
$Sheet.Cells.Item($intRow,2) = $objItem.Name
$Sheet.Cells.Item($intRow,3) = $objItem.CapacityMB$intRow = $intRow + 1
}
$totalmem = 0
$cpumhz = 0
$totalcpu = 0Get-VMHost | %{Get-View $_.ID} | %{
$esx = "" | select NumCpuCores, Hz, Memory
$esx.NumCpuCores = $_.Hardware.CpuInfo.NumCpuCores
$esx.Hz = $_.Hardware.CpuInfo.Hz
$esx.Memory = $_.Hardware.MemorySize
$totalmem = $totalmem + $esx.Memory
$cpumhz = $esx.numCpuCores * $esx.Hz
$totalcpu = $totalcpu + $cpumhz
}
$formatedmem = ($totalmem /1024) /1024
$formatedcpu = ($totalcpu /1000) /1000
$Sheet = $Excel.WorkSheets.Item(1)
$Sheet.Cells.Item(6,4) = $formatedmem
$Sheet = $Excel.WorkSheets.Item(3)
$Sheet.Cells.Item(5,3) = "Done"
$Sheet = $Excel.WorkSheets.Item(1)
$Sheet.Cells.Item(7,4) = $formatedcpu
$Sheet = $Excel.WorkSheets.Item(3)
$Sheet.Cells.Item(6,3) = "Done"$Sheet = $Excel.WorkSheets.Item(2)
$colItems = Get-Stat -Entity (Get-ResourcePool -Name "Live") -Stat cpu.usagemhz.average -MaxSamples 1 -Realtime
foreach ($objItem in $colItems)
{
$Sheet.Cells.Item(4,4) = $objItem.Value
}$Sheet = $Excel.WorkSheets.Item(3)
$Sheet.Cells.Item(7,3) = "Done"
$Sheet = $Excel.WorkSheets.Item(2)$colItems = Get-Stat -Entity (Get-ResourcePool -Name "Test") -Stat cpu.usagemhz.average -MaxSamples 1 -Realtime
foreach ($objItem in $colItems)
{
$Sheet.Cells.Item(5,4) = $objItem.Value
}
$Sheet = $Excel.WorkSheets.Item(3)
$Sheet.Cells.Item(8,3) = "Done"
$Sheet = $Excel.WorkSheets.Item(2)
$colItems = Get-Stat -Entity (Get-ResourcePool -Name "Live") -Stat mem.active.average -MaxSamples 1 -Realtime
foreach ($objItem in $colItems)
{
$promem = ($objItem.Value /1000)
$Sheet.Cells.Item(4,3) = $promem
}$Sheet = $Excel.WorkSheets.Item(3)
$Sheet.Cells.Item(9,3) = "Done"
$Sheet = $Excel.WorkSheets.Item(2)
$colItems = Get-Stat -Entity (Get-ResourcePool -Name "Test") -Stat mem.active.average -MaxSamples 1 -Realtime
foreach ($objItem in $colItems)
{
$nonpromem = ($objItem.Value /1000)
$Sheet.Cells.Item(5,3) = $nonpromem
}
$Sheet = $Excel.WorkSheets.Item(3)
$Sheet.Cells.Item(10,3) = "Done"
$Sheet.Cells.Item(13,3) = "Done"
To be honest this was a pretty site specific piece of code I wrote when i was learning, what were you trying to get into excel? perhaps i could help.
please send spreadsheet to todd.parker@live.com.au….thanks