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"

I have seen on many if the VMware followers forums a way to use Excel to send commands to the VI server to create machines, alter vlans etc.

I wanted a way to export information and could not find anyone doing this yet other than using the export-csv so I created the following script which will export all the information for all Virtual machines in your virtual center straight to excel ready for you to manipulate. 

Don't forget to change yourservername for your virtual center server.

Get-VIServer yourservername

$Excel = New-Object -Com Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add(1)

$Sheet = $Excel.WorkSheets.Item(1)
$Sheet.Cells.Item(1,1) = "Name"
$Sheet.Cells.Item(1,2) = "Power State"
$Sheet.Cells.Item(1,3) = "Description"
$Sheet.Cells.Item(1,4) = "Number of CPUs"
$Sheet.Cells.Item(1,5) = "Memory (MB)"

$WorkBook = $Sheet.UsedRange
$WorkBook.Font.Bold = $True

$intRow = 2
$colItems = Get-VM | Select-Object -property "Name","PowerState","Description","NumCPU","MemoryMB"

foreach ($objItem in $colItems) {
    $Sheet.Cells.Item($intRow,1) = $objItem.Name
           
    $powerstate = $objItem.PowerState
    If ($PowerState -eq 1) {$power = "Powerd On"}
        Else {$power = "Powerd Off"}
       
    $Sheet.Cells.Item($intRow,2) = $power
    $Sheet.Cells.Item($intRow,3) = $objItem.Description
    $Sheet.Cells.Item($intRow,4) = $objItem.NumCPU
    $Sheet.Cells.Item($intRow,5) = $objItem.MemoryMB

$intRow = $intRow + 1

}
$WorkBook.EntireColumn.AutoFit()
Clear