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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 |
# =========================================================================================== # # 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() |
