Yet another diskspd Powershell wrapper

param(
    [Parameter(Mandatory=$false)]
    [ValidateSet('512', '1024', '2048', '4096', '8KB', '16KB', '32K', '64K', '128K', '256K', '521K', '1M', '2M')]
    [string]$Blocksize  = '64k',
    [Parameter(Mandatory=$false)]
    [string]$Filesize   = '8G',
    [Parameter(Mandatory=$false)]
    [int]$Filecount      = 4,
    [Parameter(Mandatory=$false)]
    [int]$Threadcount    = 8,
    [Parameter(Mandatory=$false)]
    [int]$WritePercent   = 50,
    [Parameter(Mandatory=$false)]
    [int]$OutstandigIOs  = 32,
    [Parameter(Mandatory=$false)]
    [int]$DurationInSeconds = 10,
    [Parameter(Mandatory=$false)]
    [string]$Filepath,
    [Parameter(Mandatory=$false)]
    [string]$DiskSpdPath = "c:\tools\diskspd\diskspd.exe",
    [Parameter(Mandatory=$false)]
    [bool]$StoreInDatabase = $true,
    [Parameter(Mandatory=$false)]
    [string]$Computername

)

# How to call me:
# .\DoDiskSpdTest.ps1 -Blocksize 64K -Filesize 8G -Filecount 8 -Threadcount 32 -WritePercent 50 -OutstandigIOs 32 -DurationInSeconds 120 -Filepath d:\sqlserverfiles -Computername MYSQLSERVERNAME


Add-Type -Assembly System.IO.Compression.FileSystem;

