Some find the built in logging utility within Cowrie to be difficult to read, so in this post we will install a MySQL database to make it easier to read the ssh attack data. This post assumes that you already have an operational Cowrie instance (you can try this one) running on Ubuntu 14.04 and will describe how to install MySQL, and how to make simple attack data queries (although more complex queries will probably be the subject of a future post). I don’t claim to be a MySQL expert, but this has been tested and it works for me.
First, we need to log in to our Cowrie instance as the root user (remember, if you followed my other Cowrie blog post, we’re using port 8742 for admin) and install the additional dependencies, mysql-server and python-mysqldb.
apt-get install mysql-server python-mysqldb
During the installation, we’ll be presented with a screen that asks us to create a MySQL password:
When the installation is complete, we’ll start building the database by assigning it a name and associating it with non root user cowrie.
mysql -u root -p
We’ll have to enter our password, then once we’re greeted with a MySQL prompt that looks like this:
mysql>, we’ll enter the following commands:
CREATE DATABASE cowrie;
GRANT ALL ON cowrie.* TO cowrie@localhost IDENTIFIED BY 'your_password';
We’ll now navigate to the home directory of user cowrie to build the table structures for the database.
Now we’ll enter the SQL shell, tell it which specific database we’d like to work with, tell it where to find the table structure information to build the database, then exit:
mysql -u cowrie -p
We’ll now exit as root user and log back in to our admin port (8742) as user cowrie. Navigate to /cowrie and use the stop script to shutdown the honeypot so we can make changes to the configuration file:
Now we’ll open the Cowrie configuration file, cowrie.cfg, and find the section that begins with “[database_mysql]”. We need to uncomment the entire section and enter our database password so that it looks like this:
We’ll save and exit the file, then restart the honeypot with the start script in /cowrie:
Lets ensure that Cowrie is running and listening for attacks on port 22:
Now that we’ve ensured that Cowrie is back up, let’s test our database. Log out, then using something like PuTTY we’ll try to connect to our server on port 22 by entering username and password combinations that we know are not in username.txt. Then, we’ll log back in to our server using our admin port as user cowrie and log into our database.
mysql -u cowrie -p
We can query MySQL to show the databases that are available, show the tables within those databases and describe the information contained in each of those tables. In the screenshot below, we’re shown that cowrie is an available database, and that the database is comprised of the following tables: auth, clients, downloads, input, sensors, sessions and ttylog. If we choose one of these tables, we can drill down and have MySQL describe what kind of information is contained in each table. In this example, we’ve queried the ‘auth’ table and we discover that this is where we’ll be able to find all of the username and password combinations that were attempted by attackers trying to enter our honeypot (this works for any table):
We can make simple queries of our database with the following format:
SELECT * FROM (table);
Where “*” is a wildcard and (table) is the table we wish to query. So, if we issue the following query,
SELECT * FROM auth;
we’ll see an ordered table of all of the username/password combinations that were attempted on our honeypot:
Likewise, if we use
SELECT * FROM sessions;
we’ll see all of the IP addresses from which attacks on our honeypots originated:
That’s it, for now. We’ve installed and configured a MySQL database along side an existing Cowrie instance and ensured that it was properly logging attacks against our server. SQL is a powerful language, these queries barely scratch the surface of what is possible and I encourage you to read the MySQL Reference Manuals to learn more.
I tried to link to as much official documentation as I could to give you a head start in answering questions and learning more. If you still have a question and I can’t answer it, I either know who can or I can point you in the right direction
You can find me on Twitter or email me sehque(at)tuta(dot)io.