I've been running kippo for several years now on a couple of honeypots that I have around and when I started I was just logging to the text logs that kippo can create.  Since then, kippo now supports logging directly to a MySQL database and some other folks (especially Ioannis �??Ion�?� Koniaris at bruteforce.gr) have created some nice tools to generate reports from kippo data.  These tools expect the data to be in the kippo MySQL database schema.  Having logged several years worth of stuff to the text log files, I didn't want to lose all that data, but I did want to be able to take advantage of some of the neat tools that Ion has developed, so I needed a way to get that data from the text logs to the supported db schema.  Now Ion had created a script that he called Kippo2MySQL, but that converted things to his own schema and lost some data in the process.  Using that as inspiration, however, I have created a script that will read the kippo text logs and populate a kippo database (using the same schema that kippo can now log to directly).  The only hitch that I discovered is that when kippo is logging to text logs and restarts, it doesn't maintain unique session ids, it starts over again from 1.  This caused me have to make a small change to the sessions table.  I had to change the primary key from ID to (ID,STARTTIME).  Fortunately, I haven't had an collisions where multiple sessions with the same id actually had ttylogs which is where things might get a bit sketchy.  This was accomplished with

mysql> alter table sessions drop primary key, add primary key(id,starttime);

yielding

mysql> show create table sessions\G
*************************** 1. row ***************************
       Table: sessions
Create Table: CREATE TABLE `sessions` (
  `id` char(32) NOT NULL,
  `starttime` datetime NOT NULL,
  `endtime` datetime DEFAULT NULL,
  `sensor` int(4) NOT NULL,
  `ip` varchar(15) NOT NULL DEFAULT '',
  `termsize` varchar(7) DEFAULT NULL,
  `client` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`,`starttime`),
  KEY `starttime` (`starttime`,`sensor`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

I've imported about 800K login attempts and can now play with kippo-graph or (soon, I haven't had the chance yet) kippo2elasticsearch.  The script can be found here though I have one small issue that I'll try to fix shortly, I think it is printing out too many #'s, I set it to print out 1 every 10,000 lines it reads from the log files and it seems like I'm getting way more than that, but that is a minor annoyance, maybe I'll just add a switch to turn that off later.  In the meantime, enjoy and if you find any problems or have ideas for improvement, let me know either in the comments or by e-mail at my address below.

References:

http://handlers.sans.org/jclausing/kippo-log2db.pl

---------------
Jim Clausing, GIAC GSE #26
jclausing --at-- isc [dot] sans (dot) edu