JSON Try

Categories
Page content

Learning Json

Takeing some failed SSH Password Attemps on a Honeypot and learn so Json :)

RAW Data

cat <<EOF > data.json
{"_etime":"2021-03-31T15:10:03+02:00","_host":"139.217.129.158:46882","_proto":"ssh","_server":"[::]:222","_type":"credential","method":"password","password":"external","username":"external","version":"SSH-2.0-libssh-0.6.3"}
{"_etime":"2021-03-31T15:17:14+02:00","_host":"152.136.11.211:48528","_proto":"ssh","_server":"[::]:222","_type":"credential","method":"password","password":"support","username":"support","version":"SSH-2.0-libssh-0.6.3"}
{"_etime":"2021-03-31T15:17:51+02:00","_host":"139.217.129.158:54804","_proto":"ssh","_server":"[::]:222","_type":"credential","method":"password","password":"ambilogger","username":"ambilogger","version":"SSH-2.0-libssh-0.6.3"}
{"_etime":"2021-03-31T15:18:07+02:00","_host":"68.183.84.215:40716","_proto":"ssh","_server":"[::]:222","_type":"credential","method":"password","password":"passwd1234","username":"root","version":"SSH-2.0-libssh-0.6.3"}
{"_etime":"2021-03-31T15:18:16+02:00","_host":"104.131.231.109:43296","_proto":"ssh","_server":"[::]:222","_type":"credential","method":"password","password":"guest","username":"Guest","version":"SSH-2.0-libssh-0.6.3"}
{"_etime":"2021-03-31T15:18:32+02:00","_host":"91.217.83.61:45994","_proto":"ssh","_server":"[::]:222","_type":"credential","method":"password","password":"qwertyu","username":"test","version":"SSH-2.0-libssh-0.6.3"}
{"_etime":"2021-03-31T15:18:36+02:00","_host":"181.143.81.52:37964","_proto":"ssh","_server":"[::]:222","_type":"credential","method":"password","password":"sh123456","username":"root","version":"SSH-2.0-libssh-0.6.3"}
{"_etime":"2021-03-31T15:18:44+02:00","_host":"52.178.155.67:1024","_proto":"ssh","_server":"[::]:222","_type":"credential","method":"password","password":"Root12345!","username":"root","version":"SSH-2.0-libssh-0.6.3"}
{"_etime":"2021-03-31T15:18:55+02:00","_host":"103.94.6.69:48871","_proto":"ssh","_server":"[::]:222","_type":"credential","method":"password","password":"wq","username":"wq","version":"SSH-2.0-libssh-0.6.3"}
{"_etime":"2021-03-31T15:19:24+02:00","_host":"177.67.203.135:27752","_proto":"ssh","_server":"[::]:222","_type":"credential","method":"password","password":"daemon","username":"daemon","version":"SSH-2.0-libssh-0.6.3"}
EOF

Questions

I take a bigger File with 1000 Entries

What SSH Agents are in use ? How many ?

$ cat data1000.json |jq -r .version |sort |uniq -c |sort -rn |head -10
 445 SSH-2.0-libssh-0.6.3
  43 SSH-2.0-libssh2_1.4.3
  30 SSH-2.0-PUTTY
  22 SSH-2.0-libssh2_1.8.0
  20 SSH-2.0-PuTTY_Release_0.63
  20 SSH-2.0-OpenSSH_5.3
  17 SSH-2.0-paramiko_1.8.1
  14 SSH-2.0-Go
  13 SSH-2.0-libssh_0.5.5
  13 SSH-2.0-WinSCP_release_5.7.6

Single Element

$ cat test.json |jq '.attack[0]'
{
  "_etime": "2021-05-08T08:34:40+02:00",
  "_host": "217.23.12.117:21247",
  "_proto": "ssh",
  "_server": "[::]:222",
  "_type": "credential",
  "method": "password",
  "password": "123456",
  "username": "admin",
  "version": "SSH-2.0-paramiko_1.12.4"
}

Values

