43 lines
1.5 KiB
Bash
Executable File
43 lines
1.5 KiB
Bash
Executable File
#!/bin/bash
|
|
|
|
# create the database file and table if it doesn't exist
|
|
if [ ! -f mydata.db ]; then
|
|
echo "Creating database file..."
|
|
sqlite3 mydata.db "CREATE TABLE mytable(timestamp TEXT, WAN_Connection TEXT, miles_from_server TEXT, download_speed REAL, upload_speed REAL, UNIQUE(timestamp, WAN_Connection));"
|
|
fi
|
|
|
|
# define an array of URLs that point to the csv file. One URL per line and within " "
|
|
# e.g. "http://192.168.69.101/speedtest-wan1.csv"
|
|
urls=(
|
|
""
|
|
)
|
|
|
|
# loop through each URL
|
|
for url in "${urls[@]}"
|
|
do
|
|
# download the file
|
|
filename=$(basename $url)
|
|
curl -s $url -o $filename
|
|
|
|
# loop through each line in the input file, starting from the second line
|
|
tail -n +2 $filename | while read line
|
|
do
|
|
# split the CSV data into columns
|
|
WAN_Connection=$(echo $filename | grep -oP '(?<=wan).*' | sed 's/\.csv//')
|
|
timestamp=$(echo $line | cut -d, -f1)
|
|
miles_from_server=$(echo $line | cut -d, -f2 | cut -d' ' -f1)
|
|
download_speed=$(echo $line | cut -d, -f3 | cut -d' ' -f1)
|
|
upload_speed=$(echo $line | cut -d, -f4 | cut -d' ' -f1)
|
|
|
|
# check if the data is already in the database
|
|
if ! sqlite3 mydata.db "SELECT * FROM mytable WHERE timestamp='$timestamp' AND WAN_Connection='$WAN_Connection';" | grep -q "$timestamp"; then
|
|
# add the data to the database
|
|
sqlite3 mydata.db "INSERT INTO mytable(timestamp, WAN_Connection, miles_from_server, download_speed, upload_speed) VALUES ('$timestamp', '$WAN_Connection', $miles_from_server, $download_speed, $upload_speed);"
|
|
fi
|
|
done
|
|
|
|
# remove the file
|
|
rm $filename
|
|
done
|
|
|