Kippo2MySQL

Kippo2MySQL is yet another simple piece of software that simply extracts some very basic stats from Kippo’s text-based log files (a mess to analyze!) and inserts them in a MySQL database. Then you can run some queries and of course visualize the data if you want to.

Many things are hardcoded or dead simple, but it does the job. The file is a modified version of “kippo-stats” perl script originally writen by Tomasz Miklas and modified by mig5. Later on I might update Kippo-Graph or write a new tool specifically for Kippo2MySQL to generate some graphs from this type of data.

Update: There is a new tool to transfer Kippo entries to a MySQL DB called kippo-log2db.pl.

DOWNLOAD Kippo2MySQL:

INSTALLATION:

You will have to change the script and enter the correct paths, your MySQL credentials, have a database and a db user created beforehand, etc. It’s pretty straightforward if you take a look at the script and have some basic understanding of perl and MySQL server.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

  • Pingback: Kippo2MySQL v0.1, populate a MySQL DB with data from Kippo logs! » BruteForce Lab's Blog()

  • DisSsha

    Hi,

    I just want to tell you that i had theses line in your script to avoid alert and miss duplicated entries :
    $SQL = “drop table hosts;”;
    $DROP = $dbh->do($SQL);
    $SQL = “drop table clients;”;
    $DROP = $dbh->do($SQL);
    $SQL = “drop table auth;”;
    $DROP = $dbh->do($SQL);

    at line 30.

    Regards,

    • Ion

      Hello there 🙂 I missed this obvious thing I guess. Thanks for contributing! I will add it to the file.
      PS. I had to rewrite your comment by myself after a wordpress hiccup.

  • mara

    Just used it… it’s great! Thanks!
    A timestamp would also be very useful…
    I’ll try to import it.. and feed back…
    Thank you again!

  • MadHat Unspecific

    I used it to import old data into my new kippo logging system, but I needed to add some entries to better escape passwords. I got a lot of SQL errors the first time. I can send what I did if you are maintaining, but like you said newer versions support MySQL directly now.

    • Ion

      Hello MadHat, yes please do so I can update the code. I’m sending you an email.
      Regards, Ion.

  • Mato

    Hello all.
    I want ask did you do modification of this script for current structure of mysql database, that is used by Kippo-graph? It would be a big help for me. Thank you for replies.

    • Ion

      Hello Mato.
      Unfortunately no, and if I remember correctly when I first wrote this script it wasn’t easy to do that (if not impossible). But, you can still run your own queries against the resulting DB to get some basic stats.

      If you have a big number of interesting kippo text logs, let me know, and perhaps I can help you by modifying the script for you.

      Regards, Ion

      • Mato

        Hello Ion.
        Thank you for your reply. Yes I have a big number of text logs. I work on research with Kippo and my mysql database and backups were destroyed. Now I am desperate. 🙁 I would very need convert txt logs to mysql database. I will be very grateful for your help.

      • Ion

        Hello Mato,
        please use the contact form and send me a link to your log files (you can use Dropbox’s share link feature or something like that) and I will see if I can do anything more.

        Regards,
        Ion

      • Mato

        Hello Ion,
        Thanks. I sent the link via contact form.

        Best regards
        Mato

  • Kavan

    Hello Ion/all,

    Have you tried running this script on a Mac? I ran into dependency issues from the outset:

    *******************************************************
    sh: md5sum: command not found
    install_driver(mysql) failed: Can’t locate DBD/mysql.pm in @INC (@INC contains: /Library/Perl/5.16/darwin-thread-multi-2level /Library/Perl/5.16 /Network/Library/Perl/5.16/darwin-thread-multi-2level /Network/Library/Perl/5.16 /Library/Perl/Updates/5.16.2 /System/Library/Perl/5.16/darwin-thread-multi-2level /System/Library/Perl/5.16 /System/Library/Perl/Extras/5.16/darwin-thread-multi-2level /System/Library/Perl/Extras/5.16 .) at (eval 3) line 3.
    Perhaps the DBD::mysql perl module hasn’t been fully installed,
    or perhaps the capitalisation of ‘mysql’ isn’t right.
    Available drivers: DBM, ExampleP, File, Gofer, Proxy, SQLite, Sponge.
    at kippo2mysql.pl line 51.
    ********************************************************

    I tried using cpan to install DBD::mysql but it too fails because of permissions:

    ********************************************************
    ERROR: Can’t create ‘/Library/Perl/5.16/darwin-thread-multi-2level/auto/DBD/mysql’
    mkdir /Library/Perl/5.16/darwin-thread-multi-2level/auto/DBD: Permission denied at /System/Library/Perl/5.16/ExtUtils/Install.pm line 494.

    ********************************************************

    I would try sudo-ing but I shouldn’t need to…

    If anybody has tried using this script on a Mac, please let me know if you encountered any of the above issues 🙂

    Cheers,

    Kavan

    • Ion

      Hello Kavan, thanks for letting me know.

      I don’t currently own a Mac for testing, so I can’t tell what is missing. But I can test it next week, and I will if I find time. I ‘ll let you know.

      If everything else fails you can always run the script on a Linux VM/VPS.

      Regards,
      Ion

      • Kavan

        Many thanks, Ion. No need to test it out on a Mac for my sake; ultimately I will be using it on Linux anyway 🙂

  • stonia

    Hello!
    when I execute the perl script in order to get the data of my kippo logs into a mysql database, I hava the following problem (see picture)

    • Ion

      Hi stonia, I haven’t updated this script for a while, there is a newer one here: http://bruteforce.gr/new-tool-kippo-log2db-pl.html that you might want to try.

      • stonia

        Hi Ion, thanks a lot for your quick answer 🙂
        I executed the kippo-log2db.pl after i adapted the sql username and password as well as the path for the kippo logs accordingly.
        There were a lot of error messages:
        “Column ‘sensor cannot be null at kippo-log2db.pl line x” occured a lot as well as
        “fetchrow_array faled: fetch() without execute() at kippo-log2db.pl line x”

Read previous post:
Kippo2MySQL v0.1, populate a MySQL DB with data from Kippo logs!
Kippo-Graph 0.6 released!
Kippo-Graph 0.5.1 released.
Kippo-Graph 0.5 released!
Kippo-Graph 0.4 released, introducing Kippo-Geo!
Close