Dec 08 2011

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
GRANT ALL ON kippo.* TO 'kippo'@'localhost' IDENTIFIED BY 'Kippo-DB-pass';
  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;

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:

host = localhost
database = kippo
username = kippo
password = Kippo-DB-pass
  1. We are now ready to start Kippo again:

Check that Kippo is running:

netstat -antp

where you should see a line like this: tcp 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;
  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”).


3 pings

  1. Andrew Waite

    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.

    1. Ion

      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. The big post of Kippo scripts, front-ends, bash one-liners and SQL queries » bruteforcelab.com

    […] 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. Making Sense of 2,027,189 Login Attempts | Peter M Stewart

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

  3. Adding An SSH Honeypot – Execute Malware Blog

    […] 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

More in Honeypots
Installing Kippo SSH Honeypot on Ubuntu