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:
Step 1 – Creating the SQL Database
Step 2 – Inserting data into the SQL Database
Step 3 – Querying the SQL Database
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:
$SQLDataMaxHistoryInDays = "30" $LastUpdated = "{0:yyyy-MM-dd HH:mm:ss}" -f (Get-Date) $MaxHistoryDate = "{0:yyyy-MM-dd HH:mm:ss}" -f (Get-Date).AddDays(-$SQLDataMaxHistoryInDays)
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:
############################################## # Pruning VMs SQL Table ############################################## # Creating the SELECT query to find records to prune $SQLVMPruneQuery = "USE $SQLDatabase SELECT RecordID, LastUpdated FROM VMs" # Run the SELECT query $VMPruneOutput = invoke-sqlcmd -query $SQLVMPruneQuery -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword # Selecting records to delete based on those older than the MaxHistoryDate $VMRecordsToPrune = $VMPruneOutput | Where-Object { $_.LastUpdated -lt $MaxHistoryDate} # Performing DELETE for each applicable record, if value is not NULL (matching records were found outside of the max history range) If ($VMRecordsToPrune -ne $null) { ForEach($VMRecord in $VMRecordsToPrune) { $VMRecordID = $VMRecord.RecordID # Creating the DELETE query using the variables defined $SQLDeleteVMRecord = "USE $SQLDatabase DELETE FROM VMs WHERE RecordID='$VMRecordID';" # Running the DELETE query invoke-sqlcmd -query $SQLDeleteVMRecord -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword } }
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:
############################################## # Setting the variables for connecting to the SQL database ############################################## # SQL server, database & authentication $SQLInstance = ".\SQLEXPRESS" $SQLDatabase = "vSphereChangeControlv1" $SQLUsername = "sa" $SQLPassword = "Password1234!" # Max record retention history, after x configured days (any max, or just don't run this script), records are deleted from the database $SQLDataMaxHistoryInDays = "30" ############################################################################################ # Nothing to change below this line, comments provided if you need/want to change anything ############################################################################################ ############################################## # Checking to see if the SqlServer module is already installed, if not installing it ############################################## $SQLModuleCheck = Get-Module -ListAvailable SqlServer if ($SQLModuleCheck -eq $null) { write-host "SqlServer Module Not Found - Installing" # Not installed, trusting PS Gallery to remove prompt on install Set-PSRepository -Name PSGallery -InstallationPolicy Trusted # Installing module Install-Module -Name SqlServer –Scope AllUsers -Confirm:$false -AllowClobber } ############################################## # Importing the SqlServer module ############################################## Import-Module SqlServer ############################################## # Setting the current dates (for use in each SQL row inserted to prune history) ############################################## $LastUpdated = "{0:yyyy-MM-dd HH:mm:ss}" -f (Get-Date) # Subtracting the SQLDataMaxHistoryInDays from the current time to prune data older than this date $MaxHistoryDate = "{0:yyyy-MM-dd HH:mm:ss}" -f (Get-Date).AddDays(-$SQLDataMaxHistoryInDays) ############################################## # Pruning VMs SQL Table ############################################## # Creating the SELECT query to find records to prune $SQLVMPruneQuery = "USE $SQLDatabase SELECT RecordID, LastUpdated FROM VMs" # Run the SELECT query $VMPruneOutput = $null $VMPruneOutput = invoke-sqlcmd -query $SQLVMPruneQuery -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword # Selecting records to delete based on those older than the MaxHistoryDate $VMRecordsToPrune = $VMPruneOutput | Where-Object { $_.LastUpdated -lt $MaxHistoryDate} # Performing DELETE for each applicable record, if value is not NULL (matching records were found outside of the max history range) If ($VMRecordsToPrune -ne $null) { ForEach($VMRecord in $VMRecordsToPrune) { $VMRecordID = $VMRecord.RecordID # Creating the DELETE query using the variables defined $SQLDeleteVMRecord = "USE $SQLDatabase DELETE FROM VMs WHERE RecordID='$VMRecordID';" # Running the DELETE query invoke-sqlcmd -query $SQLDeleteVMRecord -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword } } ############################################## # Pruning VMDisks SQL Table ############################################## # Creating the SELECT query to find records to prune $SQLVMDiskPruneQuery = "USE $SQLDatabase SELECT RecordID, LastUpdated FROM VMDisks" # Run the SELECT query $VMDiskPruneOutput = $null $VMDiskPruneOutput = invoke-sqlcmd -query $SQLVMDiskPruneQuery -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword # Selecting records to delete based on those older than the MaxHistoryDate $VMDiskRecordsToPrune = $VMDiskPruneOutput | Where-Object { $_.LastUpdated -lt $MaxHistoryDate} # Performing DELETE for each applicable record, if value is not NULL (matching records were found outside of the max history range) If ($VMDiskRecordsToPrune -ne $null) { ForEach($VMDisk in $VMDiskRecordsToPrune) { $VMDiskRecordID = $VMDisk.RecordID # Creating the DELETE query using the variables defined $SQLDeleteVMDiskRecord = "USE $SQLDatabase DELETE FROM VMDisks WHERE RecordID='$VMDiskRecordID';" # Running the DELETE query invoke-sqlcmd -query $SQLDeleteVMDiskRecord -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword } } ############################################## # Pruning VMNICs SQL Table ############################################## # Creating the SELECT query to find records to prune $SQLVMNICsPruneQuery = "USE $SQLDatabase SELECT RecordID, LastUpdated FROM VMNICs" # Run the SELECT query $VMNICPruneOutput = $null $VMNICPruneOutput = invoke-sqlcmd -query $SQLVMNICsPruneQuery -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword # Selecting records to delete based on those older than the MaxHistoryDate $VMNICRecordsToPrune = $VMNICPruneOutput | Where-Object { $_.LastUpdated -lt $MaxHistoryDate} # Performing DELETE for each applicable record, if value is not NULL (matching records were found outside of the max history range) If ($VMNICRecordsToPrune -ne $null) { ForEach($VMNIC in $VMNICRecordsToPrune) { $VMNICRecordID = $VMNIC.RecordID # Creating the DELETE query using the variables defined $SQLDeleteVMNICRecord = "USE $SQLDatabase DELETE FROM VMNICs WHERE RecordID='$VMNICRecordID';" # Running the DELETE query invoke-sqlcmd -query $SQLDeleteVMNICRecord -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword } } ############################################## # Pruning Datastores SQL Table ############################################## # Creating the SELECT query to find records to prune $SQLDatastoresPruneQuery = "USE $SQLDatabase SELECT RecordID, LastUpdated FROM Datastores" # Run the SELECT query $DatastorePruneOutput = $null $DatastorePruneOutput = invoke-sqlcmd -query $SQLDatastoresPruneQuery -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword # Selecting records to delete based on those older than the MaxHistoryDate $DatastoreRecordsToPrune = $DatastorePruneOutput | Where-Object { $_.LastUpdated -lt $MaxHistoryDate} # Performing DELETE for each applicable record, if value is not NULL (matching records were found outside of the max history range) If ($DatastoreRecordsToPrune -ne $null) { ForEach($Datastore in $DatastoreRecordsToPrune) { $DatstoreRecordID = $Datastore.RecordID # Creating the DELETE query using the variables defined $SQLDeleteDatstoreRecord = "USE $SQLDatabase DELETE FROM Datastores WHERE RecordID='$DatstoreRecordID';" # Running the DELETE query invoke-sqlcmd -query $SQLDeleteDatstoreRecord -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword } } ############################################## # Pruning PortGroups SQL Table ############################################## # Creating the SELECT query to find records to prune $SQLPortGroupsPruneQuery = "USE $SQLDatabase SELECT RecordID, LastUpdated FROM PortGroups" # Run the SELECT query $PortGroupPruneOutput = $null $PortGroupPruneOutput = invoke-sqlcmd -query $SQLPortGroupsPruneQuery -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword # Selecting records to delete based on those older than the MaxHistoryDate $PortGroupRecordsToPrune = $PortGroupPruneOutput | Where-Object { $_.LastUpdated -lt $MaxHistoryDate} # Performing DELETE for each applicable record, if value is not NULL (matching records were found outside of the max history range) If ($PortGroupRecordsToPrune -ne $null) { ForEach($PortGroup in $PortGroupRecordsToPrune) { $PortGroupRecordID = $PortGroup.RecordID # Creating the DELETE query using the variables defined $SQLDeletePortGroupRecord = "USE $SQLDatabase DELETE FROM PortGroups WHERE RecordID='$PortGroupRecordID';" # Running the DELETE query invoke-sqlcmd -query $SQLDeletePortGroupRecord -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword } } ############################################## # Pruning Hosts SQL Table ############################################## # Creating the SELECT query to find records to prune $SQLHostsPruneQuery = "USE $SQLDatabase SELECT RecordID, LastUpdated FROM Hosts" # Run the SELECT query $HostPruneOutput = $null $HostPruneOutput = invoke-sqlcmd -query $SQLHostsPruneQuery -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword # Selecting records to delete based on those older than the MaxHistoryDate $HostRecordsToPrune = $HostPruneOutput | Where-Object { $_.LastUpdated -lt $MaxHistoryDate} # Performing DELETE for each applicable record, if value is not NULL (matching records were found outside of the max history range) If ($HostRecordsToPrune -ne $null) { ForEach($ESXiHost in $HostRecordsToPrune) { $HostRecordID = $ESXiHost.RecordID # Creating the DELETE query using the variables defined $SQLDeleteHostRecord = "USE $SQLDatabase DELETE FROM Hosts WHERE RecordID='$HostRecordID';" # Running the DELETE query invoke-sqlcmd -query $SQLDeleteHostRecord -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword } } ############################################## # Pruning Clusters SQL Table ############################################## # Creating the SELECT query to find records to prune $SQLClustersPruneQuery = "USE $SQLDatabase SELECT RecordID, LastUpdated FROM Clusters" # Run the SELECT query $ClusterPruneOutput = $null $ClusterPruneOutput = invoke-sqlcmd -query $SQLClustersPruneQuery -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword # Selecting records to delete based on those older than the MaxHistoryDate $ClusterRecordsToPrune = $ClusterPruneOutput | Where-Object { $_.LastUpdated -lt $MaxHistoryDate} # Performing DELETE for each applicable record, if value is not NULL (matching records were found outside of the max history range) If ($ClusterRecordsToPrune -ne $null) { ForEach($Cluster in $ClusterRecordsToPrune) { $ClusterRecordID = $Cluster.RecordID # Creating the DELETE query using the variables defined $SQLDeleteClusterRecord = "USE $SQLDatabase DELETE FROM Clusters WHERE RecordID='$ClusterRecordID';" # Running the DELETE query invoke-sqlcmd -query $SQLDeleteClusterRecord -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword } } ############################################## # Pruning Datacenters SQL Table ############################################## # Creating the SELECT query to find records to prune $SQLDatacentersPruneQuery = "USE $SQLDatabase SELECT RecordID, LastUpdated FROM Datacenters" # Run the SELECT query $CDatacenterPruneOutput = $null $DatacenterPruneOutput = invoke-sqlcmd -query $SQLDatacentersPruneQuery -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword # Selecting records to delete based on those older than the MaxHistoryDate $DatacenterRecordsToPrune = $DatacenterPruneOutput | Where-Object { $_.LastUpdated -lt $MaxHistoryDate} # Performing DELETE for each applicable record, if value is not NULL (matching records were found outside of the max history range) If ($DatacenterRecordsToPrune -ne $null) { ForEach($Datacenter in $CDatacenterRecordsToPrune) { $DatacenterRecordID = $Datacenter.RecordID # Creating the DELETE query using the variables defined $SQLDeleteDatacenterRecord = "USE $SQLDatabase DELETE FROM Datacenters WHERE RecordID='$DatacenterRecordID';" # Running the DELETE query invoke-sqlcmd -query $SQLDeleteDatacenterRecord -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword } } ####################### # End of script #######################
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):
vSphereChangeControlDBv1.1.zip
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,
Joshua
[…] Creating a vSphere Change Management DB for free – Step 4 of 4 […]