More Powershell

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 = 0

foreach ($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 = 0

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

2 thoughts on “More Powershell”

  1. 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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.