A PowerShell Script to export VMware data to CSV

While working in a VMware environment with thousands of VMs, I needed to make a spreadsheet containing a lot of of the data listed in vCenter for each VM, including their custom attributes (a.k.a. Annotations). Rather than using the vSphere Client’s built-in “Export List” option, I wrote a PowerShell script to do it instead. Since it seemed like something others may find useful, I’m posting it here.

The reason I didn’t simply click on the “Virtual Machines” tab in vSphere, right-click the column headings, include columns I wanted, and click File -> Export List is because I’ve had trouble with vCenter’s info being incomplete if I didn’t first slowly scroll down through it all before exporting it. (i.e., if vSphere doesn’t first display it, it won’t export and you’ll have an incomplete CSV file!) Also, vSphere doesn’t have an option to export the following fields which these scripts will capture:

  • ClusterName
  • OSBooted time
  • VMXname
  • VMDKname
  • DatastoreName
  • NumCpuShares
  • CpuLimitMHZ
  • CpuReservationMHZ
  • NumMemShares
  • LimitMB
  • SnapshotCount
  • VLANid
  • Portgroup

A script also just makes things easier and less time consuming. If someone asks for some data, you can run this PowerShell script and go do something else. Then, when you come back later, your CSV file is waiting for you. So, while it may take a few hours to extract all the data you’re looking for, it only takes 30 seconds of your actual time to generate that CSV, since you can be doing other things while it runs in the background. However, if you need that data ASAP, then exporting directly from vSphere may be your best option, though it’s tedious, since this script can take a while to run.

So, on to the script!

This script was written for VMware 5.5, but I see no reason why it wouldn’t work in other versions. Also I should mention that bits of this code may have been borrowed from/inspired by other pages around the net, but I can’t cite sources, since I’ve long since forgotten where anything I adapted came from. (Sorry!)

To allow PowerShell to talk to vCenter and be able to run this script, you first must install the VMware vSphere PowerCLI. For me it was VMware-PowerCLI-5.5.0-1295336.exe, but you can get whatever your current version is.

Here’s the script… Scroll down to read important notes and additional comments about it below!

#### Comment out unneeded lines down below to make this run faster!! ####
#########################################################################

$StopWatch = [system.diagnostics.stopwatch]::startNew()

Write-Host " "
Write-Host "Loading snapin and connecting to vcenter.domain.com - please wait..."
Add-PSSnapin -Name VMware.VimAutomation.Core
Connect-VIServer vcenter.domain.com -WarningAction SilentlyContinue

$allLines = @()

$VMsNotFoundFile = "C:\Temp\VMsNotFound.txt"
$CSVOutputFile = "C:\Temp\VMreport.csv"

