# ===========================================================================================
#
#  Script Information
#
#  Title:              create-vmOverview.ps1
#  Author:             Josh Burkard
#  Date:               29.12.2011
#
#  Requirements:       - Windows Powershell
#                      - vmWare PowerCLI V5
#                      - Microsoft Excel
#
#  Usage:              .\create-vmOverview.ps1
#
#  Description:        Get Informations from all existing VM's in a vCenter and writes it
#                      to an Excel-File
#
#===========================================================================================
 
# VARIABLES
$VCServer = "vCenter Name or IP-Address"    # Virtual Center Server
 
$outputFile = "D:\VMDiskOverview_" + $VCServer + ".xlsx"   # Output file path
 
$xlConstants = "microsoft.office.interop.excel.Constants" -as [type]
 
# SCRIPT
 
# Add the VI-Snapin if it isn't loaded already
if ((Get-PSSnapin -Name "VMware.VimAutomation.Core" -ErrorAction SilentlyContinue) -eq $null )
{
    Add-PSSnapin -Name "VMware.VimAutomation.Core"
}
 
[threading.thread]::CurrentThread.CurrentCulture = 'en-US'
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $False
$WorkBook = $objExcel.Workbooks.Add()
$WorkSheet = $WorkBook.Worksheets.Item(1)
$WorkSheet.Cells.Item(1, 1) = "DataCenter:"
$WorkSheet.Cells.Item(1, 2) = "VM:"
$WorkSheet.Cells.Item(1, 3) = "Description:"
$WorkSheet.Cells.Item(1, 4) = "Disk:"
$WorkSheet.Cells.Item(1, 5) = "DiskType:"
$WorkSheet.Cells.Item(1, 6) = "DataStore:"
$WorkSheet.Cells.Item(1, 7) = "DSFolder:"
$WorkSheet.Cells.Item(1, 8) = "CapacyityGB:"
$WorkSheet.Cells.Item(1, 9) = "vCPU:"
$WorkSheet.Cells.Item(1,10) = "vRAM:"
 
$intRow = 2
 
Clear
$VC = Connect-VIServer $VCServer    # Connect to Virtual Center
$myCol = @()    # Prepare output collection
$VMs = Get-VM | Sort Name    # Get all VMs (sorted)
$counter = 0    # Initialize counter for progress bar
ForEach ($Datacenter in Get-DataCenter)
{
    ForEach ($VM in Get-DataCenter $DataCenter | Get-VM | Sort Name)    # Loop through VMs
    {
        $counter++    # Increase counter for progress bar
        Write-Progress -Activity "Gathering disk information" -Status "Processing VM $VM" -PercentComplete (100*($counter/$VMs.count))    # Display progress bar
        $VMHost = Get-VMHost -VM $VM    # Get this VM's host
        $VMHostView = $VMHost | Get-View    # Get advanced properties of host
 
        $WorkSheet.Cells.Item($intRow, 1) = $DataCenter.Name                                # DataCenter Name
        $WorkSheet.Cells.Item($intRow, 2) = $VM.Name                                        # Virtual Machine Name
        $WorkSheet.Cells.Item($intRow, 3) = $VM.Notes                                       # Virtual Machine Description
        $WorkSheet.Cells.Item($intRow, 9) = $VM.NumCpu                                      # NumCpu
        $WorkSheet.Cells.Item($intRow,10) = $VM.MemoryMB                                    # MemoryMB
 
        $intDisks = 0
        ForEach ($DISK in Get-Harddisk -VM $VM)    # Loop through VM's harddisks
        {
            $WorkSheet.Cells.Item($intRow + $intDisks, 4) = $DISK.Name                                          # Virtual Disk Name
            $WorkSheet.Cells.Item($intRow + $intDisks, 5) = ""+$DISK.DiskType
            If ($DISK.DiskType -eq "RawPhysical")
            {
                $Lun = Get-SCSILun $DISK.SCSICanonicalName -VMHost (Get-VM $VM).VMHost
                $WorkSheet.Cells.Item($intRow + $intDisks, 6) = $Lun.CanonicalName                              # Datastore Name
                $WorkSheet.Cells.Item($intRow + $intDisks, 7) = ""
            } else {
                $DataStoreName = $DISK.Filename.Split("[]")[1]
                $WorkSheet.Cells.Item($intRow + $intDisks, 6) = $DataStoreName                                  # Datastore Name
                $DS = Get-Datastore -Name $DataStoreName
                If ($DS.ParentFolder.Name -eq "datastore")
                {
                    $WorkSheet.Cells.Item($intRow + $intDisks, 7) = ""                                          # DSFolder
                } else {
                    $WorkSheet.Cells.Item($intRow + $intDisks, 7) = $DS.ParentFolder.Name                       # DSFolder
                }
            }
            $WorkSheet.Cells.Item($intRow + $intDisks, 8) = $DISK.CapacityKB / 1024 / 1024                      # CapacityKB
 
            Clear-Variable DISK -ErrorAction SilentlyContinue
 
            $intDisks = $intDisks + 1
        }
        if ($intDisks -gt 1)
        {
            $intLastRow = $intRow + $intDisks - 1
            $MergeCells = $WorkSheet.Range("A" + $intRow + ":A" + $intLastRow)
            $MergeCells.Merge()
 
            $MergeCells = $WorkSheet.Range("B" + $intRow + ":B" + $intLastRow)
            $MergeCells.Merge()
 
            $MergeCells = $WorkSheet.Range("C" + $intRow + ":C" + $intLastRow)
            $MergeCells.Merge()
 
            $MergeCells = $WorkSheet.Range("I" + $intRow + ":I" + $intLastRow)
            $MergeCells.Merge()
 
            $MergeCells = $WorkSheet.Range("J" + $intRow + ":J" + $intLastRow)
            $MergeCells.Merge()
        }
        $intRow = $intRow + $intDisks
    }
}
 
$WorkSheet.Range("A1:J1").Font.Bold = $true
$WorkSheet.Range("A1:G" + $intRow).HorizontalAlignment = $xlConstants::xlLeft
$WorkSheet.Range("A1:J" + $intRow).VerticalAlignment = $xlConstants::xlTop
$WorkSheet.Range("H2:H" + $intRow).Numberformat="0.00"
$WorkSheet.application.activewindow.splitcolumn = 2
$WorkSheet.application.activewindow.splitrow = 1
$WorkSheet.application.activewindow.freezepanes = $true
 
$Range = $WorkSheet.Range("A1:J1")
$Range.EntireColumn.AutoFit() | Out-Null
$WorkBook.SaveAs($outputFile)
$objExcel.Workbooks.close()
$objExcel.Quit()