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 (plus all the other things like adding a new user etc, as described in the previous post).

  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”).

2 comments

3 pings

    • Andrew Waite on December 9, 2011 at 4:16 PM
    • Reply

    Word of caution with phpmyadmin; it’s had some serious vulnerabilities disclosed in past versions and is a favourite of those looking to cause damage. From my own experiences I’d strongly suggest moving it from its default path (possibly adding a canary page at that location) and restrict access from untrusted source locations.

      • Ion on December 9, 2011 at 5:15 PM
        Author
      • Reply

      Thanks Andrew, yes it seems that phpmyadmin is frequently targeted. In fact some web logs show that a installation I’m running has already been targeted by automated GET requests. I assume that the shipped version with every distro is secure but following your suggestion is recommended anyway.

  1. […] On the contrary, if you have setup MySQL logging as per the instructions, then have in mind the following useful SQL queries, again taken from the blog of Andrew, here and […]

  2. […] course, this could all have been avoided if I’d remembered to enable MySQL logging in the first […]

  3. […] need to set up Cowrie to log to a mySQL database. Ion has very good instructions for doing that here on his web […]

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Read previous post:
Installing Kippo SSH Honeypot on Ubuntu

I decided to setup a simple ssh honeypot in a small VPS (192MB RAM) to see if they actually record...

Close