FaVM6xDk

1#!/usr/bin/perl 
2
3#
4# speedtest.pl - Quick and dirty script to capture output of speedtest-cli and
5#                store the results in a MySQL database.
6#
7# Note that this script presumes a database named 'speedtest' and a table 
8# called 'results' exists, and that there is a user account with INSERT
9# permissions. The table should look like this:
10#
11# CREATE TABLE results (
12#  ping FLOAT NOT NULL,
13#  download FLOAT NOT NULL,
14#  upload FLOAT NOT NULL,
15#  date_time TIMESTAMP
16#);
17#
18# 2/23/2017
19#
20
21use warnings; 
22use strict;
23
24use DBI;
25use Data::Dumper;
26
27#------------------------------------------------------------------------------
28# Vars -- Change these as needed.
29#------------------------------------------------------------------------------
30# Change to where the script lives.
31my $speedtest_cmd = '/usr/bin/speedtest-cli --simple';
32
33# Holds the metrics we want, which are also our column names.
34my @metrics;
35
36# Holds the results of the test.
37my @results;
38
39# DB connection parameters.
40my $db_host   = '127.0.0.1';
41my $db_user   = 'speed_user';
42my $db_passwd = 'speed_user_password';
43
44# DB to insert into.
45my $db_name  = 'speedtest';
46my $db_table = 'results';
47 
48#------------------------------------------------------------------------------
49# Script -- No changes needed below here.
50#------------------------------------------------------------------------------
51
52# Sample output looks like this:
53#   Ping: 21.997 ms
54#   Download: 11.91 Mbits/s
55#   Upload: 4.92 Mbits/s
56foreach my $line (`$speedtest_cmd`) {
57  if ($line =~ /^(\w+):\s([\d\.]+)\s/) {
58    push(@metrics, lc($1));
59    push(@results, lc($2));
60  }
61}
62
63if (!@metrics || !@results) {
64  die "There was an error capturing output of the speedtest script.\n";
65}
66
67my $dbh = DBI->connect("DBI:mysql:$db_name", $db_user, $db_passwd, {RaiseError => 1});
68
69my $sql = "INSERT INTO $db_table (" . join(', ', @metrics) . ") VALUES (?, ?, ?)";
70my $sth = $dbh->prepare($sql);
71$sth->execute(@results);
72
73$sth->finish();
74$dbh->disconnect();