SQLIO results parser

Ever since I first learned about it I have been a happy user of a powershell script for parsing the results of a SQLIO test run into Excel made by Mr. Jonathan Kehayias (blog). It takes the otherwise difficult to read results file and reduces it to an excel file including a couple of “management-friendly” charts  Smilefjes.

The original script can be found here.

I have modified the script slightly, adding a chart comparing minimum and average latency, and everything was fine until the day I tried running it on a Windows 8 computer with Office 2013. For some reason the script failed to add a workbook in Excel, stating that the format might be old or the library invalid. Sadly, I didn’t get a screenshot of the exact error message, but some googling led me to the following article on stackoverflow: http://stackoverflow.com/questions/687891/exception-automating-excel-2007-with-powershell-when-calling-workbooks-add.

It seems to be related to the fact that I am in Norway and the Office 2013 installation was Norwegian. Strangely this is not a problem on my Windows 7 machine running Office 2010, but I didn’t spend a lot of time researching the underlying issue, I just added it to my mental list of Powershell commands that doesn’t work as expected. Adding the following line directly before the first call to Excel solved the problem:

 
[threading.thread]::CurrentThread.CurrentCulture = 'en-US'

Furthermore Excel 2013 only adds one worksheet when you create a new workbook, so the following commands will raise an error:

 
$WorkBook.WorkSheets.Item(3).Delete()
$WorkBook.WorkSheets.Item(2).Delete()

This is not a problem for the execution of the script though, so I left them as is for backwards compatibility.

I added the following lines to graph the latency. It was surprisingly difficult to find a method for manipulating Excel into displaying two series on the same chart via powershell, but I got there in the end.

 
$WorkBook.Charts.Add() | Out-Null
$Chart = $WorkBook.ActiveChart
$WorkBook.ActiveChart.SetSourceData($WorkSheet.Range("J2:K$x"))
$Chart.SeriesCollection(1).select()
$Chart.SeriesCollection(1).Name = '=RawData!$J$1'
$Chart.SeriesCollection(2).Name = '=RawData!$K$1'
$Chart.SeriesCollection(1).XValues = $WorkSheet.Range("M2:M$x")
$Chart.Name = "Latency"

image

You can download my version if the script here:SQLIOResults_jkl

Author: DizzyBadger

SQL Server DBA, Cluster expert, Principal Analyst

3 thoughts on “SQLIO results parser”

  1. Finally – a sqlio parse script that actually worked…

    THANK YOU…

    I do however get a small error in the functions to delete the two sheets 3 and 2
    Exception getting “Item”: “Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))”
    At E:\Sysback\SQLIO\SQLIOResults_jkl.ps1:68 char:1
    + $WorkBook.WorkSheets.Item(3).Delete()
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], GetValueInvocationException
    + FullyQualifiedErrorId : CatchFromBaseAdapterParameterizedPropertyGetValueTI

    Calling these lines in your script version
    $WorkBook.WorkSheets.Item(3).Delete()
    $WorkBook.WorkSheets.Item(2).Delete()

    1. Regarding the error messages you mention, that is as expected on office 2013. Those two lines are there for backwards compatibility with earlier versions of office, in which there are always 3 worksheets in a blank workbook. Office 2013 has only one worksheet in a blank workbook, thus you get an error when the script tries to delete the missing worksheets. I haven’t had the time to figure out a way to detect if you are running 2013 and act accordingly, but you can safely ignore this error. If you are always on office 2013, you can just remove the two lines from the script. Then the error should disappear, and the script will still work as expected.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.