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 […]