Creating a vSphere Change Management DB for free – Step 4 of 4

For my final blog post in the 4-part series on how to create a vSphere Change Management Database for free, I’m going to show you how to prune the data. If you missed the first 3 posts check them out here:

Depending on the size of your vSphere environment or your data retention policies you probably don’t want to log vCenter settings indefinitely. Because we designed the database to always contain a RecordID in each table along with a LastUpdated datetime, we can use this to prune the data.

To start we need a variable for the number of days we want to retain, then we can use the cool PowerShell feature of being able to add/subtract days from a datetime to calculate the date after which records should be pruned:

The datetime format specified ensures the data is compatible with the SQL datetime format. Now we can select all the RecordIDs from a table where $LastUpdated is older than $MaxHistoryDate to get a list of records to delete. Here you can see the process for the VM table:

To test you can simply change the $SQLDataMaxHistoryInDays to 0, and you will see all the records deleted as they will be older than the $MaxHistoryDate. To repeat this for all the tables in the database copy the script below:

I recommend scheduling this on the same schedule that you run the insert script, I.E hourly, daily, weekly etc, to prune the old data. You can also download the script in .ps1 format below (along with all the scripts I’ve provided in this 4 part series):

So that’s it! If you’ve followed me through all 4 posts you can now create, update, and prune your own vSphere Change Management Database for free! If you found this cool please like and share. Let me know if you have any feedback, questions or just to tell me if you put it into production, as I love to hear from readers. Happy scripting,