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:

21 thoughts on “Converting vscsiStats data into Excel charts

  1. This script is great, thanks for sharing

    Just wanted to let everyone know that it worked for me using an excel file in office 2007, script mentions it was tested on 2003. you can also open a new excel add the script, then import data from file, use comma as separator, everything else default.
    now the fun part, interpreting the results :)

  2. I'm using excel 2003 SP3 (VB 6.5) and I get Compile error: Syntax error. It is on “ActiveChart.SetSourceData Source:=Sheeets…”.

  3. Same results. My original file was just “stats.csv”.So I do the Alt + F11, right click on VBAProject (data.csv), insert module, then run. I then get the error.

  4. Unforunatly I cannot email it. The hosts are in a secure environment. I did however try it out with excel 2007 and it worked great.

  5. The dissertation chapters should be perfectly composed by distinguished thesis service, when people want to present a writing talent. Thus, this is manifestly that
     you understand the right way to finish a superb issue referring to this topic. Thank you very much for distributing this. 

Comments are closed.