«

»

Dec 08 2014

New tool: kippo-log2db.pl

I’m copying an interesting email from SANS’ mailing list, by Jim Clausing. Jim has developed a new tool as a replacement for Kippo2MySQL. The new tools is called kippo-log2db.pl and you can download it here (local copy).

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 bruteforcelab.com) 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

7 comments

2 pings

Skip to comment form

  1. Jim

    I found a bug in the old version and updated the script to version 0.9, you’ll want to update your copy. The new one is at http://handlers.sans.org/jclausing/kippo-log2db.pl

    1. Ion

      Thanks for the notice Jim, I updated the script.

    2. Ion

      Hi Jim, hopefully this reply will notify you. There was a comment from Moin above that you might want to take a look at. Thanks.

  2. Moin

    Hi,

    Thank you for the script. I am running kippo for sometime and hadn’t configure MySQL logging beforehand. Your script will definitely help me. But, I am getting following error when I run the script.

    #####DBD::mysql::st fetchrow_array failed: fetch() without execute() at ./kippo-log2db.pl line 98, line 6.

    #############DBD::mysql::st execute failed: Column ‘sensor’ cannot be null at ./kippo-log2db.pl line 125, line 19.

    I have already created the ‘kippo’ database and also created the tables using the sql script at /opt/kippo/doc/sql/mysql.sql

    Do I need to do something more to make the script work?

    – Moin

    1. Ion

      Hi Moin, this is not my script actually. Jim from SANS had created it. Look at the bottom of the quoted text above for his email. If you find a solution please make sure to let me know! Thanks.

      1. Moin

        Thanks Ion, I will contact with Jim. I will definitely notify you if I have any update.

      2. Jim

        Looking into it and will share another update when I figure out what is going on and fix it. Thanx for letting me know.

  1. 21 dni z honeypotem Kippo i grupa PsychoSSH | Kamil Frankowicz

    […] mieć “ładne” statystyki i używać Kippo Graph, loguj od początku do bazy danych. Migracja z plików tekstowych jest dosyć kłopotliwa i na chwilę obecną nie migruje informacji o ilości udanych ataków […]

  2. Making Sense of 2,027,189 Login Attempts | Peter M Stewart

    […] have taken a few days to throw something together in Python, a bit of searching pointed me at Ion’s blog about kippo-log2db. I was getting […]

Leave a Reply

More in Honeypots
Run HoneyDrive 3 on Hyper-V server
s06 Bringing PWNED To You Interesting Honeypot Trends Elliott Brink
DionaeaFR: adding parameterized date range
Kippo-Graph 1.3 released!
Adding ElasticSearch support to Kippo SSH honeypot
Close