PowerCLI script to export all Vms

Here is a PowerCLI script I hacked together to list out all of our VMs by cluster.

# List out all VMs by cluster, export to Excel
 
$VMCollection = @()
$ClusterName = ""
 
# Path to save Excel output
$savepath = "D:\My Documents\Scripts\powershell\VMware\VMListByCluster\"
# Enter your vCenter server here
$VIServer = "MYSERVER"
 
function GetVmDetails( $Details, $ClusterName )
{
 
$Details | Add-Member -Name Name -Value $VM.Name -membertype NoteProperty
$Details | Add-Member -Name DNSName -Value $vm.Guest.get_HostName() -membertype NoteProperty
$Details | Add-Member -Name Description -Value $vm.Description -membertype NoteProperty
$Details | Add-Member -Name OperatingSystem -Value $vm.Guest.get_OSFullName() -membertype NoteProperty
$Details | Add-Member -Name Cluster -Value $ClusterName -membertype NoteProperty
 
if ( $Details.DNSName.Length -eq 0 )
{
$Details.DNSName = " "
}
}
 
Write-Host "Connecting to Virtual Center..."
Connect-VIServer $VIServer
$AllClusters = Get-Cluster | Sort-Object "Name"
ForEach( $Cluster in $AllClusters)
{
 
$ClusterName = $Cluster.Name
$AllVMs = get-cluster $ClusterName | Get-VM | Sort-Object Name
ForEach ($VM in $AllVMs )
{
Write-Host $VM.Name
$Details = New-Object PSObject
GetVMDetails $Details $ClusterName
$VMCollection += $Details
}
}
 
#$VMCollection
 
Write-Host "Exporting to Excel..."
$cnt = ($VMCollection | Measure-Object).Count
 
$Excel = New-Object -Com Excel.Application
#$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()
 
$Sheet = $Excel.WorkSheets.Item(1)
 
$Sheet.Cells.Item(1,1) = "VM Name"
$Sheet.Cells.Item(1,1).Font.Bold = $True
$Sheet.Range("A1").ColumnWidth = 24
 
$Sheet.Cells.Item(1,2) = "DNS Name"
$Sheet.Cells.Item(1,2).Font.Bold = $True
$Sheet.Range("B1").ColumnWidth = 35
 
$Sheet.Cells.Item(1,3) = "Description"
$Sheet.Cells.Item(1,3).Font.Bold = $True
$Sheet.Range("C1").ColumnWidth = 47
 
$Sheet.Cells.Item(1,4) = "OS"
$Sheet.Cells.Item(1,4).Font.Bold = $True
$Sheet.Range("D1").ColumnWidth = 54
 
$Sheet.Cells.Item(1,5) = "Cluster"
$Sheet.Cells.Item(1,5).Font.Bold = $True
$Sheet.Range("E1").ColumnWidth = 16
 
#Header Row
$Sheet.Range("A1").RowHeight = 50
 
$intRow = 2
ForEach ($objVM in $VMCollection )
{
$Sheet.Cells.Item($intRow,1) = $objVM.Name
$Sheet.Cells.Item($intRow,2) = $objVM.DNSName
$Sheet.Cells.Item($intRow,3) = $objVM.Description
$Sheet.Cells.Item($intRow,4) = $objVM.OperatingSystem
$Sheet.Cells.Item($intRow,5) = $objVM.Cluster
$rng = "A" + $intRow.ToString()
$Sheet.Range($rng).RowHeight = 110
Write-Host $objVM.Name
$msg = ($intRow -1).ToString() + " of " + $cnt.ToString()
Write-Host $msg
$intRow += 1
 
}
 
$fname = $savepath + "vms.xlsx"
$Excel.Application.DisplayAlerts = $False
$Sheet.SaveAs($fname)
$Excel.Application.DisplayAlerts = $True
$Excel.Close()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)

6 comments

    1. pkremer

      get-vm | Where { $_.Guest.get_OSFullName() -Like “*Windows*” } is a command that you can run in PowerCLI that would list Windows VMs. So if you changed the $AllVMs line of code to include the Where filter, it would filter for you.

  1. pkremer

    Thanks. For multiple vCenters there’d have to be quite a bit of reengineering of the script. When you use multiple vCenters in Connect-VIServer, I can’t find a way to retrieve the vCenter name out of Get-VM. So instead you’d have to change the script to run in a foreach loop and connect to each vCenter one at a time… then you’d have to add the vCenter name to the $Details | Add-Member section, plus the Excel headers and data columns. If you need all the VMs from multiple vCenters in one output, I don’t see a way around doing it this way.

    An easier way might be to change the script to accept a parameter of vCenter and filename, then change it to connect to the vCenter specified in the parameter and output it to the specified filename. i.e. GetVMsByCluster -vCenterName MyVC -OutputPath C:\myvc.csv, then GetVMsByCluster -vCenterName MyVC2 -OutputPath C:\myvc2.csv

  2. Justin

    Great script… I’ve added a line to the script to pull the data from multiple vCenter servers, and I’m looking to add a tab for the vCenter’s name as well. Is this possible?

Leave a Reply

Your email address will not be published. Required fields are marked *