###########################################################################
## Uncomment these lines to use only the VMs listed in a text file with this layout:
##    vmname1
##    vmname2
##    vmname3
##    ...
##
# Begin section for reading VMs from text file:
# (Comment out section below (all VMs) when using this.)
    $InputFile = 'C:\Temp\vmnames.txt'

    Write-Host " "
    Write-Host "OK, now reading in server info. (This could take a while if you have more than a few servers.)"
    $null | Out-File $VMsNotFoundFile

    $reader = New-Object IO.StreamReader $InputFile
     while($reader.ReadLine() -ne $null){ $TotalVMs++ }
     
    Get-Content -Path $InputFile | %{
        $vm = Get-VM -Name $_ -ErrorAction SilentlyContinue
## End section for VMs in text file.
###########################################################################

###########################################################################
## Begin lines for *all* VMs in vCenter: 
## (Comment out above section (VMs from text) when using this.)

#    Write-Host " "
#    Write-Host "OK. Now counting VMs - stand by..."
#
#    $TotalVMs = Get-VM
#    $TotalVMsCount = $TotalVMs.count
#
#    Write-Host " "
#    Write-Host "Here we go! Collecting server info... (Go get a cup of coffee - this will take a while...)"
#    Get-VM | `
#    ForEach-Object {
#    $VM = $_

## End lines for all VMs in vCenter.
###########################################################################

    $i++
    $percentdone = (($i / $TotalVMs) * 100)
    $percentdonerounded = "{0:N0}" -f $percentdone
    Write-Progress -Activity "Gathering data from VMs" -CurrentOperation "Working on VM: $VM (VM $i of $TotalVMs)" -Status "$percentdonerounded% complete" -PercentComplete $percentdone

    $VMview = $VM | Get-View
    $VMResourceConfiguration = $VM | Get-VMResourceConfiguration

    IF (!$vm){
        Write-Host "$_ can't be found in VMware!"
        $_ | Out-File $VMsNotFoundFile -Append
        } 
        else 
        {
        $VMHardDisks = $VM | Get-HardDisk
        $HardDisksSizesGB = @()
        $Temp = $VMHardDisks | ForEach-Object { $HardDisksSizesGB += [Math]::Round($_.CapacityKB/1MB) }
        $VmdkSizeGB = ""
        $Temp = $HardDisksSizesGB | ForEach-Object { $VmdkSizeGB += "$_+" }
        $VmdkSizeGB = $VmdkSizeGB.TrimEnd("+")
        $TotalHardDisksSizeGB = 0
        $Temp = $HardDisksSizesGB | ForEach-Object { $TotalHardDisksSizeGB += $_ }
        $VMDKnames = @()
        $Temp = $VMHardDisks | ForEach-Object { $VMDKnames += $_.Filename.Split("/")[1] }
        
        $Snapshots = $VM | Get-Snapshot

        $Report = "" | Select-Object VMname,ESXname,Host,ClusterName,Powerstate,OSBooted,MemoryGB,vCPUcount,vNICcount,IPaddresses,VMXname,VMDKname,VmdkSizeGB,TotalVmdkSizeGB,DiskFree,DiskUsed,DatastoreName,ToolsVersion,ToolsUpdate,NumCpuShares,CpuLimitMHZ,CpuReservationMHZ,NumMemShares,ReservationsMB,LimitMB,SnapshotCount,GuestOS,HardwareVersion,VLANid,Portgroup,Notes,AnnoBackEx,AnnoBackWeek,AnnoBusiness,AnnoChgMgmt,AnnoCostCtr,AnnoBLAHBLAH
        $Report.VMName = $VM.name
        $Report.ESXname = $VM.VMHost
        $Report.Host = $vm.VMHost.name
        $Report.ClusterName = ($VM | Get-Cluster).Name
        $Report.Powerstate = $vm.Powerstate
        $Report.OSBooted = (Invoke-VMScript -VM $VM -ScriptText 'systeminfo| find "System Boot Time"' -ScriptType Bat -ErrorAction SilentlyContinue | Select -ExpandProperty ScriptOutput) -replace 'System Boot Time:          ([^-]+)', '$1'
        $Report.MemoryGB = $VM.MemoryMB/1KB
        $Report.vCPUcount = $VM.NumCpu
        $Report.vNICcount = $VM.Guest.Nics.Count
        $Report.IPaddresses = [string]::Join(',',$VM.Guest.IPAddress)
        $Report.VMXname = $VMview.Config.Files.VmPathName.Split("/")[1]
        $Report.VMDKname = [string]::Join(',',$VMDKnames)
        $Report.VmdkSizeGB = $VmdkSizeGB
        $Report.TotalVmdkSizeGB = $TotalHardDisksSizeGB
        $Report.DiskFree = [Math]::Round((($vm.Guest.Disks | Measure-Object -Property FreeSpace -Sum).Sum / 1GB),2)
        $Report.DiskUsed = $Report.TotalVmdkSizeGB - $Report.DiskFree
        $Report.DatastoreName = [string]::Join(',',($VMview.Config.DatastoreUrl | %{$_.Name}))
        $Report.ToolsVersion = $VMview.Config.Tools.ToolsVersion
        $Report.ToolsUpdate = $VMview.Guest.ToolsStatus
        $Report.NumCpuShares = $VMResourceConfiguration.NumCPUShares
        $Report.CpuLimitMHZ = $VMResourceConfiguration.CpuLimitMhz
        $Report.CpuReservationMHZ = $VMResourceConfiguration.CpuReservationMhz
        $Report.NumMemShares = $VMResourceConfiguration.NumMemShares
        $Report.ReservationsMB = $VMResourceConfiguration.MemReservationMB
        $Report.LimitMB = $VMResourceConfiguration.MemLimitMB
        $Report.SnapshotCount = (@($VM | Get-Snapshot)).Count
        $Report.GuestOS = $VM.Guest.OSFullName
        $Report.HardwareVersion = $VM.Version
        $tmpVLANId = Get-VirtualPortgroup -VM $vm | %{$_.VlanId}
        if(!$tmpVLANId){$tmpVLANId = ""}
        $Report.VLANid = [string]::Join(',',$tmpVLANId)
        $tmpPG = Get-VirtualPortgroup -VM $vm | %{$_.Name}
        if(!$tmpPG){$tmpPG = ""}
        $Report.Portgroup = [string]::Join(',',$tmpPG)
        $Report.Notes = $VM | Select-Object -ExpandProperty Notes
        If (($Report.AnnoBackEx = ($VM | Get-Annotation -CustomAttribute "BackupExcluded") -replace 'BackupExcluded:([^-]+)', '$1') -eq "BackupExcluded:") { $Report.AnnoBackEx = "" } else { $Report.AnnoBackEx = ($VM | Get-Annotation -CustomAttribute "BackupExcluded") -replace 'BackupExcluded:([^-]+)', '$1' }
        If (($Report.AnnoBackWeek = ($VM | Get-Annotation -CustomAttribute "BackupWeekly") -replace 'BackupWeekly:([^-]+)', '$1') -eq "BackupWeekly:") { $Report.AnnoBackWeek = "" } else { $Report.AnnoBackWeek = ($VM | Get-Annotation -CustomAttribute "BackupWeekly") -replace 'BackupWeekly:([^-]+)', '$1' }
        If (($Report.AnnoBusiness = ($VM | Get-Annotation -CustomAttribute "Business") -replace 'Business:([^-]+)', '$1') -eq "Business:") { $Report.AnnoBusiness = "" } else { $Report.AnnoBusiness = ($VM | Get-Annotation -CustomAttribute "Business") -replace 'Business:([^-]+)', '$1' }
        If (($Report.AnnoChgMgmt = ($VM | Get-Annotation -CustomAttribute "ChangeMgmt") -replace 'ChangeMgmt:([^-]+)', '$1') -eq "ChangeMgmt:") { $Report.AnnoChgMgmt = "" } else { $Report.AnnoChgMgmt = ($VM | Get-Annotation -CustomAttribute "ChangeMgmt") -replace 'ChangeMgmt:([^-]+)', '$1' }
        If (($Report.AnnoCostCtr = ($VM | Get-Annotation -CustomAttribute "Cost Center") -replace 'Cost Center:([^-]+)', '$1') -eq "Cost Center:") { $Report.AnnoCostCtr = "" } else { $Report.AnnoCostCtr = ($VM | Get-Annotation -CustomAttribute "Cost Center") -replace 'Cost Center:([^-]+)', '$1' }
        If (($Report.AnnoBLAHBLAH = ($VM | Get-Annotation -CustomAttribute "BLAHBLAH") -replace 'BLAHBLAH:([^-]+)', '$1') -eq "BLAHBLAH:") { $Report.AnnoBLAHBLAH = "" } else { $Report.AnnoBLAHBLAH = ($VM | Get-Annotation -CustomAttribute "BLAHBLAH") -replace 'BLAHBLAH:([^-]+)', '$1' }

        $allLines += $Report
    }
}

