Set vApp Templates to Never Expire within Organizations with VAT Storage Leases

In migrating from Lab Manager to vCloud Director one systems administration feature loss (of many) is no longer being able to set lease times for longer periods than the policy states.  This is used when system administrators create library entries that shouldn’t expire but any library entry a standard user creates will take on the default policy.  vCloud Director allows for different lease times for different Organizations but you cannot change lease times beyond that of the Organizations policy via the GUI or any API’s, even as a system administrator.

I did some digging in the DB and found differences in the “vm_container” table for vApp Templates with lease times set and those that are set to never expire.  The values for “auto_delete_date” and “auto_delete_ticks” are both NULL for Templates set to never expire.  I manually changed the DB for a vApp Template within an Organization that had a maximum lease time set to see if it would get mad, and it did not.  The template stayed function-able as long as it was there.

I wrote the following DB query to find all vApp Templates, and some other info to distinguish them for quicker search:

Select VMC.name as vAppTemplate, ausr.username as UserID, org.display_name as Organization, VMC.auto_delete_date as DateExpire
FROM vm_container as VMC
INNER JOIN usr as ausr on VMC.user_id = ausr.user_id
INNER JOIN  organization as org on VMC.org_id = org.org_id
WHERE VMC.sg_type = 2
Order by VMC.name

With hundreds of vApp Templates in an environment it would be nice to have a way to set these quickly without digging through the DB each time.  There had to be a better/quicker way to do this.  I first set out to create a webpage displaying the info above.  I added a form so I can filter by organization and if they were already set to never expire.  I then changed the table so I can select a checkbox for each template I wanted to set to never expire.

Clicking the “Set to Never Expire” button loads another page that updates the DB, queries the DB again for updated info of just the templates that were modified to display changes to the DB as a confirmation page.

I searched the web to find pieces and parts to do most of this.  The page uses PHP, jquery, tablesorter, and java-script.  I’m not the best web developer so it can probably be condensed/cleaned up but if you copy the following package and update the DB connection information it should work for you as well.  Let me know if you have any questions.