I had the task to list all existing virtual machines in our vSphere vCenter to an Excel-List. Cause i have alot of VM’s and they change often, i wrote a powershell-script, which creates an excel-file like this:

The requirements for this script are Microsoft Excel and VMware PowerCLI 5. Look at the script or download it here:

# ===========================================================================================
#
#  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()