Logging Kippo events using MySQL DB

Continuing on the previous post about Kippo SSH honeypot, let’s see how we can make our lives easier and log its events in a MySQL database instead of the difficult to read text-based log files (located at kippo-dir/log by default). This post assumes that you have already followed the procedure and successfully installed Kippo on your Ubuntu Server.

1. We login as root in our box and install the required software packages:

apt-get install python-mysqldb mysql-server

MySQL server will ask for a root password, enter something a bit complex.

1. We setup the database for Kippo logging:

mysql -u root -p

CREATE DATABASE kippo;
GRANT ALL ON kippo.* TO 'kippo'@'localhost' IDENTIFIED BY 'Kippo-DB-pass';
exit

1. We go to Kippo’s directory (normally /home/kippo/kippo/ if you followed the previous post) and load the table structures into the database:

mysql -u kippo -p

USE kippo;
source ./doc/sql/mysql.sql;
exit

At this stage re-login as ‘kippo’ user into the system.

1. If Kippo is running we will have to kill it in order to change its configuration and start it again.

ps x

Look for a line like this: 10650 ? Sl  0:00 /usr/bin/python /usr/bin/twistd -y kippo.tac -l log/kippo.log -pidfile kippo.pid

The first column shows the process ID, and you will use this number to kill it:

kill 10650

1. We are ready to make the necessary changes to Kippo’s config file:

nano kippo.cfg

Here we un-comment the following lines and type the corrent data:

[database_mysql]
host = localhost
database = kippo
username = kippo
password = Kippo-DB-pass

1. We are now ready to start Kippo again:

./start.sh

Check that Kippo is running:

netstat -antp

where you should see a line like this: tcp 0  0 0.0.0.0:22  0.0.0.0:*  LISTEN  10650/python

We are now ready! To see the logging events in the database, you can use simple SQL commands like:

$ mysql -u kippo -p

USE kippo;
SELECT * FROM auth;

1. (OPTIONAL) In order to make things even easier we can install phpmyadmin, a web GUI for our mysql server:

sudo apt-get install phpmyadmin

it will be located at: http://server-ip/phpmyadmin and you can login as root (with MYSQL’s root password that you entered above) or better yet as kippo user (using kippo’s password, in our example “Kippo-DB-pass”).