$scriptroot = (Get-Location).Path;
$diskspdDownloadPath = "https://github.com/microsoft/diskspd/releases/download/v2.0.21a/DiskSpd-2.0.21a.zip";
$localZipPath = $([System.IO.Path]::Combine($scriptroot, "diskspd"));
$localZipFilePath = $([System.IO.Path]::Combine($scriptroot, "diskspd", "diskspd.zip"));
$localDiskSpdPath = $DiskSpdPath.Replace("\diskspd.exe", "");
$remoteDiskSpdPath = $DiskSpdPath.Replace("$($DiskSpdPath.Substring(0,3))", "\\$($Computername)\$($DiskSpdPath.Substring(0,1))$\");
$remoteDiskSpdFilePath = $DiskSpdPath.Replace("$($DiskSpdPath.Substring(0,3))", "\\$($Computername)$($DiskSpdPath.Substring(0,1))$\").Replace("\diskspd.exe", "");
$sqlServerName = "LOGGING_SQLSERVERNAME";
$sqlServerDatabase = "LOGGING_DATABASE";


if($(Test-Path $localZipPath) -eq $false)
{
    New-Item -ItemType Directory -Force $localZipPath;
}

# we use SQL-Server file extensions to be sure that no anti-virus comes in the game 
$files = @();
for($x=1; $x -le $Filecount; $x++)
{
    $filename = [IO.Path]::Combine($Filepath, $x.ToString("D2"));
    if($x -eq 1)
    {
        $filename += ".mdf";    
    }
    else
    {
        $filename += ".ndf";    
    }

    $files += $filename;
}


# connect a powershell session to the target-computer
# if "diskspd.exe" is already there, we have no need to download an copy it there
$session = New-PSSession -ComputerName $Computername;
$diskspdExePresent = Invoke-Command -Session $session -ArgumentList $DiskSpdPath, $localDiskSpdPath -ScriptBlock { param([string]$DiskSpdPath, [string]$localDiskSpdPath) if($(Test-Path $DiskSpdPath) -eq $false) { New-Item -Path $localDiskSpdPath -ItemType Directory -Force | Out-Null; return 1 } else { return 0; } }
$session | Remove-PSSession;


if($diskspdExePresent -eq 1)
{
    Invoke-WebRequest -Uri $diskspdDownloadPath -Method Get -OutFile $localZipFilePath;

    $zip = [IO.Compression.ZipFile]::OpenRead($localZipFilePath)
    $zip.Entries | where { $_.FullName -eq "AMD64/diskspd.exe" } | % { [System.IO.Compression.ZipFileExtensions]::ExtractToFile($_, "$localZipPath\diskspd.exe", $true); }
    $zip.Dispose();

    Remove-Item $localZipFilePath -Force;

    # some older operating systems do not support Copy-ToSession, so we`re using the old style
    $remoteDiskSpdPath;
    Copy-Item -Path "$localZipPath\diskspd.exe" -Destination $remoteDiskSpdPath;
}


# get the current timestamp from the local system - used later when logging
$executionTime = $(Get-Date).ToString("yyyy-MM-dd hh:mm:ss.fff");

# build the diskspd execution string
$exeString = "& $($DiskSpdPath) -Rxml -b$($Blocksize) -c$($Filesize) -t$($Threadcount) -Suw -w$($WritePercent) -r -o$($OutstandigIOs) -d$($DurationInSeconds) -L $($files -join ' ')";

$sb = [scriptblock]::create($exeString);

# execute diskspd on the remote host
# the result is stored in XML in the $test-variable
[xml]$test = $(Invoke-Command -ComputerName $Computername -ScriptBlock $sb);


# building a psobject in order to store the result in a database
$myObject = New-Object -TypeName psobject;
$myObject | Add-Member -MemberType NoteProperty -Name 'ExecutionTime' -Value $executionTime -TypeName "System.String";
$myObject | Add-Member -MemberType NoteProperty -Name 'Computername' -Value $test.Results.System.ComputerName;
$myObject | Add-Member -MemberType NoteProperty -Name 'AverageReadLatencyMs' -Value  $([int]($test.Results.TimeSpan.Latency.AverageReadMilliseconds));
$myObject | Add-Member -MemberType NoteProperty -Name 'AverageWriteLatencyMs' -Value $([int]($test.Results.TimeSpan.Latency.AverageWriteMilliseconds));
$myObject | Add-Member -MemberType NoteProperty -Name 'ReadMegabytes' -Value  $([int](($test.Results.TimeSpan.Thread.Target.ReadBytes   | Measure-Object -Sum).Sum  / 1MB));
$myObject | Add-Member -MemberType NoteProperty -Name 'WriteMegabytes' -Value $([int](($test.Results.TimeSpan.Thread.Target.WriteBytes  | Measure-Object -Sum).Sum  / 1MB));
$myObject | Add-Member -MemberType NoteProperty -Name 'IOCount' -Value $([int](($test.Results.TimeSpan.Thread.Target.IOCount | Measure-Object -Sum).Sum));
$myObject | Add-Member -MemberType NoteProperty -Name 'DurationInSeconds' -Value $DurationInSeconds;
$myObject | Add-Member -MemberType NoteProperty -Name 'TestedDrive' -Value $Filepath  -TypeName "System.String";
$myObject | Add-Member -MemberType NoteProperty -Name 'FileCount'   -Value $Filecount;


# building a sql-statement dynamically of that object
if($StoreInDatabase -eq $true)
{
    $columnList =  $($myObject.PsObject.Properties.Name -join ",");

    $valueList = @();

    $myObject.PsObject.Properties | % {
         if($_.TypeNameOfValue -eq "System.String")
         {
             $valueList += "'" + $_.Value + "'";
         }
         else
         {
             $valueList += $_.Value;
         }
    }

    $insertQuery = "INSERT INTO [DiskSpdTests] ($columnList) VALUES($($valueList -join ','))";

    Invoke-Sqlcmd -ServerInstance $sqlServerName -Database $sqlServerDatabase -Query $insertQuery ;
}

USE [DATABASENAME]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DiskSpdTests](
	[Id] [INT] IDENTITY(1,1) NOT NULL,
	[ExecutionTime] [DATETIME] NOT NULL,
	[Computername] [NVARCHAR](128) NOT NULL,
	[AverageReadLatencyMs] [INT] NOT NULL,
	[AverageWriteLatencyMs] [INT] NOT NULL,
	[ReadMegabytes] [INT] NOT NULL,
	[WriteMegabytes] [INT] NOT NULL,
	[IOCount] [INT] NOT NULL,
	[DurationInSeconds] [INT] NOT NULL,
	[TestedDrive] [NVARCHAR](256) NOT NULL,
	[FileCount] [INT] NOT NULL
) ON [PRIMARY]
GO