Skip to content

Instantly share code, notes, and snippets.

@linosteenkamp
Last active August 29, 2021 16:11
Show Gist options
  • Save linosteenkamp/4dd59b966e4998d845e40ea8e94f3ec8 to your computer and use it in GitHub Desktop.
Save linosteenkamp/4dd59b966e4998d845e40ea8e94f3ec8 to your computer and use it in GitHub Desktop.
Execute speedtest-cli and store result in sqlite database
#!/bin/bash
# Path to speedtest-cli (Change this to match the path to your speedtest-cli executable)
SPEEDTEST_CLI_PATH="/usr/local/bin/speedtest-cli"
# Path to the sqlite database (Change this to suit your needs)
#DB_PATH="/home/lino/speedlog.db"
DB_PATH="/var/www/speedtest-api/speedtest.db"
# Create test table SQL statement
SQL_CREATE=" \
create table tests ( \
id integer PRIMARY KEY AUTOINCREMENT, \
server_id integer, \
sponsor text, \
server_name text, \
timestamp text, \
distance real, \
ping real, \
download real, \
upload real, \
share text, \
ip_address text \
);"
# Input Field Seperator for splitting the CSV result into an array
IFS=";"
# Create Sqlite database and table if it does not exist
if ! [ -f "$DB_PATH" ]
then
sqlite3 $DB_PATH "$SQL_CREATE"
fi
# Do Speed Test
result=$($SPEEDTEST_CLI_PATH --csv --csv-delimiter ";")
# Result to Array
arr=($result)
# Transform values
arr[6]=$(awk "BEGIN {print ${arr[6]}/1000000}")
arr[7]=$(awk "BEGIN {print ${arr[7]}/1000000}")
# Array to Sqlite database
sqlite3 $DB_PATH " \
insert into tests ( \
server_id, \
sponsor, \
server_name, \
timestamp, \
distance, \
ping, \
download, \
upload, \
share, \
ip_address \
) values ( \
${arr[0]}, \
'${arr[1]}', \
'${arr[2]}', \
'${arr[3]}', \
${arr[4]%.*}, \
${arr[5]%.*}, \
${arr[6]}, \
${arr[7]}, \
'${arr[8]}', \
'${arr[9]}' \
);"
@linosteenkamp
Copy link
Author

Some server names (server_name) might have commas. If you change IFS="," to IFS=";" and add --csv-delimiter ";" to the result= line it works.

Thank you @matt-manuel.

Gist updated with your suggestion.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment