How to find all VMs that have a media injected?

[rguhr]

We use a shared catalog to distribute some basic ISOs to our customers. Unfortunately a lot of users don't remove the ISOs after they are done with it. To keep the shared catalog clean, we have to remove/update the ISOs from time to time.

We have a powercli command to find VMs with a media connected on vSphere level:

Get-VM | Where-Object {$_.PowerState –eq "PoweredOn"}| Get-CDDrive | Where {$_.ISOPath -ne $null} | select Parent,IsoPath | format-table -autosize

But we need a version to this on VMware Cloud Director Level. Sometimes the VCD database thinks the media is still connected somewhere, but on vSphere level it's already gone. Get-Media and Get-CIVM apparently have no property that tracks that.

It doesn't need to be a powercli cmdlet - a select query for the postgres database would also work 🙂

[Macleud · Accepted solution]

Hi.

It's not fast. Approximately 39 minutes on 2308 vm.

$Vms = Search-Cloud -QueryType AdminVM -Filter "IsVAppTemplate==false"

$Report = @()
foreach ($vm in $Vms)
{
$VmMedia = $Vm | get-ciview | Where {$_.Section[0].item.Description -like "CD/DVD Drive" -and $_.Section[0].item.HostResource.value -ne $null}
if ($VmMedia) {
$NewRulevm = new-object PSObject -Property @{
VmName = $vm.Name;
Status = $vm.Status;
Media = ($VmMedia.Section[0].item | Where {$_.Description -like "CD/DVD Drive"}).HostResource.value
Vdc = (Search-Cloud -QueryType AdminOrgVdc -Filter "Id==$($Vm.Vdc)").name
}
$Report += $NewRulevm
}
}

$Report | Select VmName, Status, Media, Vdc | Format-Table -autosize

[rguhr]

Thanks @Macleud this works for us 🙂