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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.