RVTools & Excel - Part Deux

In this post we are going to build upon the RVTools data that we worked with in my previous post and take a dive deeper into some stats for Hosts & Datastores. Building on what we did before at the VM level, this time we will be working with the vHosts and the vDataStores tabs within our Excel export of RVTools.

Specifically I am interested in the gathering the following information:

  • Hosts: Count per Cluster, # of Sockets, Cores, Speed, vCPUs/Core, Total Physical Memory, & Allocated vRAM.
  • Storage: # of Datastores broken down by type: NFS and VMFS, Capacity, Provisioned, Utilized, & Free.

Following our reciepe from last time (you can skip to step 5, if you have been following along in this series)

  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 vHosts 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 new Pivot Table...Mileage will vary depending on what version of Excel you are using for this next step...in Office 2010, 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. 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.
  9. Now Find and Drag the following Entries into the appropriate Pivot Table fields:
    • Values Field: Host, # CPU, # Cores, Speed, # vCPUs, vCPUs per Core
    • Row Label Field: DataCenter, Cluster, Host

You will now have something that should look similar to the sheet below, which if you update vCPU/core to an AVERAGE rather then a SUM will allow us to achieve what we were looking for.

Screen Shot 2014-03-19 at 2.44.15 PM.png

You could easily break this out by ESX version, Model, Domain, or EVC type for that matter. One of the wonders of Pivot Tables is that you can slice and dice any type of relational data quickly.

Let's complete the information for our hosts by adding the following memory items in the Values Field of our Pivot Table: # Memory, vRAM, Memory Usage % to get an idea of the memory footprint we have committed and are using.

This results in a nice summary statement regarding our Hosts:

  • CPU: 14 hosts, 28 Sockets, 148 Cores, 45 GHz, 607 vCPUs, 4.2 vCPU/Core (Avg.)
  • Memory: 2.5 TB Physical Memory, 1.18 TB vRAM, 40% Avg. Mem Utilization

Now let's turn our attention to Storage by repeating steps 6-10 using the vDataStore tab. We will be using the following entries in our Storage Pivot:

  • Values Field: Name, Capacity MB, Provisioned MB, In Use MB, Free MB
  • Row Label Field: Type, Name

This allows you to quickly share information about our Storage:

  • NFS: 13 DataStores, 106 TB Capacity, 20.5 TB Provisioned, 20.5 Utilized, 85.6 TB Free
  • VMFS: 126 DataStores, 146.5 TB Capacity, 104.5 TB Provisioned, 58 TB Utilized, 88.7 TB Free

Other areas that may be of interest relating to storage that are interesting to map out on this Pivot are: Block size, VMFS Major Version, # of VMs/Datastore

If you have found these last two posts helpful please share your favorite Pivots using RVTools data. In the next post we can spend some time looking at how to catch anomolies within a VMware environment as we strive to maintain consistency.