Speedtest

Background

Recently, we had random short-lived outages in our ATT DSL. In the end, a tech came out, probed the jack and left to fix a short 500m up the road. 

But- while I was waiting on the tech that morning, I started poking around on the internet and found out that Ookla has a downloadable speedtest.exe that can be run from the command line (or from PowerShell). So, sure.

This is just the bare basics. At some point, I will (probably)/may come back and flesh this out some by adding more detail and some explanation. Right now I had to install the Code Filter module to display the snippet below (see: https://www.drupal.org/project/codefilter) (*while I was at it, I updated to Drupal 11.04 because I was here)

Speedtest

Here's a good GitHub for this: https://github.com/asheroto/speedtest but rather than using asheroto.com/speedtest, I just downloaded the Windows package at https://www.speedtest.net/apps/desktop

There's a better GitHub here: <I have since lost this link, sorry>

BUT - there's a bit of oddness. Each time the speedtest is run, it generates a set of values stored on the Ookla server and part of the results include a link to that test in a nicely formatted page. HOWEVER, the measurements are off:

• On the first row of the csv that I ended up creating the download is 522637, while the value on their page is 4.18 Mbps
• According to the available documentation, the default unit is Mbps for displaying speeds (using the --help flag)
• So, they should be the same number, but aren't. Thanks to Google, the data transfer rate conversion from 522637 is Bytes per Second, which is equal to 4.181096 Megabit per Second (the formula being 'divide the data transfer rate value by 125000')

PowerShell

And because it'd been so long since I'd done anything in PowerShell, I had to crack the docs: https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/invoke-expression?view=powershell-7.4

And also I had to update PowerShell on my home computer, and Visual Studio Code and download the PS extension for VS Code. So after all that, it was time to figure out how to code a command-line test. This is what I ended up with (I didn't clean up the code, so it's *really* dirty and full of checks that are commented out).

(Anything following a octothorpe (a pound sign) is a comment and is ignored)

(Also, I'd like to find out a way to copy and paste code snips with line numbers - something I'll have to look up later)

#create speedtest.csv if none exist, run speedtest and pipe to csv 
$myPath = "c:\speedtest\speedtest.csv" 
#check exists - if not then run speedtest and ask for headers 
if (-not ($myPath | Test-Path)) {
    $p = Invoke-Command -ScriptBlock {.\speedtest.exe --server-id=20013 --progress=no --format=csv --output-header }
    if ($p) {
        $q = New-Object -TypeName psobject -Property @{
            Array = $p        }
        #$q | Add-Member -MemberType NoteProperty -Name "Timestamp" -Value (Get-Date -UFormat "%m/%d/%Y %R")
        #$q = $q | ConvertTo-Json -AsArray

        #$p | Out-File -Path $myPath
        #$p.GetType()
        #$q.GetType()
        #$q.Array | Get-Member
        #$q.Array.Count
        $q.Array[0] = $q.Array[0] + ",`"Timestamp`""
        $tstamp = Get-Date -UFormat "%m/%d/%Y %R"
        $q.Array[1] = $q.Array[1] + ",`"" + $tstamp + "`""
        $q.Array[0] | Out-File $myPath
        $q.Array[1] | Out-File $myPath -Append
    } else {
        Write-Host "Speedtest failed"
    }
 } else {
    $p = Invoke-Command -ScriptBlock {.\speedtest.exe --server-id=20013 --progress=no --format=csv }
 #    $q = New-Object -TypeName psobject -Property @{
 #        Array = $p #    }    
 #    $p | Add-Member -MemberType NoteProperty -Name "Timestamp" -Value (Get-Date -UFormat "%m/%d/%Y %R")
    if ($p) {
        $q = $p.ToString()
        $tstamp = Get-Date -UFormat "%m/%d/%Y %R"
        $q = $q + ",`"" + $tstamp + "`""
        $q | Out-File -Path $myPath -Append
    } else {
        Write-Host "Speedtest failed"
    }
 }

Power BI for some reason

And since I wrote out the values to a csv, why not grab that and make a line chart? 

Really the only thing I had to do to the file in the Transform/Power Query Editor step was to split the Timestamp field into two separate fields, one for the date and one for the time - Power BI will take both and give you a date/time hierarchy to add you your chart.

This is the right side of my CSV. The first run with the PowerShell script, creates a file and runs speedtest with the header option so that the CSV is nicely labeled by the process.

[Right side of original csv]

When the data source is added to PowerBI, you can transform the data to do things like dropping columns, adjusting the data types in each column, etc. What I did to split the Timestamp column into ScanDate and ScanTime columns is use the Add Column > Date > Parse and Time > Parse (in the From Date & Time in the Ribbon) to add the two columns at the end. 

[Transform Window]

With the Date and Time split, I could do a time series in a line chart with two y-values (the second one, idle jitter, was just for fun) - Select a line chart in Visualizations pane, drag ScanDate into the X-axis box, then drag ScanTime into the X-axis box below the ScanDate, then drag Sum of download and Sum of Upload into the Y-axis box, and you get a nice line chart for some pretty poor Internet speeds.

[Resulting Line Chart]