Jq

2020-12-14 (Last Modified: 2022-08-14)

Page content

Json Query

some basics about JQ

RAW Data

cat history.shelly.0.SHEM-3#40F52000B661#1.Total.Current.json | jq '.[0:3]'
[
  {
    "val": 2.64,
    "ack": 1,
    "ts": 1607900404883,
    "q": 0,
    "user": "system.user.admin"
  },
  {
    "val": 2.61,
    "ack": 1,
    "ts": 1607900410483,
    "q": 0,
    "user": "system.user.admin"
  },
  {
    "val": 2.58,
    "ack": 1,
    "ts": 1607900416083,
    "q": 0,
    "user": "system.user.admin"
  }
]

Query First Record

cat history.shelly.0.SHEM-3#40F52000B661#1.Total.Current.json | jq '.[0]'
{
  "val": 2.64,
  "ack": 1,
  "ts": 1607900404883,
  "q": 0,
  "user": "system.user.admin"
}

Filter val and ts

cat history.shelly.0.SHEM-3#40F52000B661#1.Total.Current.json | jq '.[0] |.ts,.val'
1607900404883
2.64

Filter val and ts on one line

cat history.shelly.0.SHEM-3#40F52000B661#1.Total.Current.json | jq '.[0] | (.ts |tostring) + ";" + (.val |tostring)'
"1607900404883;2.64"

Filter first 10 val and ts on one line

cat history.shelly.0.SHEM-3#40F52000B661#1.Total.Current.json | jq '.[] | (.ts |tostring) + ";" + (.val |tostring)' |head -10
"1607900404883;2.64"
"1607900410483;2.61"
"1607900416083;2.58"
"1607900421739;2.62"
"1607900427335;2.62"
"1607900433003;2.57"
"1607900438543;2.72"
"1607900444131;2.67"
"1607900449791;2.6"
"1607900455383;2.55"

Filter first 10 val and ts on one line

cat history.shelly.0.SHEM-3#40F52000B661#1.Total.Current.json | jq '.[] |.ts,.val' |paste - - |head -10
1607900404883	2.64
1607900410483	2.61
1607900416083	2.58
1607900421739	2.62
1607900427335	2.62
1607900433003	2.57
1607900438543	2.72
1607900444131	2.67
1607900449791	2.6
1607900455383	2.55

Current over 50A

cat history.shelly.0.SHEM-3#40F52000B661#1.Total.Current.json | jq -c '.[] | select (.val >= '50')'
{"val":52.55,"ack":1,"ts":1607907152399,"q":0,"user":"system.user.admin"}
{"val":52.54,"ack":1,"ts":1607907157975,"q":0,"user":"system.user.admin"}
{"val":52.53,"ack":1,"ts":1607907163639,"q":0,"user":"system.user.admin"}
{"val":52.5,"ack":1,"ts":1607907169220,"q":0,"user":"system.user.admin"}
{"val":52.49,"ack":1,"ts":1607907174863,"q":0,"user":"system.user.admin"}
{"val":52.53,"ack":1,"ts":1607907180639,"q":0,"user":"system.user.admin"}
...

BGP Stuff

dump networks from AS 3303

user@host$ doas bgpctl -j show ip bgp source-as 3303 inet |jq -r '.[] |.[].prefix' |head
45.10.168.0/22
45.85.96.0/24
45.85.98.0/24
46.14.0.0/16
46.245.144.0/21
62.202.0.0/15
78.110.128.0/20
80.75.192.0/20
80.94.144.0/20
81.62.0.0/15

BGP All CH Stuff

dump all network from CH Providers

$ o=ipv4_ch.txt; >$o; as="559 3303 6730 6830 8758 13030 15600 15796 21040 24940 28875"; for i in $as; do doas bgpctl -j show ip bgp source-as $i inet |jq -r '.[] |.[].prefix' >> $o; done

$ wc -l $o
     982 ipv4_ch.txt

$ head $o
82.130.64.0/18
86.119.0.0/16
89.206.64.0/18
128.178.0.0/15
129.129.0.0/16
129.132.0.0/16
129.194.0.0/15
130.59.0.0/16
130.60.0.0/16
130.82.0.0/16

DNS with Dog & JQ

$ dog dns.google -J --time |jq -c -r '.responses[].answers[] |.type +","+ .name +","+ .address'
A,dns.google.,8.8.8.8
A,dns.google.,8.8.4.4

DualStacked

$ dog dns.google -t A -t AAAA -J --time |jq -c -r '.responses[].answers[] |.type +","+ .name +","+ .address'
A,dns.google.,8.8.8.8
A,dns.google.,8.8.4.4
AAAA,dns.google.,2001:4860:4860::8888
AAAA,dns.google.,2001:4860:4860::8844

