Skip to main content

Michele's Blog

vSphere export VM list in excel

Michele Baldessari

I wanted to have a list of all the VMs in our vSphere farm exported to excel. CPU, RAM, Disks, Resource Allocation and so on. I needed one line per vm. I read up a little bit on powershell (which does seem to be pretty neat) and cooked up the following script:

# Export VM lists in a nice Excel List

$rows = @()
Foreach ($VM in get-vm) {
 $View = $VM | get-view
 $Config = $View.config
 if ($Config.Template) { continue } # Skip templates

 $row = New-Object -TypeName PSObject
 $res = Get-ResourcePool -VM $View.Name

 $row | Add-Member -MemberType NoteProperty -Name ResourcePool -Value $res.Name
 $row | Add-Member -MemberType NoteProperty -Name VM -Value  $Config.Name
 $row | Add-Member -MemberType NoteProperty -Name Hostname -Value $View.Guest.HostName
 $row | Add-Member -MemberType NoteProperty -Name PoweredOn -Value $VM.PowerState
 $row | Add-Member -MemberType NoteProperty -Name Cpu -Value  $Config.Hardware.NumCPU
 $row | Add-Member -MemberType NoteProperty -Name Ram -Value  $Config.Hardware.MemoryMB
 $row | Add-Member -MemberType NoteProperty -Name FullOS -Value $Config.GuestFullName
 $row | Add-Member -MemberType NoteProperty -Name IP -Value $View.Guest.IPAddress
 $row | Add-Member -MemberType NoteProperty -Name Tools -Value $View.Guest.ToolsStatus
 $row | Add-Member -MemberType NoteProperty -Name HWVersion -Value $Config.Version

 Write-Host "VM: $VM" -ForegroundColor blue
 $i = 1
 foreach ($Disk in Get-HardDisk -VM $View.Name)
 {
   $row | Add-Member -MemberType NoteProperty -Name "DiskPath$i" -Value $Disk.Filename
     $CapacityGB =  [math]::Round(([int]$Disk.CapacityKB) / 1024 / 1024)
   $row | Add-Member -MemberType NoteProperty -Name "DiskCapacityGB$i" -Value $CapacityGB
   $row | Add-Member -MemberType NoteProperty -Name "Persistent$i" -Value $Disk.Persistence
   Write-Host "$i"
   $i += 1
 }
 foreach ($j in $i..7)
 {
   $row | Add-Member -MemberType NoteProperty -Name "DiskPath$j" -Value ""
   $row | Add-Member -MemberType NoteProperty -Name "DiskCapacityGB$j" -Value ""
   $row | Add-Member -MemberType NoteProperty -Name "Persistent$j" -Value ""
 }
 $rows += $row
}

$rows | Export-Csv "vms.csv" -NoTypeInformation

I definitely need to dig powershell more. I'll be able to ditch vbscript once and for all for repetitive Windows admin tasks ;)