VMware Capacity Planner: taking the data offline (Part 3: Getting CSV data)

This is part 3 of a series concerning VMware Capacity Planner.

First a general remark about the update to version 2.6 of the tool: it was not always pleasant to use the web interface the last week because of downtime (scheduled maintenance), errors in the interface, latency, etc. It all feels normal again, and some of the new features where definitely worth it.

Back to business: What interests me most when it comes to downloading data from the website is the pure performance data. VMware averages this information over a week, so data can be downloaded on a weekly basis.

I'm not going into the full details of everything that can be tweaked, configured or queried but most of it will be clear when looking at the actual command-line to get a CSV file. Most important is understanding that some of the choices you make are based on the session that is open on te server, and are not selected using POST or GET. Some of these options can be configured on the html view, but not the export view (although the syntax is available) so we have to apply a trick: first get the html view for the type of data we want and then export the data.

Below, you find a typical command-line to download the core VMware statistics for week 45 of a company with ID 1234:

wget --keep-session-cookies --load-cookies cookies.txt
--save-cookies cookies.txt https://optimize.vmware.com/inv_report.cfm?page=ViewStatsMain2.cfm&action=export§ion=View&sort=ObjectName&sortDir=ASC&YearWeek=200745&Mon=2&ISO=2&opt=P&PerfGroupID=1&grpt=S&HostType=0&menutab=Performance --post-data CID=1234&YearWeek=200745 --no-check-certificate -O Stats.csv

Note that quite some things have to be specified: week, company, type of average, type of metrics, system group, etc.

Drop me a line or leave a comment in case you want to know more about the precise parameters that are important or in case you're interested in my little script that does the magic for me.

This concludes the practical part of this series. I plan to write more about capacity planning and capacity monitoring in general later...

Word vs. Excel

I stumbled upon the iWork suite (aka Office for Mac) already some time ago, but today thought back of it as I was thinking about Excel and Word...

The main problem I have with Excel and Word is that there is nothing in-between. Let me explain what I mean by means of some examples:

Text-based tables: Excel (as most spreadsheets) is a numerical calculation sheet application. Although it has some functions to work with strings, it is not the primary goal of the application. This is fine, but in practice, we see that a lot of people (and even big companies) use it to store text-based data (e.g.: IP ranges, Application overview, user accounts, passwords, etc.)

Side-by-side comparison: this is in fact an example of the previous point: how many times do we not want to compare pros and cons of something? Excel is not really optimized for this, but a table in Word is even worse.

Documents with a lot of tables: This really is on the boundary of both products. In order to keep the exact formatting as set in Excel, I usually copy/past the table into Word as a picture. This is not very efficient in terms of having to change some numbers in the tables. Using the OLE features with a conventional copy/paste is usually not an option as the table in Word does not look half as nice as it should.

Reports: This is again a special case of the previous point: sometimes the data in the tables is expected to change because it contains for instance KPI information, or extracts from a database. Using Excel to nicely print a paper/PDF report is hard, copying all the tables and graphs to a Word document every day or week is even harder. In practice, I solve this by using a macro that automatically creates a Word document and pastes the tables and graphs (as pictures) where they belong. This approach has been the most successful for me up till now.

It seems iNumber makes a step in the right direction. Take a look at some of the demos on the page and see for yourself: graphics, text and tables are mixed easily.

Is this another 'sign' I have to switch to the Mac?

VMware Capacity Planner: taking the data offline (Part 2: Cygwin & wget)

I started thinking about avoiding the manual exports from the VMCP website and remembered I used WGET in the past for this kind of stuff. The difference with before was that this time, I needed to login to the website before being able to get data from it.

Login in to the VMCP website is done using a POST method and luckily wget supports that. The next thing is to understand what the post data needs to be. This can be fetched from the source of the logon page:


The relevant POST data is derived to be

fuseaction=Security
Page=users_login.cfm
username=???
password=???


Since I run Windows XP on my laptop, I use Cygwin to run wget. Cookies are used to store a session ID for you logon session, so wget has to be told to store those cookies in a file. This is the resulting command line:


wget --keep-session-cookies --save-cookies cookies.txt 'https://optimize.vmware.com/index.cfm' --post-data 'fuseaction=Security&Page=users_login.cfm&username=???&password=???' --no-check-certificate -O output.html


Parsing the output.html file, you should be able to see whether logon was succesful. The success depends on many factors (local proxy, network settings, username/password, etc.). You can get more info by adding suitable options to wget.

This concludes part 2 of this series of articles. In this part, we used wget to logon to the VMware capacity planner website.

VMware Capacity Planner: taking the data offline (Part 1: Introduction)

Lately, I have been involved in some VMware Capacity Planner (VMCP) tracks. VMCP deals with monitoring a set of (physical) servers in order to assess possible virtualization scenarios.

VMCP works in the following way: a monitoring server is set-up with a tool that does (but is not limited to) regular performance measurements. This tools sends the data to a VMware database through the web (over a secure channel). Via a web interface, one can then query the information, get reports, view trends, show graphs, configure and create consolidation scenarios, etc. Usually, we let the system run for around 4 weeks to get a realistic idea of the performance characteristics.

What I like about VMCP is that the data is not located at the customer site, and available at all times (once it has been uploaded). This gives me the opportunity to regularly check on the status of the performance measurements.

The biggest disadvantage of VMCP is that the web interface is not the most flexible and fast interface around. Some things I would like to do are not available (lack of flexibility) but could easily be done in, e.g., Excel and at times when everyone in the world is awake it takes ages to refresh a page and get the result of a query. Moreover, it is not easy to get good-looking information to paste in a document.

When it comes to writing a report, the customer is obviously not only interested in a statement like: you will need 5 ESX server of type X to cope with the load. Therefore, I like to add tables with the most useful metrics (CPU, network, Disk I/O, ...) for later reference. I add this information as an appendix.

This is where I would spend at least half a day exporting CSV files from the VMCP website, loading them in Excel, laying it out as a nice table and paste it in the document. I started thinking about automating some of the steps required, and I covered the most time-consuming already: exporting the information from the website as a CSV file.

In the following part, I'll explain how I started this little adventure...

VDI vs. TS/CTX

Check out this blog article by Brian Madden.