Nepenthes had awful logging, huge logfiles, pretty useless for most people. Some people even started writing parsers for the logfiles to extract&convert the usefull information for use in a database. For dionaea, I decided to stick with awful logging to textfiles, but provide a useful alternative which is easy to setup and maintain, feature rich and allows retrieving information in a useful way, so you don't have to grep.
Therefore, SQLite is used to write usefull information down to disk in the logsql.py script.
I know, SQLite is not PostgreSQL, PostgreSQL is superior in many ways, but it requires some more steps to setup, where SQLite just works out of the box. SQLite does not support concurrency, but as dionaea does not access the database simulaneaously, there were no problems with database-concurrency. On the other hand, if it works with SQLite, it will work with PostgreSQL too, all you'll have to do is adjust some things.

The definition of useful information is undefined, therefore I decided to go for things I want to see for now:

• connections
• exploits
• malware offers
• malware downloads

After adjusting the code, to be able to indicate a relation between connections, I had to recompile dionaea's python to support sqlite, and updated the docs.
After verifying 'import sqlite3' worked, I started hacking the code to log incidents to the database.
Once some things got into the database, I wrote some queries to make use of the data.

Which ports got the most connections incoming:

SELECT 
	COUNT(local_port) AS hitcount, 
	local_port AS port
FROM 
	connections  
WHERE 
	connection_type = 'accept' 
GROUP BY 
	local_port 
HAVING 
	COUNT(local_port) > 10

Distribution of the attacks over a day:

SELECT 
	ROUND((connection_timestamp%(3600*24))/3600) AS hour,
	COUNT(*) 
FROM 
	connections 
WHERE 
	connection_parent IS NULL 
GROUP BY 
	ROUND((connection_timestamp%(3600*24))/3600);

As I do not have enough data for a single day yet, we miss the hours 7-11.

Which files got downloaded most:

SELECT 
	COUNT(download_md5_hash), 
	download_md5_hash
FROM 
	downloads
GROUP BY
	download_md5_hash
ORDER BY
	COUNT(download_md5_hash) DESC

Which host attacked us most:

SELECT 
	COUNT(remote_host),
	remote_host 
FROM 
	connections 
WHERE 
	connection_type = 'accept' 
GROUP BY 
	remote_host 
ORDER BY 	
	COUNT(remote_host) 
	DESC 
LIMIT 
	10;

Count of parent connections remote ip address for each file offerd:

SELECT 
	count(*),
	download_md5_hash,
	remote_host 
FROM 
	connections 
NATURAL JOIN 
	downloads 
GROUP BY 
	download_md5_hash,remote_host
ORDER BY 
	download_md5_hash
	DESC

