Powered Off VMs by date and Datastore

[mark7five7]

I am using the below script to get powered off VMs as well as their powered off date so I can better manage stale VMs.  However, I would also like to see what datastores those VMs are stored on.  There is a line in the script that is supposed to get that information but when I get the final CSV, the datastore tab is blank.  Any help?

#### Get Virtual Center To Connect to:

$VCServerName = Read-Host "What is the Virtual Center name?"

$ExportFilePath = Read-Host "Where do you want to export the data?"

$VC = Connect-VIServer $VCServerName

$Report = @()

$VMs = get-vm |Where-object {$_.powerstate -eq "poweredoff"}

$Datastores = Get-Datastore | select Name, Id

$VMHosts = Get-VMHost | select Name, Parent

### Get powered off event time:

Get-VIEvent -Entity $VMs -MaxSamples ([int]::MaxValue) |

where {$_ -is [VMware.Vim.VmPoweredOffEvent]} |

Group-Object -Property {$_.Vm.Name} | %{

   $lastPO = $_.Group | Sort-Object -Property CreatedTime -Descending | Select -First 1

  $vm = Get-VIObjectByVIView -MORef $lastPO.VM.VM

  $report += New-Object PSObject -Property @{

     VMName = $vm.Name

    Powerstate = $vm.Powerstate

    OS = $vm.Guest.OSFullName

    IPAddress = $vm.Guest.IPAddress[0]

     ToolsStatus = $VMView.Guest.ToolsStatus

     Host = $vm.host.name

     Cluster = $vm.host.Parent.Name

     Datastore = ($Datastores | where {$_.ID -match (($vmview.Datastore | Select -First 1) | Select Value).Value} | Select Name).Name

     NumCPU = $vm.NumCPU

     MemMb = [Math]::Round(($vm.MemoryMB),2)

     DiskGb = [Math]::Round((($vm.HardDisks | Measure-Object -Property CapacityKB -Sum).Sum * 1KB / 1GB),2)

     PowerOFF = $lastPO.CreatedTime

    Note = $vm.Notes  }

}

$Report = $Report | Sort-Object VMName

if ($Report) {

   $report | Export-Csv $ExportFilePath -NoTypeInformation}

else{

   "No PoweredOff events found"

}

$VC = Disconnect-VIServer $VCServerName -Confirm:$False

[LucD]

I don't see where $vmview is initialised, but try changing that line as follows

Datastore = $Datastores | where {$_.id -match $vm.DatastoreIdList[0]} | Select -ExpandProperty Name

[mark7five7]

I changed the datastore line to what you suggested below but after running the script I still get an empty datastore column in my CSV.  

[LucD]

Can you add the following 2 lines to the PSObject properties ?

Just to see what the Ids are for the VM's datastoreIdList and for the Datastores.

VMDatastoreId = [string]::Join(',',$vm.DatastoreIdList)

DatastoreId = [string]::Join(',',($Datastores | %{$_.Id}))