Mar 30 2014

Transferring Kippo’s data to ElasticSearch

I have been investigating ElasticSearch and Kibana for some projects lately and I’ve come to appreciate the easiness of using the two pieces of software together for storing and visualizing data.

This will be an introductory post to something bigger, but I just want to throw the idea out there: let’s transfer honeypot data to ElasticSearch and use Kibana for easy visualization and creation of dashboards.

For Kippo, it all starts with the MySQL database. Our first move is to transfer entries from the DB to ElasticSearch. Now, EleasticSearch accepts JSON documents as input, so we’ll have to convert MySQL rows to JSON objects. The second step is to send those JSON objects to ElasitcSearch for indexing.

The obvious table to convert and send to ElasticSearch is the “auth” table which contains login attempts (timestamp, username, password, success, etc). Here is a quick Python script to do just that (you will need pony and pyes):

#!/usr/bin/env python

import pony.orm
import pony.options
import collections
import json
import pyes

mysql_host = 'localhost'
mysql_port = 3306
mysql_user = 'username'
mysql_pass = 'password'
mysql_db = 'database'

es_host = 'localhost'
es_port = 9200

# We need this, otherwise pony returns an error during the SELECT
pony.options.MAX_FETCH_COUNT = 999999

db = pony.orm.Database('mysql', host=mysql_host, port=mysql_port, user=mysql_user, passwd=mysql_pass, db=mysql_db)

with pony.orm.db_session:
    auth_rows = db.select('SELECT * FROM auth')

es = pyes.ES(es_host + ':' + str(es_port))

for auth_row in auth_rows:
    auth_dict = collections.OrderedDict()
    auth_dict['id'] = auth_row[0]
    auth_dict['session'] = auth_row[1]
    auth_dict['success'] = auth_row[2]
    auth_dict['username'] = auth_row[3]
    auth_dict['password'] = auth_row[4]
    auth_dict['timestamp'] = auth_row[5].strftime("%Y-%m-%dT%H:%M:%S")
    auth_json = json.dumps(auth_dict)
    print auth_json
    es.index(auth_json, 'kippo', 'auth')

(a repo for this and similar scripts has been added here: kippo2elasticsearch)

You can then go to Kibana, add a new histogram panel and in seconds (literally) have the following visualization of time based attack summaries:


Another idea is to use an IP-to-country library and include another field in the JSON object that you can then use in Kibana to create a heatmap of attacks, etc. There are generally many possibilities and I would like to gather ideas if you have anything in mind.

As I said this is just an introductory post, I will come back to this idea in the future, publish some proper open source scripts to parse the data and perhaps guides on how to visualize the results with Kibana. Let me know what you think.

1 comment

4 pings

  1. Cerasela Adriana


    I want to attack kippo and dionaea with metasploit. For kippo and dionaea I use honeydrive virtual machine, and for metasploit, a kali linux machine. After de attack i want to see a report using kibana and dionaeaFR. Can you help me? This is for a project.


  1. Security-Vision » Transferring Kippo’s data to ElasticSearch

    […] Security Bloggers Network, Ion, […]

  2. Kippo attack heatmap in seconds using Kibana and Kippo2ElasticSearch - BruteForce Lab's Blog

    […] « Transferring Kippo’s data to ElasticSearch […]

  3. Adding ElasticSearch support to Kippo SSH honeypot - BruteForce Lab's Blog

    […] data, especially from honeypots. If you follow my blog, you would have seen my first attempts at transferring Kippo’s data to ElasticSearch, or creating Kibana dashboards to visualize SSH attacks. These eventually led to the […]

  4. Kippo + ElasticSearch | Dominican Honeynet Project

    […] In the past, our friends from Bruteforce Lab have made some nice efforts to integrate Kippo’s logs with ElasticSearch using some pretty good scripts that you can find here. […]

Leave a Reply

More in Honeypots, Visualization
Kippo-Graph 0.9.3 released, with new component: “Kippo-IP”
Kippo-Graph 0.9.2, with Kippo-Playlog!
Kippo-Malware update #2
Kippo-Malware update
Announcing Kippo-Malware