PowerCLI to list all Powered Off VMs and Date of Event

[BrianDGeorge]

I am trying to get this script to work but it doesn't even seem close at this point.  I am looking to run a report on Powered Off VMs and information about them.  I need to be able to clean the environment and have to wait 60 days before I permanently delete anything.  Any help would be great.

Connect-VIServer -Server XXX-vcenter1 -User administrator@vsphere.local -Password XXXXXXXXXXXX

Connect-VIServer -Server XXX-vcenter1 -User administrator@vsphere.local -Password XXXXXXXXXXXX

$Report = @()

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

$Datastores = Get-Datastore | select Name, Id

$VMHosts = Get-VMHost | select Name, Parent

foreach ($vm in Get-VM){

$view = Get-View $VMs

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

  $row = '' | select VMName,Powerstate,OS,Host,Cluster,Datastore,NumCPU,MemMb,DiskGb,PowerOFF

    $row.VMName = $VMs.Name

    $row.Powerstate = $VMs.Powerstate

    $row.OS = $VMs.Guest.OSFullName

    $row.Host = $VMs.host.name

    $row.Cluster = $VMs.host.Parent.Name

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

    $row.NumCPU = $VMs.NumCPU

    $row.MemMb = (($VMs.MemoryMB),2)

    $row.DiskGb = ((($VMs.HardDisks | Measure-Object -Property CapacityKB -Sum).Sum * 1KB / 1GB),2)

    $row.PowerOFF = $lastPO.CreatedTime

  $report += $row

}}

$report | Sort Name | Export-Csv -Path "C:\XXXXX\Powered_Off_VMs.csv"

disconnect-viserver * -confirm:$false

I don't get an export to CSV and a display on screen of the following:

Template             : False

Key                  : 165369

ChainId              : 165369

CreatedTime          : 6/28/2016 10:19:35 AM

UserName             :

Datacenter           : VMware.Vim.DatacenterEventArgument

ComputeResource      : VMware.Vim.ComputeResourceEventArgument

Host                 : VMware.Vim.HostEventArgument

Vm                   : VMware.Vim.VmEventArgument

Ds                   :

Net                  :

Dvs                  :

FullFormattedMessage : ServerName on  XXX-prodesxi-10.xxxxx.com in ClusterName is powered off

ChangeTag            :

[LucD]

There is a pipe symbol missing on the Where-clause line.

And I took the liberty of fixing a few other issues

Connect-VIServer -Server XXX-vcenter1 -User administrator@vsphere.local -Password XXXXXXXXXXXX

$Report = @()

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

$Datastores = Get-Datastore | select Name, Id

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-VM -Name $_.Name

  $row = '' | select VMName,Powerstate,OS,Host,Cluster,Datastore,NumCPU,MemMb,DiskGb,PowerOFF

    $row.VMName = $vm.Name

    $row.Powerstate = $vm.Powerstate

    $row.OS = $vm.Guest.OSFullName

    $row.Host = $vm.VMHost.name

    $row.Cluster = $vm.VMHost.Parent.Name

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

    $row.NumCPU = $vm.NumCPU

    $row.MemMb = $vm.MemoryMB

    $row.DiskGb = ((($vm.HardDisks | Measure-Object -Property CapacityKB -Sum).Sum * 1KB / 1GB),2)

    $row.PowerOFF = $lastPO.CreatedTime

    $report += $row

}

$report | Sort Name | Export-Csv -Path "C:\XXXXX\Powered_Off_VMs.csv" -NoTypeInformation -UseCulture

disconnect-viserver * -confirm:$false

[BrianDGeorge]

The script is working much better but only captioning a portion of the machines and throwing the following error:

Get-VM : 7/22/2016 7:34:07 AM    Get-VM        VM with name 'Server_poweredoff_07082016' was not found using the

specified filter(s).

At C:\Scripts\Powered Off Guests.ps1:26 char:9

