Press "Enter" to skip to content

Logging vCenter data into SQL with the vSphereCMDB v1.1

Joshua Stenhouse 2

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, including 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

vCMDB VM Report Example

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,


  1. Chris Smith Chris Smith

    I like this post. I’ve done the same saving data from powercli queries, and then graphing out the results.. this is a nice post and the concept of storing other info I want in a separate DB is actually quite useful.

    • Joshua Stenhouse Joshua Stenhouse

      Thanks Chris! Glad you liked it.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: