RVTools & Excel - A Great Way to Pull VMware Stats

Growing up as a kid and now as an adult who loves sports, I have always been a bit obsessed with statistics. Stats, Stats, Stats. I find statistics to be a helpful way to baseline, compare, and motivate.

So it is no suprise that when I was introduced, courtesy of Duncan Epping, to a free product that would allow me to collect and grab stats on my VMware environment, I had to learn more.

Screen Shot 2014-03-07 at 8.56.01 AM.png

The tool is called RVTools and is a .NET Application that collects lots of great information about your VMs, Hosts, CPU, Memory, Datastores, Network Switches, Snapshots....the list goes on and on. It displays the output in a tabular view and is super easy to export. I have found that coupled with the magic of Excel pivot tables (a must for anyone obessed with stats like me), you can quickly create and share some great insights about your VMware landscape.

Let me show you some of the insights I have gathered on a sample VMware environment:

  • Overview: ESXi v. 5.0 / 14 Hosts / 372 VMs / 5 Clusters / 139 DataStores / DataCenters
  • VM O/S: 22 Flavors, 34% W2K8R2, 27% VDI, 16% W2K3 Standard (x32)
  • VM Info: 372 Total (241 Powered On), 2 vCPU, 5GB vRAM, 2 vDisks
  • CPU: 28 Sockets, 148 Cores, 45 GHz, 607 vCPUs, 4.2 vCPU/Core (Avg.)
  • Memory: 2.5 TB Physical, 1.8 TB Allocated, 40% Avg. Utilization
  • Storage - NFS: 13 DataStores, 106 TB Capacity, 20.5 TB Provisioned, 20.5 Utilized
  • Storage - VMFS: 126 DataStores, 146.5 TB Capacity, 104.5 TB Provisioned, 58 TB Utilized
  • Snapshots: 10 Active

This is a great summary of information that can easily be shared among your team, managment, or fellow VMware friends. I like to think of it as the 'State of the Union' for my VMware environment. Of course, no envioronment is created equal, so mileage may very on what your data looks like - but the important thing is that this information shows that you are 'in command' of the environment that you support.

How I got here.

  1. Install RVTools
  2. Run it against your vCenter server by providing credentials(nothing scary here, it utlizes the VI SDK so all standard VMware APIs being called)
  3. Peruse the tabs that come back....vInfo, vCPU, vMemory, vHosts, vDataStores, vNetwork, and vHealth are my personal favorites.
  4. Export to Excel using File > Export all to Excel
  5. Open up the exported file in Excel and browse to the vInfo tab
  6. Select all of the Data on this tab (I usually perform a Ctrl+A, but you can also hit the Select All button located in the top left corner of the worksheet.
  7. Create a Pivot Table...Mileage will vary depending on what version of Excel you are using for this next step...in Office 2007, it is located on the Insert > Pivot Table, on a Mac it is located in Data > Pivot Table. When prompted open the Pivot table up as a new sheet.
  8. Stop & Breathe.....Pivot Tables are extremely powerful but can be a bit overwhelming if you are not use to using them. We will go through one Pivot here and build on them in future posts if there is interest.
  9. For this post, we will find the number of VMs, Average # of vCPUs and vRAM per VM, % of running O/S, and in use Memory.
  10. Using the Excel Pivot Table Builder, clear out all of the default entries by dragging and dropping values out of the Row, Values, & Columns Field.
Screen Shot 2014-03-06 at 10.42.47 AM.png

Now Find and Drag the following Entries into the appropriate Pivot Table fields:

        1. Values Field: **VM, Memory, CPUs,**
        2. Row Labels Field: **Datacenter, OS**

You will now have something that should look similar to this.

Screen Shot 2014-03-06 at 11.13.14 AM.png

Let's not stop there....counts are great and everything but let's look at some averages and %'s. To do that drag another 'instance' of VM and In Use MB into the Values Field so that the Values Field looks like this:

Screen Shot 2014-03-06 at 12.08.00 PM.png

Let's Modify Count of VM2 to a % by clicking on it in the Values Field, Hitting Options, and selecting % of column. I like to also change the Field Name to % of Total VMs

Screen Shot 2014-03-06 at 12.11.45 PM.png

Perform a simliar step for CPUs, changing from a SUM of CPUs to an Average of CPUs and Sum of Memory to Average of Memory. I aslo added the Powerstate field to the Report Filter so that I can easily filter between powered off, powered on, or all VMs.

Screen Shot 2014-03-07 at 8.41.21 AM.png
Screen Shot 2014-03-07 at 8.40.27 AM.png

Looking at the final results we can see that we have a 372 VMs, which average around 2 vCPUs and 5GB vRAM. A third of these are W2K8 R2, 25% are Win7 (VDI), and then the rest is a flavor of other stuff including many distros of Linux (virtual appliances mostly). You can also peform a quick filter on powered on VMs and see that of the 372 VMs only 241 are powered on.

This is insightful and once you get comforatable with Pivot Tables then you can get highly creative. Play around with adding, sorting, moving things around.

In another post I will share some insights I gathered about Hosts & Datastores. For now, the inner stats geek has been satisfied.