Find placement of Cloud Director ISO images on Datastore

Sometimes you need to find information about placement on vSphere Datastores ISO images that clients uploaded to their catalogs.

Let's dive into the workflow when a client is initiating uploading of ISO image to catalog:

• The client creates a catalog and places it on some specific policy at needed Organization VDC (or using default storage policy if we don't choose Pre-provision on specific storage policy):
  

After the catalog is created it will use Datastores that available to Provider VDC of current Organization VDC with a needed tag of Storage Policy.

The Next client is initializing uploading of ISO image from the "Media & Other" menu of Cloud Director and chooses already created catalog:

Lest check status of task at Cloud Director:

When the task is initiated, at spool area at NFS mount-point (/opt/vmware/vcloud-director/data/transfer) will be created folder with UUID-like name (this name is transfer_handle_id at logical_disk table - this is an ID of our transfer that we've initialized from Cloud Director web-panel).

If we uploading not a template of the VM it should create the folder - "file". In that folder, we can see an uploading file that is chunked into 50Mb pieces. After upload on NFS share is completed it will be transferred to vSphere Datastore that is backing Storage policy of Cloud Director client catalog.

After copying a file to Datastore will be successful , the source file with a folder on NFS share (spool area) will be deleted.

The files in the spool area are always associated with a transfer session (true for both upload and download). The transfer session has a timeout that is configurable in the system settings.

We can check folder structure on the vSphere level to understand how the uploaded file will be named. We didn't find the ISO image by their name, because it will be renamed to media-UUID3.iso. As you can see in the next screenshot structure of folders at Datastore level: /vcd_SystemName/media/UUID-1/UUID-2/media-UUID3.iso

Let's explain what does these UUID means:

• UUID-1 - ID of Client's Organization VDC:

• UUID-2 - ID of Client's Cloud Director Organization:

• UUID-3 - media_id that is stored in managed_media table of Cloud Director DB:

BTW: SystemName can be found in public.config table:

Let's create an SQL query that will get information from the Database about all ISO images of the needed organization. We'll create this scheme that will help us to understand connections between tables in the Cloud Director database:

Here is SQL SELECT that will get the next information:

• Media name (without repeating images, that could be in case if you use more than one Storage Policy on one DataStore)
• Client Organization VDC name
• Provider VDC that is storing that Org VDC
• Catalog name that contains ISO
• DataStore name that is storing that ISO
• Storage Policy of Catalog that contains ISO
• Creation status (RESOLVED/ERROR) to find broken ISO's
• Full path on Datastore
• Media in MB (dividing Kb on 1048576)
• Creation date of ISO
• Description of file

select distinct on (logical_disk.name) logical_disk.name as "Media Name", org_prov_vdc.name as "Organization VDC", cluster_compute_resource_inv.name as "Provider VDC",
catalog.name as "Catalog name", datastore_inv.vc_display_name as "DS name", managed_media.dstore_moref as "DS Moref", ui_media_list_view.storage_class as "Storage Policy",
managed_media.creation_status as "Creation status", managed_media.filepath as "Full path on DS", managed_media.media_size / 1048576 as "Size in MB", 
catalog_item.date_created as "Creation date",  managed_media.description as "Description" from logical_disk
inner join managed_media on logical_disk.name=managed_media.media_name
inner join org_prov_vdc on logical_disk.org_vdc_id=org_prov_vdc.id
inner join organization on org_prov_vdc.org_id=organization.org_id
inner join catalog_item on logical_disk.id=catalog_item.entity_id
inner join catalog on catalog_item.catalog_id=catalog.id
inner join datastore_inv on datastore_inv.moref=managed_media.dstore_moref
inner join ui_media_list_view on ui_media_list_view.org_vdc_id=logical_disk.org_vdc_id
inner join resource_pool_inv on org_prov_vdc.sub_rp_moref=resource_pool_inv.moref
inner join cluster_compute_resource_inv on resource_pool_inv.cr_moref=cluster_compute_resource_inv.moref
where organization.name = 'DutyTest'

You can variate "where" filter to find needed info by Org VDC name/id or by ISO filename, for example.

I hope this info will be useful to you. Cheers =)