$allLines | Export-Csv $CSVOutputFile -NoTypeInformation
Write-Host " "
$StopWatch.Elapsed
Write-Host " "
Write-Host "Done! See above for elapsed time. Go look in C:\Temp for the files."

I didn’t put many comments in the script, but from the comments that are there, the first thing you’ll notice is that this is actually two scripts in one. In its current state, it’s set up to read in servers from a list you put in a text file, and it’ll pull in data about those servers only. However, if you want to read in info on all VMs in vCenter instead, then comment out the lines about importing from text and uncomment the lines below that section.

You’ll also want to modify the line that specifies the “VIServer” (i.e., vCenter server) you’re connecting to, to match your environment.

Also, verify that the 3 lines in the top part defining file names & paths are acceptable before running the script.

Then, as the first line in the script suggests, scroll down and comment out lines for anything that you don’t actually need data on. This takes a long time to run, so you don’t want to make it take any longer than necessary by collecting unimportant data. (Remember to also delete the corresponding name in the “Select-Object” line where the column headings are defined.)

Then save your changes and run the script.

Other things to Note:

  • The scripts don’t register values for OS Booted time & Disk Free space for non-Windows VMs.
  • Be sure the vmnames.txt file doesn’t have extra line breaks after the last server name or you’ll get errors!
  • Rarely, you may see red errors when it tries accessing a specific VM saying something like: “The object has already been deleted or has not been completely created.” This issue may be caused by mismatched snapshot object id in vCenter database. The mismatch can be triggered by migrating the powered off virtual machine to a different ESX host then restarting vpxd service. More info available on VMware’s web site.

Nifty features of this script:

  • Shows a progress bar at the top, giving % complete and number of complete of total.
  • Displays elapsed time at end (good for testing to judge how long bigger exports will take).
  • If you’re reading from a list of VMs in a text file, it will output a list of any VMs it couldn’t find, both to the screen and to a “vmnotfound.txt” file for later troubleshooting.
  • I could’ve made this a little tighter and more efficient, but I intentionally listed each piece of VM data on its own line so you can more easily comment out the values you don’t need when trying to save on running time.
  • Outputs results to CSV file.
  • This script can collect any of the following data:
    VM name
    ESX name
    Host
    Cluster Name
    Power state
    OS Booted Time
    Memory GB
    vCPU count
    vNIC count
    IP addresses
    VMX name
    VMDK name
    Vmdk Size GB
    Total Vmdk Size GB
    Disk space Free
    Disk space Used
    Datastore Name
    Tools Version
    Tools Last Updated
    Number of CPU Shares
    CPU Limit MHZ
    CPU Reservation MHZ
    Number of Memory Shares
    Reservations MB
    Limit MB
    Snapshot Count
    Guest OS
    Hardware Version
    VLAN ID
    Port group
    Notes
    Annotation: BackupExcluded
    Annotation: BackupWeekly
    Annotation: Business
    Annotation: ChangeMgmt
    Annotation: Cost Center
    Annotation: BLAHBLAH (by now you get the idea!)

I hope this helps someone – it took me a little while to put together the way I wanted it, so I’m posting it as much for my own future reference as yours. :) If you know of any more data that can be collected, or more efficient ways to do anything, let me know in the comments below.

–Steve