Quick look at the data from Cowrie
After running the Cowrie honeypot for some days we have some data to start looking into. 
If you are only interesting in the stats, check it out here.
Dig into SQL and data
The Data (and the mistake)
So before we start to analyze the data, I have a confession to make.. When I was changing some values and config around I accidentally messed up the userdb.txt file, which Cowrie uses to authenticate users. I made it so that there was no password required, meaning everyone was welcomed to login.. here at the TheByteDungeon we don’t discriminate!
Massage the data
I have tested to login a few times, so we need to exclude data generated by my own testing.
[EXPAND] Let's clean up the test sessions
We start by creating a temporary tables where we will store all sessions id:
-- Create a temporary table to store common session IDs
CREATE TEMPORARY TABLE common_session_ids (
session_id VARCHAR(100)
);
Then we insert all the sessions into the new table called common_session_ids.
-- insert id into new table
INSERT INTO common_session_ids (session_id)
SELECT s.id
FROM sessions s
WHERE ip in ("127.0.0.1", "192.168.0.143", "192.168.0.187");
Now we DELETE the sessions from all available tables.
-- Delete from each table using the temporary table
DELETE FROM sessions WHERE id IN (SELECT session_id FROM common_session_ids);
DELETE FROM ttylog WHERE session IN (SELECT session_id FROM common_session_ids);
DELETE FROM downloads WHERE session IN (SELECT session_id FROM common_session_ids);
DELETE FROM auth WHERE session IN (SELECT session_id FROM common_session_ids);
-- DELETE FROM clients WHERE id IN (SELECT session_id FROM common_session_ids);
DELETE FROM ipforwards WHERE session IN (SELECT session_id FROM common_session_ids);
DELETE FROM input WHERE session IN (SELECT session_id FROM common_session_ids);
-- DELETE FROM sensors WHERE id IN (SELECT session_id FROM common_session_ids);
DELETE FROM params WHERE session IN (SELECT session_id FROM common_session_ids);
DELETE FROM keyfingerprints WHERE session IN (SELECT session_id FROM common_session_ids);
DELETE FROM ipforwardsdata WHERE session IN (SELECT session_id FROM common_session_ids);
-- Drop the temporary table
DROP TABLE common_session_ids;
The Tables
We’ve got 11 tables with data to play with:
show tables;
+------------------+
| Tables_in_cowrie |
+------------------+
| auth |
| clients |
| downloads |
| input |
| ipforwards |
| ipforwardsdata |
| keyfingerprints |
| params |
| sensors |
| sessions |
| ttylog |
+------------------+
[EXPAND] Describe tables:
DESCRIBE auth;
DESCRIBE clients;
DESCRIBE downloads;
DESCRIBE input;
DESCRIBE ipforwards;
DESCRIBE ipforwardsdata;
DESCRIBE keyfingerprints;
DESCRIBE params;
DESCRIBE sensors;
DESCRIBE sessions;
DESCRIBE ttylog;
MariaDB [cowrie]> DESCRIBE auth;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| session | char(32) | NO | | NULL | |
| success | tinyint(1) | NO | | NULL | |
| username | varchar(100) | NO | | NULL | |
| password | varchar(100) | NO | | NULL | |
| timestamp | datetime | NO | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
6 rows in set (0.001 sec)
MariaDB [cowrie]> DESCRIBE clients;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| version | varchar(50) | NO | | NULL | |
+---------+-------------+------+-----+---------+----------------+
2 rows in set (0.001 sec)
MariaDB [cowrie]> DESCRIBE downloads;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| session | char(32) | NO | MUL | NULL | |
| timestamp | datetime | NO | | NULL | |
| url | text | NO | | NULL | |
| outfile | text | YES | | NULL | |
| shasum | varchar(64) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
6 rows in set (0.000 sec)
MariaDB [cowrie]> DESCRIBE input;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| session | char(32) | NO | MUL | NULL | |
| timestamp | datetime | NO | | NULL | |
| realm | varchar(50) | YES | | NULL | |
| success | tinyint(1) | YES | | NULL | |
| input | text | NO | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
6 rows in set (0.000 sec)
MariaDB [cowrie]> DESCRIBE ipforwards;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| session | char(32) | NO | MUL | NULL | |
| timestamp | datetime | NO | | NULL | |
| dst_ip | varchar(255) | NO | | | |
| dst_port | int(5) | NO | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
5 rows in set (0.000 sec)
MariaDB [cowrie]> DESCRIBE ipforwardsdata;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| session | char(32) | NO | MUL | NULL | |
| timestamp | datetime | NO | | NULL | |
| dst_ip | varchar(255) | NO | | | |
| dst_port | int(5) | NO | | NULL | |
| data | text | NO | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
6 rows in set (0.001 sec)
MariaDB [cowrie]> DESCRIBE keyfingerprints;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| session | char(32) | NO | | NULL | |
| username | varchar(100) | NO | | NULL | |
| fingerprint | varchar(100) | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.000 sec)
MariaDB [cowrie]> DESCRIBE params;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| session | char(32) | NO | | NULL | |
| arch | varchar(32) | NO | MUL | NULL | |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.000 sec)
MariaDB [cowrie]> DESCRIBE sensors;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| ip | varchar(255) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.000 sec)
MariaDB [cowrie]> DESCRIBE sessions;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | char(32) | NO | PRI | NULL | |
| starttime | datetime | NO | MUL | NULL | |
| endtime | datetime | YES | | NULL | |
| sensor | int(4) | NO | | NULL | |
| ip | varchar(15) | NO | | | |
| termsize | varchar(7) | YES | | NULL | |
| client | int(4) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
7 rows in set (0.000 sec)
MariaDB [cowrie]> DESCRIBE ttylog;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| session | char(32) | NO | | NULL | |
| ttylog | varchar(100) | NO | | NULL | |
| size | int(11) | NO | | NULL | |
+---------+--------------+------+-----+---------+----------------+
General Table info:
-
auth: authentication data: user, password, login status -
clients: ssh version, e.g.SSH-2.0-OpenSSH_6.6.1 -
downloads: if an authenticated user downloads a file we can find the path and the hash -
input: commands entered -
ipforwards: proxy attempts, source and destination IP -
ipforwardsdata: data attempted to be sent through direct-tcpip forwarding -
keyfingerprints: fingerprint of SSH key -
params: example entrylinux-x64-lsb -
sensors: id of honeypot (contains one value) -
sessions: sessions info; start, end, ip etc. -
ttylog: location of tty log
Also see Cowrie doc.
Look at all that pretty data
Quick Summary
- Data range: 2024-08-21 -> 2024-08-31
- Sessions: 82 839
- Logins
attempts: 95 035 - Distinct
users: 3 469- Only used one time: 1 144
- Distinct
passwords: 42 064- Only used one time: 30 852
- Distinct
IPs: 715- 98 distinct passwords per IP
- 22 distinct usernames per IP
Top IPs
SELECT ip, COUNT(*) as count
FROM sessions
GROUP BY ip
ORDER BY count desc
LIMIT 10;
+----------------+-------+
| ip | count |
+----------------+-------+
| 189.206.56.113 | 30058 |
| 218.92.0.116 | 7005 |
| 61.177.172.144 | 3532 |
| 178.128.16.191 | 3276 |
| 183.81.169.238 | 1665 |
| 85.133.202.15 | 1598 |
| 121.237.178.18 | 1304 |
| 103.140.73.49 | 918 |
| 92.118.39.17 | 572 |
| 101.46.57.30 | 567 |
+----------------+-------+
The 189.206.56.113 was our top man located at an ISP line in Mexico. 
Passwords
SELECT password, COUNT(*) as count
FROM auth
GROUP BY password
ORDER BY count desc
LIMIT 10;
+----------+-------+
| password | count |
+----------+-------+
| 123456 | 2578 |
| 123 | 686 |
| admin | 673 |
| password | 660 |
| 1234 | 619 |
| 12345 | 460 |
| 12345678 | 364 |
| root | 359 |
| 1 | 297 |
| P@ssw0rd | 221 |
+----------+-------+
No surprises there, same old, same old.
SELECT AVG(password_count) AS avg_password_count
FROM (
SELECT s.ip, COUNT(DISTINCT a.password) AS password_count
FROM sessions s
JOIN auth a ON s.id = a.session
GROUP BY s.ip
) AS password_counts;
98 distinct passwords per IP.
Usernames
SELECT username, COUNT(*) as count FROM auth GROUP BY username ORDER BY count desc limit 10;
+----------+-------+
| username | count |
+----------+-------+
| root | 72941 |
| admin | 1414 |
| user | 654 |
| ubuntu | 478 |
| test | 461 |
| debian | 298 |
| oracle | 268 |
| ftpuser | 208 |
| postgres | 140 |
| ubnt | 136 |
+----------+-------+
root stands for 77% of all attempts, far outrunning admin.
SELECT AVG(username_count) AS avg_count
FROM (
SELECT s.ip, COUNT(DISTINCT a.username) AS username_count
FROM sessions s
JOIN auth a ON s.id = a.session
GROUP BY s.ip
) AS counts;
22 distinct usernames per IP.
I did wonder if there are threat actors trying the same password multiple times.
SELECT a.username, a.password, s.ip, COUNT(*) AS count
FROM sessions s
JOIN auth a ON s.id = a.session
GROUP BY a.username, a.password, s.ip
HAVING COUNT(*) > 5
ORDER BY count desc
LIMIT 10;
+----------+----------+----------------+-------+
| username | password | ip | count |
+----------+----------+----------------+-------+
| root | admin | 193.201.9.156 | 116 |-- previous successful sign-ins so this makes sense
| root | admin | 85.192.56.68 | 79 |
| root | 0 | 183.81.169.238 | 68 |-- previous successful sign-ins so this makes sense
| root | eve | 183.81.169.238 | 67 |
| root | root | 183.81.169.238 | 67 |
| root | 1 | 183.81.169.238 | 66 |
| root | 123 | 183.81.169.238 | 66 |
| root | admin | 183.81.169.238 | 66 |
| root | 123456 | 183.81.169.238 | 66 |
| root | 123123 | 183.81.169.238 | 65 |
+----------+----------+----------------+-------+
There are a lot of duplicates, I guess it is not a bad idea to keep trying simple credentials, just waiting to a admin to be lazy or for some misconfiguration.
When are the Threat actors trying to login?
Logins over time:
SELECT DATE(s.starttime) AS date, COUNT(DISTINCT s.ip) AS distinct_ips, COUNT(*) AS number_of_attempts
FROM sessions s
GROUP BY date;
+------------+--------------+--------------------+
| date | distinct_ips | number_of_attempts |
+------------+--------------+--------------------+
| 2024-08-21 | 32 | 422 |
| 2024-08-22 | 172 | 5697 |
| 2024-08-23 | 144 | 4426 |
| 2024-08-24 | 228 | 21258 |
| 2024-08-25 | 214 | 27506 |
| 2024-08-26 | 177 | 5113 |
| 2024-08-27 | 153 | 4479 |
| 2024-08-28 | 154 | 5887 |
| 2024-08-29 | 140 | 5701 |
| 2024-08-30 | 121 | 3207 |
| 2024-08-31 | 96 | 3585 |
+------------+--------------+--------------------+
Except a couple of outliers (so far), there seems to be a few thousand of attempts each day.
Let’s look at the attempts and distinct IPs per hours of the day:
SELECT HOUR(s.starttime) AS hour_of_day, COUNT(DISTINCT s.ip) AS distinct_ips, COUNT(*) AS number_of_attempts
FROM sessions s
GROUP BY hour_of_day;
+-------------+--------------+--------------------+
| hour_of_day | distinct_ips | number_of_attempts |
+-------------+--------------+--------------------+
| 0 | 173 | 5915 |
| 1 | 90 | 4525 |
| 2 | 92 | 3216 |
| 3 | 108 | 3714 |
| 4 | 116 | 3428 |
| 5 | 111 | 3778 |
| 6 | 94 | 3292 |
| 7 | 143 | 3732 |
| 8 | 134 | 3934 |
| 9 | 103 | 3123 |
| 10 | 99 | 2299 |
| 11 | 85 | 1204 |
| 12 | 97 | 1903 |
| 13 | 130 | 2680 |
| 14 | 114 | 4087 |
| 15 | 95 | 5181 |
| 16 | 105 | 5062 |
| 17 | 110 | 4605 |
| 18 | 110 | 3748 |
| 19 | 109 | 3398 |
| 20 | 126 | 3584 |
| 21 | 136 | 3406 |
| 22 | 141 | 3249 |
| 23 | 124 | 4107 |
+-------------+--------------+--------------------+
I am not really sure if there are any insights to be had here based on the data available..
AbuseIPDB
We did upload start to upload data to AbuseIPDB in the previous part, so we now have some results. Note that we have reported data to Abuse for a much longer time than the data we have available in SQL. At the time of writing, we’ve reported 18k IP, yay! 
We are allowed to report 1000 times before the API limits us. If I look back over the last 30 days, we are maxing out at 173, so well within the limit.
Enrich data with check
AbuseIPDB also provide a pretty cool check API which can enrich any IP data we have.
Example of data in the GUI:
Using the API:
curl -G https://api.abuseipdb.com/api/v2/check \
--data-urlencode "ipAddress=220.247.223.56" \
-d maxAgeInDays=365 \
-d verbose \
-H "Key: KEY" \
-H "Accept: application/json"
Omitting the verbose key word we get a manageable amount of data, otherwise we get the full reports, i.e. each report that includes country origin, comment etc.
[EXPAND] Looking at the same IP with the API:
{
"data": {
"ipAddress": "220.247.223.56",
"isPublic": true,
"ipVersion": 4,
"isWhitelisted": false,
"abuseConfidenceScore": 100,
"countryCode": "LK",
"usageType": null,
"isp": "Sri Lanka Telecom PLC",
"domain": "slt.lk",
"hostnames": [
"56.sta.idc-2.slt.lk"
],
"isTor": false,
"totalReports": 14791,
"numDistinctUsers": 1251,
"lastReportedAt": "2024-08-25T19:46:31+00:00"
}
}
The API does not appear to give us the total amount of reports since we are limited to one year. The GUI tells us it was reported 27 575 while the API can “only” find 14 791 reports.
Other than that, the data provided give us great value with fields such as: abuseConfidenceScore, isp, isTor, numDistinctUsers. This can for example be used in a SOC to enrich incoming data to easily separate a targeted attack or “background noise” from the internet. Like a poor mans Greynoise. 
The Standard level is limited to 1 000 checks per day, but by paying a bit each month you can increase the quota.
Conclusion
It’s fun and easy to play around with SQL after working several years with KQL.
That people continuously attack exposed SSH servers with common username and passwords are of course not a surprise. What I do look forward to is starting to allow threat actors access to the command line and analyzing what they are trying to do. There are already many reports out there so I assume we’ll se pretty much the same. 