Adding Time

$ dog dns.google -t A -t AAAA -J --time |jq -c -r '(.duration.nanos|tostring) +","+ .responses[].answers[].type +","+ .responses[].answers[].name +","+ .responses[].answers[].address'
2995848,A,dns.google.,8.8.8.8
2995848,A,dns.google.,8.8.8.8
2995848,AAAA,dns.google.,8.8.8.8
2995848,AAAA,dns.google.,8.8.8.8

Marketstuff

get some Finance Data …

cat <<EOF > bla
{
  "pagination": {
    "limit": 1000,
    "offset": 0,
    "count": 1000,
    "total": 2517
  },
  "data": [
    {
      "open": 157.65,
      "high": 161.02,
      "low": 156.5328,
      "close": 160.55,
      "volume": 117305597,
      "adj_high": null,
      "adj_low": null,
      "adj_close": 160.55,
      "adj_open": null,
      "adj_volume": null,
      "split_factor": 1,
      "dividend": 0,
      "symbol": "AAPL",
      "exchange": "XNAS",
      "date": "2021-11-19T00:00:00+0000"
    },
    {
      "open": 153.71,
      "high": 158.67,
      "low": 153.05,
      "close": 157.87,
      "volume": 137659100,
      "adj_high": null,
      "adj_low": null,
      "adj_close": 157.87,
      "adj_open": null,
      "adj_volume": null,
      "split_factor": 1,
      "dividend": 0,
      "symbol": "AAPL",
      "exchange": "XNAS",
      "date": "2021-11-18T00:00:00+0000"
    },
    {
      "open": 150.995,
      "high": 155,
      "low": 150.995,
      "close": 153.49,
      "volume": 88807000,
      "adj_high": null,
      "adj_low": null,
      "adj_close": 153.49,
      "adj_open": null,
      "adj_volume": null,
      "split_factor": 1,
      "dividend": 0,
      "symbol": "AAPL",
      "exchange": "XNAS",
      "date": "2021-11-17T00:00:00+0000"
    }
  ]
}
EOF

Filter Stuff

$ cat bla |jq -j '.data[0:5] | .[] |(.symbol|tostring)+" "+(.open|tostring)+" "+(.close|tostring)+" "+(.date|tostring)+"\n"'
AAPL 157.65 160.55 2021-11-19T00:00:00+0000
AAPL 153.71 157.87 2021-11-18T00:00:00+0000
AAPL 150.995 153.49 2021-11-17T00:00:00+0000
AAPL 149.94 151 2021-11-16T00:00:00+0000
AAPL 150.37 150 2021-11-15T00:00:00+0000

$ cat bla |jq -j '.data[0:5] | .[] |(.symbol|tostring)+" "+(.open|tostring)+" "+(.close|tostring)+" "+(.volume|tostring)+" "+(.date|tostring)+"\n"'
AAPL 157.65 160.55 117305597 2021-11-19T00:00:00+0000
AAPL 153.71 157.87 137659100 2021-11-18T00:00:00+0000
AAPL 150.995 153.49 88807000 2021-11-17T00:00:00+0000
AAPL 149.94 151 59047800 2021-11-16T00:00:00+0000
AAPL 150.37 150 59077700 2021-11-15T00:00:00+0000

OpenBSD Inventory

if you have all your boxes inventory in json format, you can do nice things like this:

show me all boxes with php8.x installed. need version, total memory and fqdn

jq -r '.|select(.software.php |contains("8")) |
  .software.php+" "+
  .memory.total+" "+
  .system.fqdn' \
  *.json |sort -n

result

8.0 1.9GB host1.whatever
8.0 1.9GB host2.whatever
8.0 4.0GB host3.whatever
8.0 4.0GB host4.whatever
8.1 1.0GB host5.whatever
8.1 3.9GB host6.whatever

add cpu count, speed and memory free, order by free memory ascend

jq -r '.|select(.software.php |contains("8")) |
.software.php+" "+
.cpu.count+" "+
.cpu.speed+" "+
.memory.total+" "+
.memory.free+" "+
.system.fqdn' \
*.json |sort +4n

result

8.1 1 2295MHz 1.0GB 0.3GB host5.whatever
8.0 2 2495MHz 1.9GB 1.2GB host2.whatever
8.0 2 2495MHz 1.9GB 1.3GB host3.whatever
8.0 2 2304MHz 4.0GB 2.3GB host1.whatever
8.1 2 2100MHz 3.9GB 2.3GB host6.whatever
8.0 4 1000MHz 4.0GB 3.0GB host4.whatever

Any Comments ?

sha256: 52b9ba21985b0a1ac6ae1a83fef55f687f99a1a8bbe90079b11e2a43e713b107