+   $vm = Get-VM -Name $_.Name

+         ~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : ObjectNotFound: (:) [Get-VM], VimException

    + FullyQualifiedErrorId : Core_OutputHelper_WriteNotFoundError,VMware.VimAutomation.ViCore.Cmdlets.Commands.GetVM

Get-VM : 7/22/2016 7:34:08 AM    Get-VM        VM with name 'Server' was not found using the specified filter(s).

I am thinking that vm is supposed to be renamed when powered off but it looks like the Get-VM is pulling the pre renamed name and it is causing it to choke.

The other issue is the DataStore and Disk are only pulling back System.Object[]

[LucD]

The event object contains the name of the VM at the time it was powered off.

If the VM is renamed afterwards, the script would need to look at the VM's ID to identify it.

I changed that

For the Datastore and Disks entries I also did some updates

Connect-VIServer -Server XXX-vcenter1 -User administrator@vsphere.local -Password XXXXXXXXXXXX

$Report = @()

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

$Datastores = Get-Datastore | select Name, Id

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 $_.Group[0].VM.VM

  $row = '' | select VMName,Powerstate,OS,Host,Cluster,Datastore,NumCPU,MemMb,DiskGb,PowerOFF

    $row.VMName = $vm.Name

    $row.Powerstate = $vm.Powerstate

    $row.OS = $vm.Guest.OSFullName

    $row.Host = $vm.VMHost.name

    $row.Cluster = $vm.VMHost.Parent.Name

    $row.Datastore = $Datastores | Where{$_.Id -eq ($vm.DatastoreIdList | select -First 1)} | Select -ExpandProperty Name

    $row.NumCPU = $vm.NumCPU

    $row.MemMb = $vm.MemoryMB

    $row.DiskGb = Get-HardDisk -VM $vm | Measure-Object -Property CapacityGB -Sum | select -ExpandProperty Sum

    $row.PowerOFF = $lastPO.CreatedTime

    $report += $row

}

$report | Sort Name | Export-Csv -Path "C:\XXXXX\Powered_Off_VMs.csv" -NoTypeInformation -UseCulture

disconnect-viserver * -confirm:$false 

[BrianDGeorge]

Getting so close:

Get-VIObjectByVIView : 7/22/2016 8:36:26 AM    Get-VIObjectByVIView        The object has already been deleted or has

not been completely created

At C:Scripts\Powered Off Guests.ps1:26 char:9

+   $vm = Get-VIObjectByVIView -MORef $_.Group[0].VM.VM

+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : NotSpecified: (:) [Get-VIObjectByVIView], ManagedObjectNotFound

    + FullyQualifiedErrorId : Client20_QueryServiceImpl_RetrievePropertiesEx_ViError,VMware.VimAutomation.ViCore.Cmdle

   ts.Commands.DotNetInterop.GetVIObjectByVIViewCommand

VMName	Powerstate	OS	Host	Cluster	Datastore	NumCPU	MemMb	DiskGb	PowerOFF
Server	PoweredOff	SUSE Linux Enterprise 11 (64-bit)	ESXi	-CLUSTER	LUN	4	4096	17	7/6/2016 11:14
System.Object[]	System.Object[]	System.Object[]	System.Object[]	System.Object[]	LUN	System.Object[]	System.Object[]	77	7/6/2016 11:18

[Arulssj2]

Get-VIEvent : 3/30/2017 2:22:44 AM    Get-VIEvent        Object reference not set to an instance of an object.   

At line:8 char:1

