Have you ever had a situation where you needed to know the settings a VM had 1 month ago? Wanted to see historical VM disk usage? Track the growth of VMs in a cluster? Somebody renamed a VM and you can’t find it anymore? Today, with just a vCenter, once the change is made the data has gone and you can’t see what it was before.
All these challenges can be solved by creating a vSphere Change Management Database (vCMDB). Using PowerShell and Microsoft SQL Server it’s possible to log all the vSphere information into a SQL database on an hourly, daily, or weekly basis. You can then easily report on the data from any point in time. Pretty useful huh?
To get started, install a SQL Server if you don’t already have one (any version of express works fine). You can get it here. Next, download the below zip file:
Included are 5 PowerShell scripts to run in the following order:
- vSphereCMDBv1.1-Auth.ps1
Prompts you to enter the SQL and vCenter server credentials to store securely for each subsequent script. It also installs SqlServer and PowerCLI modules if not already present on the host. Requires run as administrator to install the modules. - vSphereCMDBv1.1-Create.ps1
Automatically creates the DB in which to store the vSphere data with the required structure. Only needs to be run once, but can be repeated with different DB names. - vSphereCMDBv1.1-Insert.ps1
Connects to the vCenter specified at the start of the script, runs multiple get-*, then inserts the data into the vCMDB. This should be run on the desired DB update frequency (typically daily or weekly). Create copies for multiple vCenters. - vSphereCMDBv1.1-Query.ps1
Contains example queries you can run against your vCMDB. - vSphereCMDBv1.1-Prune.ps1
Removes any DB records beyond the configured number of days retention, default 365. Run as often as desired or not at all for infinite retention.
Each script is configured to connect to a local SQLEXPRESS instance, but you can easily change the variable to connect to a remote SQL server too. If the SqlServer or PowerCLI modules aren’t installed on the host they are automatically installed from the PSGallery.
Once you’ve started logging data into your vCMDB you’ll be amazed at the information you can now ascertain that was previously unknown. Here are some of my top examples:
- All configurations of a VM, oldest, last reported, everything in-between
- Growth percentage of VMs over time
- All VMDK configurations for a VM
- All VMNIC configurations for a VM
- Port group list and config
- Host list, config, total VMs, VMDKs, NICs, Datastores and Port Groups per host (great for balancing and see if a host isn’t seeing all the Datastores that it should!)
- Cluster list, config, and total hosts, VMs, VMDKs, NICs and Datastores and per cluster
- Datacenter list, config, and total clusters, VMs, VMDKs, NICs, Datastores and Port Groups per Datacenter
This is my gift for you to implement and take as your own within whether you work. All I ask is that if you use it professionally, then please add a comment saying what you used it for and I hope you found it useful! Happy scripting,
Joshua