0

So when running the official speedtest client from Ookla and outputting to a CSV file, I've noticed the output has no timestamp field. The JSON does, but I'm not particularly savvy with jq and trying to convert the JSON output to a CSV isn't useful.

Is there a way to take the output and pipe it to a file with a timestamp in the front?

This is the output given as a JSON

{"type":"result","timestamp":"2021-07-22T16:14:17Z","ping":{"jitter":0.035999999999999997,"latency":3.9399999999999999},"download":{"bandwidth":117078051,"bytes":884657048,"elapsed":7601},"upload":{"bandwidth":117029963,"bytes":467614102,"elapsed":4006},"packetLoss":0,"isp":"CenturyLink","interface":{"internalIp":"192.168.0.35","name":"eth0","macAddr":"E4:5F:01:2F:1D:39","isVpn":false,"externalIp":"71.214.44.165"},"server":{"id":10161,"name":"CenturyLink","location":"Orlando, FL","country":"United States","host":"orlando.speedtest.centurylink.net","port":8080,"ip":"205.171.98.14"},"result":{"id":"64657421-d008-4053-9832-2d1a9b01b649","url":"https://www.speedtest.net/result/c/64657421-d008-4053-9832-2d1a9b01b649"}}

and this is the output of the CSV (with headers)

"server name","server id","latency","jitter","packet loss","download","upload","download bytes","upload bytes","share url"
"The Villages - The Villages, FL","25753","33.338","0.302","0","117318528","112406990","1488776432","1053747984","https://www.speedtest.net/result/c/8bbb92b8-880d-4021-b5e5-c90206862d18"
"CenturyLink - Orlando, FL","10161","4.013","0.399","0","76816660","112435444","1158108878","473391675","https://www.speedtest.net/result/c/17508892-6fc7-4616-84bb-810d314c50af"
"CenturyLink - Orlando, FL","10161","3.533","0.407","0","115293486","97552291","1002647576","574510787","https://www.speedtest.net/result/c/9913a846-1fbf-4d69-a1e9-27430914d397"

All I'm trying to do is to get the added timestamp data that gets outputted by JSON into a CSV format so I can further process.

2 Answers 2

0

This one liner will output what you want:

<JSON> | jq -r '.timestamp, .server.name, .server.id, .pi ng.latency, .ping.jitter, .packetLoss, .download.bandwidth, .upload.bandwidth, .download.bytes, .upload.bytes, .result.url' | tr '\n' ',' | sed '$s/,$/\n/' | tee -a <CSV File>

Using your sample JSON, I pulled out all the elements you wanted with jq -r:

  • .timestamp
  • .server.name
  • .server.id
  • .ping.latency
  • .ping.jitter
  • .packetLoss
  • .download.bandwidth
  • .upload.bandwidth
  • .download.bytes
  • .upload.bytes
  • .result.url

Then piped the results into tr `\n' ',' to make a comma separated list, then piped tr's results into sed '$s/,$/\n/' to remove the trailing comma.

The result should look like this:

2021-07-22T16:14:17Z,CenturyLink,10161,3.94,0.036,0,117078051,117029963,884657048,467614102,https://www.speedtest.net/result/c/64657421-d008-4053-9832-2d1a9b01b649

0

If you're trying to run directly into the csv file, here is what I did, after doing some research of my own:

#!/bin/sh

now=\"$(date +"%Y-%m-%d %H:%M:%S")\",
ookla -c http://www.speedtest.net/api/embed/vz0azjarf5enop8a/config -f csv | grep -v download | sed "s/^/$now/" >> /mnt/merlin/speedtest.csv

Store the date in the format that suits you best. I also added quotes and a comma to match the generated data. You'll use this later.

I ran the ookla output through grep to remove the header row, then used sed to insert the timestamp I created at the beginning of the row. I then append this to my csv. I'm running this as a cron job on my router. Hope this helps.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .