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