Converting vscsiStats data into Excel charts

Some time ago I wrote a posting on how to use vscsiStats to gather even more data from your VMs and their SCSI performance ( See: Using vscsiStats – the full how-to). Last week I received an e-mail from Paul Dunn who had written an Excel macro that can read the output from the vscsiStats exported csv file and convert it into Excel histograms.

Using the macro is very straight forward. First you let vscsiStats run for a while and have it export the data to csv file. For example with the following command (Do pay attention to just one capital S in vscsiStats):

/usr/lib/vmware/bin/vscsiStats -p all -w id -c > /root/vscsiStats-export.csv

This will start vscsiStats and with the following command you stop the collection of data. Keep in mind that this does have a little performance impact, so don’t let it run forever.

/usr/lib/vmware/bin/vscsiStats -x

After this you download the /root/vscsiStats-export.csv file to your desktop and start Excel. Load the vscsiStat-export.csv file and open the macro window by pressing ALT+F11. Now insert the VBA code you’ll find below into the new macro sheet. After pasting it you can run the code by pressing F5. After the macro has finished you will see that it created a number of charts that are much easier to read than the raw data coming from vscsiStats.

Warning: If you just include all data from all VMs it can easily generate over 100 or 200 charts !!!

Download the macro vscsiStats-excel-macro.

EDIT: The excel macro has been tweaked/re-written. Paul Dunn used some suggestions from Matt Kelliher and implemented them. Check it out: DunnSept’s Blog

Paul Dunn has been in IT in Higher Education for 18 years. Paul has a Master’s degree in Information Resource Management and along with his job duties as sys admin, teaches database design and modeling, networking and telecommunications.

eesh.. sounds horrible. I’m really just a lacky at a medium-sized university. Jack of all trades, master of none ;-) I do everything here, from dba to programmer, sysadmin, web monkey, to photographer & videographer.

Other important resources on vscsiStats are these two blogposts from Duncan: