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