☰

Speedtest Ookla CSV 输出中没有时间戳

tag-icon • tag-icon linux script csv
Speedtest Ookla CSV 输出中没有时间戳

因此，当运行 Ookla 的官方 Speedtest 客户端并输出到 CSV 文件时，我注意到输出没有时间戳字段。JSON 有，但我对 jq 不是特别熟悉，尝试将 JSON 输出转换为 CSV 是没有用的。

有没有办法获取输出并将其传输到前面带有时间戳的文件中？

这是以 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"}}

这是 CSV 的输出（带标题）

"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"

我所做的就是将 JSON 输出的添加的时间戳数据转换为 CSV 格式，以便进一步处理。

答案1

这行代码将输出你想要的内容：

<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>

使用您的示例 JSON，我提取了您想要的所有元素jq -r：

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

然后将结果通过管道传输到tr `\n' ','以制作逗号分隔的列表，然后tr将结果通过管道传输到sed '$s/,$/\n/'以删除尾随的逗号。

结果应如下所示：

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

答案2

如果您想直接运行 csv 文件，以下是我在进行一些自己的研究后所做的：

#!/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

以最适合您的格式存储日期。我还添加了引号和逗号以匹配生成的数据。您稍后会用到它。

我运行了 ookla 输出以grep删除标题行，然后用于sed插入我在行开头创建的时间戳。然后我将其附加到我的 csv 中。我将其作为路由器上的 cron 作业运行。希望这能有所帮助。

相关内容