Compile and Present DiskSpd Results Using PowerShell and PowerBI

on

Over my years consulting, I've done quite a few performance tests for clients using DiskSpd (formally SQLIO), and I usually manually compile the results to display. However, I was recently working with several at once, and I thought that there should be a better/faster way to compile and present these results to the client, making them go 'wow, my SAN is slow'. So I started searching to see if anyone else had done this yet, and I found one blog post by Joey D'Antoni.

In Joey's post, he used PowerShell to parse the files to CSV. So, I gave it a shot. It didn't work. It used grep, which needs the Linux bash shell, which is available in macOS but not readily available in Windows 10 (which I just happened to be using that side of my Bootcamp at the time). There is apparently a way to work around this in Windows 10. Still, after trying to get it to work, I decided to rewrite it for Windows PowerShell as I thought; I'm probably not the only one, and installing Linux in Windows might not be allowed by some IT departments.

I'm no PowerShell guru, so I started hacking away at it and reached out to a friend of mine in our local SQL User Group who knows PowerShell pretty well and has contributed to dbtools, Josh Corrick, for some assistance.

I had several goals for this...
  1. Drop a PowerShell script into a folder, any folder, and crawl all files and folders from the current path and below for Disk Speed output files and process them.
  2. Pull more data out (in addition to what Joey had first selected) for reporting.
  3. Display this in a PowerBI report to present with.
The reason why I wanted to have it crawl from anywhere I put the script is that I could be working from either a server where I compiled the results or from my laptop, and I didn't want to have to edit a path and make sure it was correct each time I wanted results.

I typically run Diskspd and place multiple tests in one folder named the drive name tested. (see below)


So, I modified the script to get the local folder and then any files recursively that were results files, then process all of them and save them to one CSV file for PowerBI.

The additional fields/columns we added were:
  1. Server
  2. Drive
  3. Percentile
  4. Read MS Latency
  5. Write MS Latency
  6. Total MS Latency
  7. ByteSize
  8. Test Type
  9. Read Write Percentage
These were primarily added for PowerBI to filter the report.

Here is the final script.
cls
$path=$PSScriptRoot

$Files = Get-ChildItem -Path $path -Filter *.txt -Recurse | % { $_.FullName }

foreach ($file in $files)
{  
   $content = get-content $file

   $command = ($content)|select -first 1 -skip 1
   $results = ($content | Select-String -CaseSensitive 'total:' | select -First 1).tostring().replace(' ','').replace('|',',').replace('total:','')
   $maxlatency = ($content | Select-String -Pattern "max" | select -First 1).tostring().replace(' ','').replace('|',',').replace('total:','')
   $ServerName = ($content | Select-String -Pattern "computer name: " | select -First 1).tostring().replace('computer name: ','')
   $results= $results.split(",")
   $maxlatency = $maxlatency.split(",")
   $BlockSize = ($content | Select-String -Pattern "block size: " | select -First 1).tostring().replace('block size: ','')
   if ($command.Contains("-r"))
   {
        $TestType = "Random"}
   else
   {
        $TestType = "Sequential"
   }
   $match = [regex]::Match($command,'-w[0-9]+')
   if ($match.Success)
   {
        $WritePercentage=$command.Substring($match.Index+2, $Index.Index+$match.length-2)
   }
   if ($WritePercentage -eq 0)
   {
        $ReadWritePercentage = "Read 100%"
   }
   elseif ($WritePercentage -gt 0)
   {
        $ReadWritePercentage = "Write $WritePercentage%"
   }
   $Output = New-Object -TypeName PSObject -Property @{
      Server = $ServerName
      FileName = $file
      Drive = $Command.Substring($Command.indexof(":\")-1,3)
      Command = $Command
      TotalBytes = $Results[0].Trim()
      TotalIOs = $results[1].Trim()
      MiBperSec = $results[2].Trim()
      IOPs = $results[3].Trim()
      AvgLatency = $results[4].Trim()
      LatStdDev = $results[5].Trim()
      Perecentile = $maxlatency[0].Trim()
      ReadMS = $maxlatency[1].Trim().Replace("N/A",'')
      WriteMS = $maxlatency[2].Trim().Replace("N/A",'')
      TotalMS = $maxlatency[3].Trim()
      ByteSize = $BlockSize
      TestType = $TestType
      ReadWritePercentage = $ReadWritePercentage}| Select-Object Server,FileName,Drive,Command,TotalBytes, TotalIOs, MiBperSec, IOPs,AvgLatency,LatStdDev,Percentile,ReadMS,WriteMS,TotalMS,ByteSize,TestType,ReadWritePercentage

      $Output|Export-CSV $path\IOTestResults.csv -Append -NoTypeInformation
}
Now that the hard part is done let's look at PowerBI.

I've created a PowerBI template (https://bit.ly/2V0D2yW) that works off of the above script. Once you open the template, just put in the full path to your IOTestResults.csv file, and it will do the rest.


We can now filter on byte size (64k or 8k), Read/Write Percentage, Test Type (Sequential/Random), and the server. I like to throw in my laptop's test just as a comparison sometimes.

If this helps you or have any improvements, please comment below and let me know.

Comments

Post a Comment