Speedtests in SQL-Datenbank Loggen

Aus gegebenem Anlass – siehe Grafik – wollte ich automatisch regelmäßige Speedtests durchführen und die Ergebnisse zentral sammeln. Das Ganze mit einer CSV-Datei und Excel-Import zu lösen, erschien mir dann jedoch zu ordinär, der Server dient schließlich nicht (nur) als Heizung.

Die Geschwindigkeitsmessung selbst erfolgt durch die Anwendung Speedtest CLI von Ookla, mit dieser können Speedtests über die Kommandozeile oder eben Scripts ausgeführt werden. Das Tool muss vor Ausführung des Scripts heruntergeladen und entpackt werden.

Zudem verwende ich einen Microsoft SQL Server 2019, welcher die Datenbank für das Logging bereitstellen soll. Zunächst sollte man sich Gedanken darüber machen, welche Werte gespeichert werden sollen. Ich habe mich daraufhin für folgendes Tabellendesign entschieden:

Und an der Stelle kurz ein Disclaimer: Ich bin ein blutiger Anfänger im Umgang mit Datenbanken. Falls jemand die Hände über dem Kopf zusammenschlagen sollte, freue ich mich gerne über Optimierungsvorschläge 🙂

Den Constraint habe ich gewählt, da zu einem bestimmten Zeitpunkt (Timestamp) mehrere Ergebnisse geloggt werden könnten, jedoch nie mit dem gleichen Speedtest-Server (ServerID). Insofern erschien mir eine Kombination dieser Schlüssel sinnvoll für die Wahl als Primärschlüssel. Die Datenbank habe ich also mit folgenden Queries konfiguriert:

CREATE DATABASE Speedtest;
USE Speedtest;
CREATE TABLE Results
(
	[Timestamp] DATETIME NOT NULL,
	[ServerID] INT NOT NULL,
	[ServerName] VARCHAR(80) NOT NULL,
	[Latency] REAL NOT NULL,
	[Jitter] REAL NOT NULL,
	[PacketLoss] REAL NOT NULL,
	[DownloadMBits] REAL NOT NULL,
	[UploadMBits] REAL NOT NULL,
	[DownloadBytes] INT NOT NULL,
	[UploadBytes] INT NOT NULL,
	[ShareURL] CHAR(71) NOT NULL,
	CONSTRAINT PK_Result PRIMARY KEY ([Timestamp],[ServerID])
);

Nun muss die Datenbank natürlich befüllt werden. Da ich derzeit PowerShell-Wissen aufbauen möchte, wurde diese das Werkzeug meiner Wahl. Das Modul SqlServer schafft hier die Möglichkeiten zum Hantieren in SQL-Datenbanken, muss jedoch erst einmal installiert werden:

Install-Module -Name SqlServer

Der Config-Bereich kann nun individuell angepasst werden. Zunächst wird im SpeedtestPath der Speicherort der zuvor heruntergeladenen Speedtest-CLI-Anwendung definiert. Nun müssten die SQL-Server-Verbindungsdaten angegeben werden, vorausgesetzt ist hier die aktive SQL-Server Authentifizierung.

Nach dem Importieren des SQL-Server-Moduls geht es auch schon zur Sache. Die Variable „SpeedtestResults“ wird definiert und mit einem Speedtest-Ergebnis gefüllt. Als Format wird die JavaScript Object Notation (JSON) gewählt, da die Ergebnisse einfacher verarbeitet werden können. Mit dem Parameter --server-id=xxxxx kann bei Bedarf ein bestimmter Speedtest-Server ausgewählt werden.

######################################################
#                                                    #
#                  Speedtest -> SQL                  #
#                                                    #
######################################################

####################### Config ####################### 

$SpeedtestPath = "D:\Speedtest"
$SQLInstance = "HostnameSQLServer"
$SQLUser = "sqluser"
$SQLPassword = "password1234"
$SQLDatabase = "Speedtest"

####################### Script #######################

Import-Module SqlServer

$SpeedtestResults = & "$($SpeedtestPath)\speedtest.exe" --format=json --accept-license --accept-gdpr #--server-id=33397

# $SpeedtestResults | Out-File "$($SpeedtestPath)\Speedtestlog.json" -Append
$SpeedtestResults = $SpeedtestResults | ConvertFrom-Json
$Timestamp = (Get-Date -Format "yyyy-dd-MM HH:mm:ss")

$SQLQuery = "USE $($SQLDatabase);
INSERT INTO Results (
	[Timestamp],
	[ServerID],
	[ServerName],
	[Latency],
	[Jitter],
	[PacketLoss],
	[DownloadMBits],
	[UploadMBits],
	[DownloadBytes],
	[UploadBytes],
	[ShareURL]
	) 
VALUES (
	'$($Timestamp)',
	'$($SpeedtestResults.server.id)',
	'$($SpeedtestResults.server.name)',
	'$($SpeedtestResults.ping.latency)',
	'$($SpeedtestResults.ping.jitter)',
	'$($SpeedtestResults.packetloss)',
	'$([math]::Round($SpeedtestResults.download.bandwidth / 1000000 * 8, 2))',
	'$([math]::Round($SpeedtestResults.upload.bandwidth / 1000000 * 8, 2))',
	'$($SpeedtestResults.download.bytes)',
	'$($SpeedtestResults.upload.bytes)',
	'$($SpeedtestResults.result.url)'
	)
;"

Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $SQLInstance -Username $SQLUser -Password $SQLPassword

Falls das parallele Loggen in eine Datei doch gewünscht ist, kann Zeile 21 einkommentiert werden und man erhält eine stetig wachsende JSON-Datei.

Um dem JSON-Output nutzbar zu machen, wird die Funktion ConvertFrom-Json genutzt.

Das Herzstück – die SQL Query – fügt nun die zuvor gewonnenen Ergebnisse in die jeweiligen Tabellenspalten ein, zudem werden die Werte für die Bandbreiten in MBit/s konvertiert und die Nachkommastellen begrenzt.

Zu guter Letzt wird das Ganze nun mittels Invoke-Sqlcmd ausgeführt.

Insgesamt also ein sehr überschaubares Skript, was jedoch seinen Zweck erfüllt. Um es nun periodisch und automatisch auszuführen, habe ich die gute alte Windows-Aufgabenplanung bemüht:

• 

• 

• 

Und während ich diese Zeilen zusammentippe, werden auch schon erfolgreich die ersten Ergebnisse protokolliert:

Die Daten können nun über eine beliebige Lösung ausgewertet werden. Als erste Übung kann beispielsweise der Tagesverlauf in einem hübschen Excel-Diagramm dargestellt werden.

Vielen Dank für’s Lesen!

Changelog:
09.01.2021: Dem Get-Date-Befehl wird nun auch das gewünschte Format mitgegeben, da es auf deutschen SQL-Servern sonst zu falsch interpretierten Datumsangaben kam (Tag/Monat vertauscht).
07.01.2021: Der Timestamp wird nun per Get-Date vom Hostsystem abgefragt, um die korrekte Zeitzone zu nutzen.