In my previous 2 blog posts I took you through creating a vSphere Change Management Database, how to insert data using PowerCLI, and in this post, I’ll show you some example queries you can run once you have the data. If you missed the first 2 posts check them out here:
Step 1 – Creating the SQL Database
Step 2 – Inserting data into the SQL Database
There are probably hundreds of different things you can derive from the data captured. So here is just a sample of what I’m going to show you:
- Show all configurations of a VM across all data logged
- Taking oldest date available and showing all VM configs on that datetime
- Taking the most recent data available and showing total VM size provisioned and used (useful for Rubrik sizing)
- Using the oldest and most recent VM used size to calculate the total VM used storage growth % (also useful for Rubrik sizing!)
- Show all VMDisk and VMNIC configurations for a VM
- Show all configurations for a VMDK or VMNIC
- Show all configurations for a Port Group on a host
- Show all configurations of a Cluster and Host
- Taking the oldest date available and showing all original Cluster and Host configs
- Show all current and historical stats for a Datacenter (including cluster, host and VM counts etc)
To make your life easier I’ve put them all together, with comments, in the script below:
##############################################
# Configure the variables below for connecting to the SQL database
##############################################
$SQLInstance = ".\SQLEXPRESS"
$SQLDatabase = "vSphereChangeControlv1"
$SQLUsername = "sa"
$SQLPassword = "Password123!"
##############################################
# 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
##############################################
# Querying all the tables and assigning them to variables for subsequent use
##############################################
# VM table
$VMsTableQuery = "USE $SQLDatabase
SELECT * FROM VMs"
$VMsTable = invoke-sqlcmd -query $VMsTableQuery -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
# VMDisks table
$VMDisksTableQuery = "USE $SQLDatabase
SELECT * FROM VMDisks"
$VMDisksTable = invoke-sqlcmd -query $VMDisksTableQuery -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
# VMDiskUsage table
$VMDiskUsageTableQuery = "USE $SQLDatabase
SELECT * FROM VMDisks"
$VMDiskUsageTable = invoke-sqlcmd -query $VMDiskUsageTableQuery -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
# VMNICs table
$VMNICsTableQuery = "USE $SQLDatabase
SELECT * FROM VMNICs"
$VMNICsTable = invoke-sqlcmd -query $VMNICsTableQuery -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
# Datastores table
$DatastoresTableQuery = "USE $SQLDatabase
SELECT * FROM VMNICs"
$DatastoresTable = invoke-sqlcmd -query $DatastoresTableQuery -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
# PortGroups table
$PortGroupsTableQuery = "USE $SQLDatabase
SELECT * FROM PortGroups"
$PortGroupsTable = invoke-sqlcmd -query $PortGroupsTableQuery -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
# Hosts table
$HostsTableQuery = "USE $SQLDatabase
SELECT * FROM Hosts"
$HostsTable = invoke-sqlcmd -query $HostsTableQuery -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
# Clusters table
$ClustersTableQuery = "USE $SQLDatabase
SELECT * FROM Clusters"
$ClustersTable = invoke-sqlcmd -query $ClustersTableQuery -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
# Datacenters table
$DatacentersTableQuery = "USE $SQLDatabase
SELECT * FROM Datacenters"
$DatacentersTable = invoke-sqlcmd -query $DatacentersTableQuery -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
##############################################
# Example queries from the VM table
##############################################
# Show all configurations of a VM
$VMHistory = $VMsTable | Where-Object {$_.Name -eq "DC1vCenter"}
$VMHistory | Sort-Object LastUpdated | Out-GridView -Title "VM History"
# List all unique dates in the VM table from which to take a sample
$VMDatesAvailable = $VMsTable | select LastUpdated -Unique
# Taking oldest date available and showing all VM configs
$VMOldestDateSelected = $VMDatesAvailable | Sort-Object LastUpdated | select -ExpandProperty LastUpdated -First 1
$VMsTable | Where-Object {$_.LastUpdated -eq $VMOldestDateSelected} | Out-GridView -Title "VM Point In Time Config"
# Taking most recent data available and showing total VM size provisioned and used (useful for Rubrik sizing)
$VMNewestDateSelected = $VMDatesAvailable | Sort-Object LastUpdated | select -ExpandProperty LastUpdated -Last 1
$VMProvisionedSpaceGB = $VMsTable | Where-Object {$_.LastUpdated -eq $VMNewestDateSelected} | select -ExpandProperty ProvisionedSpaceGB | Measure-Object -Sum | Select-Object -ExpandProperty Sum
write-host "Total VM ProvisionedSpace (GB): $VMProvisionedSpaceGB"
$VMUsedSpaceGB = $VMsTable | Where-Object {$_.LastUpdated -eq $VMNewestDateSelected} | select -ExpandProperty UsedSpaceGB | Measure-Object -Sum | Select-Object -ExpandProperty Sum
write-host "Total VM UsedSpace (GB): $VMUsedSpaceGB"
# Taking most recent and oldest data available and then calculating growth over the period (also useful for Rubrik sizing!)
$VMOldestUsedSpaceGB = $VMsTable | Where-Object {$_.LastUpdated -eq $VMOldestDateSelected} | select -ExpandProperty UsedSpaceGB | Measure-Object -Sum | Select-Object -ExpandProperty Sum
$VMNewestUsedSpaceGB = $VMsTable | Where-Object {$_.LastUpdated -eq $VMNewestDateSelected} | select -ExpandProperty UsedSpaceGB | Measure-Object -Sum | Select-Object -ExpandProperty Sum
# Calculating time sample in days
$TimeSpan = New-Timespan –Start $VMOldestDateSelected –End $VMNewestDateSelected | Select -ExpandProperty TotalDays
# Calculating growth percentage
$Diff = $VMNewestUsedSpaceGB - $VMOldestUsedSpaceGB
$PercentDiff = ($Diff / $VMOldestUsedSpaceGB) * 100
write-host "Time Sample in Days: $TimeSpan
Total VM UsedSpace Growth (%): $PercentDiff"
##############################################
# Example queries from the VMDisks table
##############################################
# Show all VMDisk configurations for a VM
$VMDiskConfigHistory = $VMDisksTable | Where-Object {$_.Parent -eq "DC1vCenter"}
$VMDiskConfigHistory | Sort-Object LastUpdated | Out-GridView -Title "VMDisk Config History"
# Show all configurations for a VMDK
$VMDiskConfigHistory = $VMDisksTable | Where-Object {($_.Parent -eq "DC1vCenter") -and ($_.Name -eq "Hard disk 1")}
$VMDiskConfigHistory | Sort-Object LastUpdated | Out-GridView -Title "VMDisk Config History"
##############################################
# Example queries from the VMNICs table
##############################################
# Show all VMNIC configurations for a VM
$VMNICConfigHistory = $VMNICsTable | Where-Object {$_.Parent -eq "DC1vCenter"}
$VMNICConfigHistory | Sort-Object LastUpdated | Out-GridView -Title "VMNIC Config History"
# Show all configurations for a VMNIC
$VMNICConfigHistory = $VMNICsTable | Where-Object {($_.Parent -eq "DC1vCenter") -and ($_.Name -eq "Network adapter 1")}
$VMNICConfigHistory | Sort-Object LastUpdated | Out-GridView -Title "VMNIC Config History"
##############################################
# Example queries from the PortGroupsTable table
##############################################
# Show all configurations for a Port Group on a host
$PortGroupHistory = $PortGroupsTable | Where-Object {($_.Name -eq "VM Network") -and ($_.VMHostId -eq "HostSystem-host-74")}
$PortGroupHistory | Sort-Object LastUpdated | Out-GridView -Title "Host Port Group History"
##############################################
# Example queries from the Hosts table
##############################################
# Show all configurations of a Host
$HostHistory = $HostsTable | Where-Object {$_.Name -eq "192.168.0.13"}
$HostHistory | Sort-Object LastUpdated | Out-GridView -Title "Host History"
# List all unique dates in the Host table from which to take a sample
$HostDatesAvailable = $HostsTable | select LastUpdated -Unique
# Taking oldest date available and showing all Host configs
$HostDateSelected = $HostDatesAvailable | Sort-Object LastUpdated | select -ExpandProperty LastUpdated -First 1
$HostsTable | Where-Object {$_.LastUpdated -eq $HostDateSelected} | Out-GridView -Title "Host Point In Time Config"
##############################################
# Example queries from the Clusters table
##############################################
# Show all configurations of a Cluster
$ClusterHistory = $ClustersTable | Where-Object {$_.Name -eq "ProdCluster1"}
$ClusterHistory | Sort-Object LastUpdated | Out-GridView -Title "Cluster History"
# List all unique dates in the Cluster table from which to take a sample
$ClusterDatesAvailable = $ClustersTable | select LastUpdated -Unique
# Taking oldest date available and showing all Host configs
$ClusterDateSelected = $ClusterDatesAvailable | Sort-Object LastUpdated | select -ExpandProperty LastUpdated -First 1
$ClustersTable | Where-Object {$_.LastUpdated -eq $ClusterDateSelected} | Out-GridView -Title "Cluster Point In Time Config"
##############################################
# Example queries from the Datacenters table
##############################################
# Show all stats for a Datacenter
$DatacenterHistory = $DatacentersTable | Where-Object {$_.Name -eq "Datacenter1"}
$DatacenterHistory | Sort-Object LastUpdated | Out-GridView -Title "Datacenter History"
# List all unique dates in the Datacenter table from which to take a sample
$DatacenterDatesAvailable = $DatacentersTable | select LastUpdated -Unique
# Taking oldest date available and showing all Datacenter stats
$DatacenterDateSelected = $DatacenterDatesAvailable | Sort-Object LastUpdated | select -ExpandProperty LastUpdated -First 1
$DatacentersTable | Where-Object {$_.LastUpdated -eq $DatacenterDateSelected} | Out-GridView -Title "Datacenter Point In Time Config"
#######################
# End of script
#######################
You can also download these examples .ps1 format below:
vSphereChangeControlDBv1.1.zip
How cool was that? Hopefully, by now you can start to see just how useful this little tool can be. But you may have a nagging question in your head. I’ll take a guess that it might be along the lines of “this is great, but I don’t want to store the data forever, so how can I prune it?”. My thoughts exactly. In the final blog post, I will show you how to prune the data to only retain the history length you desire. You can check it out here:
Creating a vSphere Change Management DB for free – Step 4 of 4
Happy scripting,
Joshua
[…] Creating a vSphere Change Management DB for free – Step 3 of 4 […]