$ cat test.json |jq '.attack[0] | values[]'
"2021-05-08T08:34:40+02:00"
"217.23.12.117:21247"
"ssh"
"[::]:222"
"credential"
"password"
"123456"
"admin"
"SSH-2.0-paramiko_1.12.4"

Data Types

$ cat test.json |jq '.attack[0] | values[] | type'
"string"
"string"
"string"
"string"
"string"
"string"
"string"
"string"
"string"

Reduce Dataset

We don’t wan’t all fields, so let’s make the Datasource smallrt

$ cat data1.json |jq
{
  "_etime": "2021-05-25T17:53:32+02:00",
  "_host": "218.92.0.191:10940",
  "_proto": "ssh",
  "_server": "[::]:222",
  "_type": "credential",
  "method": "password",
  "password": "qwer1234",
  "username": "root",
  "version": "SSH-2.0-PUTTY"
}

$ cat data1.json |jq '{_etime,_host,_type,username,password,version}'
{
  "_etime": "2021-05-25T17:53:32+02:00",
  "_host": "218.92.0.191:10940",
  "_type": "credential",
  "username": "root",
  "password": "qwer1234",
  "version": "SSH-2.0-PUTTY"

Remove Port and Reduce Data

Extract IP, Sort and Count SRC IP

$ cat data.json |jq -r '.attack[]._host |= sub(":[0-9]{1,5}$";"") |.attack[]._host' |sort |uniq -c |sort -rn |head -20 
 103 217.23.12.117
  44 51.77.66.36
  34 218.92.0.191
  15 119.29.113.253
  13 217.23.1.87
  13 167.172.221.151
  12 101.35.128.250
  11 47.243.91.41
  11 35.198.57.112
   9 89.39.104.123
   8 36.103.222.99
   7 47.243.176.69
   7 47.112.125.43
   7 206.189.29.253
   7 180.142.130.246
   6 8.141.65.225
   6 49.88.112.118
   6 47.243.178.142
   6 45.141.84.10
   5 61.177.173.12

JQ was not able to the Bigger amount of data. Had to boot a VM with 8GB RAM and try again ..

Top 20 Attacking IP

cat data.json |jq -r '.attack[]._host'|sed 's/:.*//' |sort |uniq -c |sort -rn |head -20
251361 217.23.12.117
89005 51.77.66.36
63188 218.92.0.191
30357 89.39.104.123
29866 119.29.113.253
28747 217.23.1.87
23102 101.35.128.250
20772 167.172.221.151
18646 36.103.222.99
17845 47.243.91.41
17355 35.198.57.112
16455 206.189.29.253
14047 47.243.176.69
13247 221.153.164.69
13105 45.141.84.10
12345 121.196.167.225
11919 180.142.130.246
10296 47.243.238.50
10158 47.112.125.43
10081 47.243.178.142

Attacks from IP: 217.23.12.117

$ cat data_x.json |jq -r '.attack[] | select(._host |contains("217.23.12.117")) |._etime' |sed 's/T/ /' |cut -c 1-10 |sort |uniq -c
3422 2021-04-30
55510 2021-05-03
83598 2021-05-07
108831 2021-05-08

Passing IP as Variable to Json

jq –arg ip 1.2.3.4 -> $ip can be used in jq query

$ cat data_x.json |jq -r --arg ip 35.198.57.112 '.attack[] | select(._host |contains($ip)) |._etime' |sed 's/T/ /' |cut -d"+" -f 1 |head -5
2021-08-26 02:38:50
2021-08-25 16:53:15
2021-08-26 15:19:29
2021-08-26 06:09:50
2021-08-25 16:35:58

Top 20 Attacking Subnet (/24)

$ cat data_x.json |jq -r '.attack[]._host'|sed -E 's/:.*//;s/\.[0-9]{1,3}//;s/$/.0\/24/' |sort |uniq -c |sort -rn |head -20
251361 217.23.12.0/24
89005 51.77.66.0/24
68465 218.92.0.0/24
30357 89.39.104.0/24
30098 119.29.113.0/24
28747 217.23.1.0/24
23102 101.35.128.0/24
20772 167.172.221.0/24
19749 45.141.84.0/24
18665 36.103.222.0/24
18280 49.88.112.0/24
17845 47.243.91.0/24
17355 35.198.57.0/24
16455 206.189.29.0/24
14047 47.243.176.0/24
13247 221.153.164.0/24
12345 121.196.167.0/24
11919 180.142.130.0/24
11372 61.177.173.0/24
10296 47.243.238.0/24

Top 20 Attacking Subnet with AS Number

The ususal Suspects … CN, RU, KR, US, FR, NL, …

217.23.12.1      AS49981 | NL | WORLDSTREAM
51.77.66.1       AS16276 | FR | OVH
218.92.0.1       AS4134 | CN | CHINANET-BACKBONE No.31,Jin-rong Street
89.39.104.1      AS49981 | NL | WORLDSTREAM
119.29.113.1     AS45090 | CN | CNNIC-TENCENT-NET-AP Shenzhen Tencent Computer Systems Company Limited
217.23.1.1       AS49981 | NL | WORLDSTREAM
101.35.128.1     AS45090 | CN | CNNIC-TENCENT-NET-AP Shenzhen Tencent Computer Systems Company Limited
167.172.221.1    AS14061 | US | DIGITALOCEAN-ASN
45.141.84.1      AS206728 | RU | MEDIALAND-AS
36.103.222.1     AS134761 | CN | CHINANET-NINGXIA-ZHONGWEI-IDC CHINANET NINGXIA province ZHONGWEI IDC network
49.88.112.1      AS4134 | CN | CHINANET-BACKBONE No.31,Jin-rong Street
47.243.91.1      AS45102 | CN | CNNIC-ALIBABA-US-NET-AP Alibaba US Technology Co., Ltd.
35.198.57.1      AS15169 | US | GOOGLE
206.189.29.1     AS14061 | US | DIGITALOCEAN-ASN
47.243.176.1     AS45102 | CN | CNNIC-ALIBABA-US-NET-AP Alibaba US Technology Co., Ltd.
221.153.164.1    AS4766 | KR | KIXS-AS-KR Korea Telecom
61.177.173.1     AS4134 | CN | CHINANET-BACKBONE No.31,Jin-rong Street
47.243.238.1     AS45102 | CN | CNNIC-ALIBABA-US-NET-AP Alibaba US Technology Co., Ltd.

JSON to CSV

# convert to csv
$ cat data_s.json |jq -r '.attack[:100] | (map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.])) as $rows | $cols, $rows[] | @csv' > data.csv 

# Import
$ sqlitebiter file data.csv

# Select
$ sqlite3 out.sqlite -header "select * from data limit 5" |csvlook 
|                    _etime | _host                 | _proto | _server  | _type      | method   | password   | username | version                      |
| ------------------------- | --------------------- | ------ | -------- | ---------- | -------- | ---------- | -------- | ---------------------------- |
| 2021-08-17 15:27:11+02:00 | 119.29.113.253:50320  | ssh    | [::]:222 | credential | password | katherine1 | root     | SSH-2.0-Go                   |
| 2021-04-14 04:43:07+02:00 | 119.252.143.6:40166   | ssh    | [::]:222 | credential | password | admin      | eric     | SSH-2.0-libssh-0.6.3         |
| 2021-11-08 10:54:27+01:00 | 167.172.221.151:52190 | ssh    | [::]:222 | credential | password | mahesh     | root     | SSH-2.0-Go                   |
| 2021-05-05 19:43:25+02:00 | 190.2.144.45:29303    | ssh    | [::]:222 | credential | password | 123456     | admin    | SSH-2.0-WinSCP_release_4.1.9 |
| 2021-04-26 11:18:35+02:00 | 170.210.176.254:60642 | ssh    | [::]:222 | credential | password | pass@123   | admin    | SSH-2.0-libssh-0.6.3         |

sha256: dac3778473d6e7b52a725cca38d0d54a008776a29c10438a6639d7367fcd86ef