Due to a bug in the logging (I'll take care, just to prevent complains for early adaptors), the next query required a fix:

UPDATE 
	connections 
SET 
	connection_tree = connection 
WHERE 
	connection_tree IS NULL

Number of distinct attackers per distinct malware file:

SELECT 
	download_md5_hash, 
	COUNT(DISTINCT tree.remote_host) 
FROM 
	downloads 
	NATURAL JOIN connections AS parent 
	JOIN connections AS tree ON (parent.connection == tree.connection_tree) 
GROUP BY 
	download_md5_hash;

Now, lets see if we have a host offering more than one malicious file:

SELECT 
	tree.remote_host,
	COUNT(DISTINCT download_md5_hash) 
FROM 
	downloads 
	NATURAL JOIN connections AS parent 
	JOIN connections AS tree ON (parent.connection == tree.connection_tree) 
GROUP BY 
	tree.remote_host
HAVING
	COUNT(DISTINCT download_md5_hash) > 1;

We have one, lets see which files …

SELECT 
	DISTINCT download_url,
	offer_url,
	download_md5_hash
FROM 
	downloads
	NATURAL JOIN offers 
	NATURAL JOIN connections AS parent 
	JOIN connections AS tree ON (parent.connection == tree.connection_tree) 
WHERE
	tree.remote_host = '10.224.252.47';

Obviously there is a bug, reporting download urls as ftp urls, when they were tftp urls initially … But, the 2 different files come from the same location, so it is likely the tftp transfer broke.

Number of downloads by location:

SELECT 
	COUNT(*),
	download_url 
FROM 
	downloads 
GROUP BY 
	download_url 
ORDER BY 
	COUNT(*) 
	DESC;

As mentioned previously, tftp downloads get reported as ftp downloads …

Interested which dcerpc calls get attacked most?

SELECT 
	COUNT(*),
	dcerpc_uuid,
	dcerpc_opnum 
FROM 
	dcerpcs 
GROUP BY 
	dcerpc_uuid,
	dcerpc_opnum 
ORDER BY 
	COUNT(*) 
DESC;

Using p0f, dionaea can collect fingerprints for incoming attacks, lets play with the data …

Count of operating system genre:

SELECT 
	COUNT(*),
	p0f_genre 
FROM 
	p0fs 
GROUP BY 
	p0f_genre 
ORDER BY 
	COUNT(*) 
	DESC;

Windows dominates, 10% unknown …

Split by operating system version:

SELECT 
	COUNT(*),
	p0f_genre,
	p0f_detail 
FROM 
	p0fs 
GROUP BY 
	p0f_genre,
	p0f_detail 
ORDER BY 
	COUNT(*) 
	DESC;

Obviously we are missing Windows Vista and maybe even Windows 7, I'm sure at least Vista got it's cut on the attacks, but as p0f fingerprints are rather outdated, there is nothing I can do about it. But, once there are more recent fingerprints, identifying Vista and Windows 7, they will show up.

But, lets see which ports got hit by the Linux hosts:

SELECT 
	COUNT(*),
	local_port 
FROM 
	connections 
	NATURAL JOIN p0fs 
WHERE 
	p0f_genre = 'Linux' 
GROUP BY 
	local_port;

So, something was accessing the webserver on port 80.

Given the possibility to improve a horrible situation from the beginning, I wanted to be able to correlate a malware download with the initiating connection. Unfortunately there can be more than one connection involved before downloading the malware, as an example take an exploit, which spawns a bindshell, which accepts a connection, which triggers a download via ftp. So, you have at least 3 connections, and all belong to each other hierarchical,

• connection to exploitable service #1
  • bindshell listening #2 (parent #1)
    • bindshell accept #3 (parent #2)
      • malware offer (parent #3)
      • malware download (parent #3)

So, as I wanted to store the information in rdbms, namely SQLite, I knew rdbms do not support hierarchical structures that good, you can store information hierarchical, but querying is hard. For example to get the initiating connection for the malware download in previous example you'd have to walk the parents until there is no parent left. Thats no real problem, if you can access the database with a cursor, but SQLite does not support cursors. Therefore, it sounded reasonable to store the id of the upper-most parent for each connection.

• connection to exploitable service #1
  • bindshell listening #2 (parent #1, tree #1)
    • bindshell accept #3 (parent #2, tree #1)
      • malware offer (parent #3, tree #1)
      • malware download (parent #3, tree #1)

I'll try to explain how to accomplish this in python.

The first snippet resolves a python dbi cursor result, so you get a list of dicts, and can access the items by name instead of an index. It will break if you have multiple columns with the same name, but as long as you take care of forming your queries well, it works fine.

def resolve_result(resultcursor):
	names = [resultcursor.description[x][0] for x in range(len(resultcursor.description))]
	resolvedresult = [ dict(zip(names, i)) for i in resultcursor]
	return resolvedresult

Next, some formatting to print a connection, depending on type:

def print_connection(c, indent):
	if c['connection_type'] == 'accept':
		print("%*s connection %i %s %s %s %s:%i <- %s:%i" % ( indent, " ", c['connection'], c['connection_protocol'], c['connection_transport'], c['connection_type'], c['local_host'], c['local_port'], c['remote_host'], c['remote_port']) )
	elif c['connection_type'] == 'connect':
		print("%*s connection %i %s %s %s %s:%i -> %s/%s:%i" % ( indent, " ", c['connection'], c['connection_protocol'], c['connection_transport'], c['connection_type'], c['local_host'], c['local_port'], c['remote_hostname'], c['remote_host'], c['remote_port']) )
	elif c['connection_type'] == 'listen':
		print("%*s connection %i %s %s %s %s:%i" % ( indent, " ", c['connection'], c['connection_protocol'], c['connection_transport'], c['connection_type'], c['local_host'], c['local_port']) )

Now, we open the database and retrieve the root-connections. Root-connections initiate an attack, so they have no parent-connection.

dbh = sqlite3.connect("/tmp/test.sqlite")
cursor = dbh.cursor()
 
result = cursor.execute("SELECT * from connections WHERE connection_tree = connection OR connection_tree IS NULL ")
connections = resolve_result(result)

Now we can iterate through the connections, and print each connections information:

for c in connections:
	connection = c['connection']
	print_connection(c, 1)

But we want to print the child-connections too, and the possible child-child-connection, and possible child-child-child …, so we will use recursion:

def recursive_print(cursor, connection, indent):
	result = cursor.execute("SELECT * from connections WHERE connection_parent = ?", (connection, ))
	connections = resolve_result(result)
	for c in connections:
		if c['connection'] == connection:
			continue
		print_connection(c, indent)
		recursive_print(cursor, c['connection'], indent+2)

and change the loop on all root connections to print the child-connections too:

for c in connections:
	connection = c['connection']
	print_connection(c, 1)
	recursive_print(cursor, c['connection'], 2)

This already gives good results:

  connection 610 smbd tcp accept 10.69.53.52:445 <- 10.65.34.231:2010
   connection 611 remoteshell tcp listen 10.69.53.52:1957
     connection 612 remoteshell tcp accept 10.69.53.52:1957 <- 10.65.34.231:2135
       connection 613 ftpctrl tcp connect 10.69.53.52:37065 -> 10.65.34.231/None:8218
         connection 614 ftpdata tcp listen 10.69.53.52:62087
           connection 615 ftpdata tcp accept 10.69.53.52:62087 <- 10.65.34.231:2308

After adding code to print all information we have for each connection, it looks like this:

  connection 610 smbd tcp accept 10.69.53.52:445 <- 10.65.34.231:2010
   dcerpc request: uuid '3919286a-b10c-11d0-9ba8-00c04fd92ef5' opnum 9
   p0f: genre:'Windows' detail:'XP SP1+, 2000 SP3' uptime:'-1' tos:'' dist:'11' nat:'0' fw:'0'
   profile: [{'return': '0x7c802367', 'args': ['', 'CreateProcessA'], 'call': 'GetProcAddress'}, ...., {'return': '0', 'args': ['0'], 'call': 'ExitThread'}]
   service: bindshell://1957
   connection 611 remoteshell tcp listen 10.69.53.52:1957
     connection 612 remoteshell tcp accept 10.69.53.52:1957 <- 10.65.34.231:2135
       p0f: genre:'Windows' detail:'XP SP1+, 2000 SP3' uptime:'-1' tos:'' dist:'11' nat:'0' fw:'0'
       offer: fxp://1:1@10.65.34.231:8218/ssms.exe
       download: 1d419d615dbe5a238bbaa569b3829a23 fxp://1:1@10.65.34.231:8218/ssms.exe
       connection 613 ftpctrl tcp connect 10.69.53.52:37065 -> 10.65.34.231/None:8218
         connection 614 ftpdata tcp listen 10.69.53.52:62087
           connection 615 ftpdata tcp accept 10.69.53.52:62087 <- 10.65.34.231:2308
             p0f: genre:'Windows' detail:'XP SP1+, 2000 SP3' uptime:'-1' tos:'' dist:'11' nat:'0' fw:'0'

So, if you want to have a gui for the honeypot, you could use the internal http service and script the webinterface in python, within the honeypot itself, or create static html pages using a cron job and serve them, or push them somewhere else.

Sidenote
I changed the addresses, protecting the decoy&attackers.