+ Get-VIEvent -Entity $VMs | Where-Object {$_.FullFormattedMessage -is  ...

+ ~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : NotSpecified: (:) [Get-VIEvent], VimException

    + FullyQualifiedErrorId : Core_BaseCmdlet_UnknownError,VMware.VimAutomation.ViCore.Cmdlets.Commands.GetEvent

I'm getting the above error while running the script. Could you please tell me what am i missing?

[LucD]

Did you already try to stop/start your PowerCLI session?

[Arulssj2]

Yes, I did.

[LucD]

And did you check that there is anything in $vms.

Also, try adding the -Verbose switch on the Get-VIEvent cmdlet.

See if that gives more information

[AlbertWT]

Would this be possible to add/modify who turned off the VM ?

[LucD]

Try adding the UserName property to the output.

[AlbertWT]

Yes, you are right, I've added the below lines and it works:

$row.UserName = $lastPO.UserName

Thanks LucD

[zaco]

Hi guys,

I know this is an old post, but quick question.

Would this also list VMs that were shutdown through the guest OS?

Thanks

Yan

[LucD]

Yes, normally it should.

[zaco]

Thanks for fast reply Luc..

Well what could explain some VMs missing from the list of powered off VMs?  Perhaps because the power off event for those VMs isn't in the event log anymore?

[LucD]

Yes, if the power off happened further back in time then the Event retention period, you wouldn't find any events for those.

There could be circumstances that a VM is powered off without the vCenter creating an event.

The hosting ESXi could be disconnected at the time, the VM can disconnect...

It would require further investigation into such a case.

[zaco]

Thanks man. 

Yan

[Ke1th]

I ran this script, but export nothing to  the Excel, anything  wrong

[LucD]

If you do have powered off VMs, and if you keep the events for a sufficiently long time, there should be output.

[ilikestuff]

I realise this thread is getting on a bit, but it was among the first results when I googled.

LucD's script gave me about 90% of what I was after (thanks!), except that some of our teams have been a little naughty and left VMs powered off for long enough that the event entries have been purged, which in turn meant that those VMs were not included in the report.

For my purposes I need these VMs to be included in the output, so to this end I've modified the script a little - hopefully it helps someone!

$Report = @()
$VMs = Get-VM | Where {$_.PowerState -eq "PoweredOff"}
$Datastores = Get-Datastore | Select Name, Id
$PowerOffEvents = Get-VIEvent -Entity $VMs -MaxSamples ([int]::MaxValue) | where {$_ -is [VMware.Vim.VmPoweredOffEvent]} | Group-Object -Property {$_.Vm.Name}
foreach ($VM in $VMs) {
    $lastPO = ($PowerOffEvents | Where { $_.Group[0].Vm.Vm -eq $VM.Id }).Group | Sort-Object -Property CreatedTime -Descending | Select -First 1
    $row = "" | select VMName,Powerstate,OS,Host,Cluster,Datastore,NumCPU,MemMb,DiskGb,PoweredOffTime,PoweredOffBy
    $row.VMName = $vm.Name
    $row.Powerstate = $vm.Powerstate
    $row.OS = $vm.Guest.OSFullName
    $row.Host = $vm.VMHost.name
    $row.Cluster = $vm.VMHost.Parent.Name
    $row.Datastore = $Datastores | Where{$_.Id -eq ($vm.DatastoreIdList | select -First 1)} | Select -ExpandProperty Name
    $row.NumCPU = $vm.NumCPU
    $row.MemMb = $vm.MemoryMB
    $row.DiskGb = Get-HardDisk -VM $vm | Measure-Object -Property CapacityGB -Sum | select -ExpandProperty Sum
    $row.PoweredOffTime = $lastPO.CreatedTime
    $row.PoweredOffBy   = $lastPO.UserName
    $report += $row
}
# Output to screen
$report | Sort Cluster, Host, VMName | Select VMName, Cluster, Host, NumCPU, MemMb, @{N='DiskGb';E={[math]::Round($_.DiskGb,2)}}, PoweredOffTime, PoweredOffBy | ft -a
# Output to CSV - change path/filename as appropriate
$report | Sort Cluster, Host, VMName | Export-Csv -Path "C:\XXXXX\Powered_Off_VMs.csv" -NoTypeInformation -UseCulture