View source for HoneyPot: Dionaea SQL Logging

logging

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

SQLite rocks

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.
attacked ports

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

hitcount	port
75	80
995	135
3982	445
29	1957
attacks over a day

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);

hour	hits
0.0	20
1.0	140
2.0	220
3.0	352
4.0	299
5.0	42
6.0	200
12.0	283
13.0	302
14.0	227
15.0	647
16.0	351
17.0	264
18.0	274
19.0	398
20.0	285
21.0	304
22.0	329
23.0	142
24.0	229

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

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

count(download_md5_hash)	download_md5_hash
38	df51e3310ef609e908a6b487a28ac068
19	14a09a48ad23fe0ea5a180bee8cb750a
6	32b0a00ffb8799a7782a19ee99a0214f
5	6e0a868563ee18fe6569796c7ce169be
3	2fa0e36b36382b74e6e6a437ad664a80
3	329c9d3163d9bf8f4bb9f0d1b846b6aa
3	4f4f2ebc395b0fbea708acfc3930ec6d
2	1d419d615dbe5a238bbaa569b3829a23
1	27f96b0496502104cf0bfc01391e1408
1	6c9d56535401bbd9052f499d05816ced
1	a8640b9698542689880b7619dbde1293
busy attackers

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(remote_host)	remote_host
1655	10.204.202.23
420	10.2.101.193
234	10.246.93.128
224	10.208.119.223
120	10.54.151.201
120	10.129.95.105
120	10.174.16.255
120	10.234.207.36
120	10.133.39.52
120	10.31.104.74
how often did an attacker ask us to download a file

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

count(*)	download_md5_hash	remote_host
9	df51e3310ef609e908a6b487a28ac068	10.252.215.142
13	df51e3310ef609e908a6b487a28ac068	10.241.224.0
1	df51e3310ef609e908a6b487a28ac068	10.224.252.47
7	df51e3310ef609e908a6b487a28ac068	10.142.138.33
2	df51e3310ef609e908a6b487a28ac068	10.145.185.98
1	df51e3310ef609e908a6b487a28ac068	10.18.166.63
2	df51e3310ef609e908a6b487a28ac068	10.87.54.69
3	df51e3310ef609e908a6b487a28ac068	10.179.162.134
1	a8640b9698542689880b7619dbde1293	10.72.12.70
2	6e0a868563ee18fe6569796c7ce169be	10.92.48.170
1	6e0a868563ee18fe6569796c7ce169be	10.234.235.205
1	6e0a868563ee18fe6569796c7ce169be	10.10.88.64
1	6e0a868563ee18fe6569796c7ce169be	10.1.82.35
1	6c9d56535401bbd9052f499d05816ced	10.184.18.233
1	4f4f2ebc395b0fbea708acfc3930ec6d	10.193.184.162
1	4f4f2ebc395b0fbea708acfc3930ec6d	10.214.246.53
1	4f4f2ebc395b0fbea708acfc3930ec6d	10.221.108.13
6	32b0a00ffb8799a7782a19ee99a0214f	10.224.252.47
1	329c9d3163d9bf8f4bb9f0d1b846b6aa	10.179.215.73
1	329c9d3163d9bf8f4bb9f0d1b846b6aa	10.141.47.91
1	329c9d3163d9bf8f4bb9f0d1b846b6aa	10.76.228.187
3	2fa0e36b36382b74e6e6a437ad664a80	10.197.169.135
1	27f96b0496502104cf0bfc01391e1408	10.29.109.144
2	1d419d615dbe5a238bbaa569b3829a23	10.65.34.231
1	14a09a48ad23fe0ea5a180bee8cb750a	10.65.34.231
2	14a09a48ad23fe0ea5a180bee8cb750a	10.29.109.144
4	14a09a48ad23fe0ea5a180bee8cb750a	10.241.224.0
1	14a09a48ad23fe0ea5a180bee8cb750a	10.224.252.47
2	14a09a48ad23fe0ea5a180bee8cb750a	10.142.138.33
3	14a09a48ad23fe0ea5a180bee8cb750a	10.193.67.162
2	14a09a48ad23fe0ea5a180bee8cb750a	10.145.185.98
3	14a09a48ad23fe0ea5a180bee8cb750a	10.87.54.69
1	14a09a48ad23fe0ea5a180bee8cb750a	10.179.162.134
internal bugfixing

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

distinct attackers per file

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;

download_md5_hash	COUNT(DISTINCT tree.remote_host)
14a09a48ad23fe0ea5a180bee8cb750a	1
27f96b0496502104cf0bfc01391e1408	1
2fa0e36b36382b74e6e6a437ad664a80	1
329c9d3163d9bf8f4bb9f0d1b846b6aa	3
32b0a00ffb8799a7782a19ee99a0214f	1
4f4f2ebc395b0fbea708acfc3930ec6d	3
6c9d56535401bbd9052f499d05816ced	1
6e0a868563ee18fe6569796c7ce169be	4
a8640b9698542689880b7619dbde1293	1
df51e3310ef609e908a6b487a28ac068	8
multiple infections/attacking hosts offering different malware files

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;

remote_host	COUNT(DISTINCT download_md5_hash)
10.224.252.47	2

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';

download_url	offer_url	download_md5_hash
fxp://10.224.252.47/ssms.exe 	tfxp://10.224.252.47/ssms.exe 	32b0a00ffb8799a7782a19ee99a0214f
fxp://10.224.252.47/ssms.exe 	tfxp://10.224.252.47/ssms.exe 	df51e3310ef609e908a6b487a28ac068

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.
popular download locations

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 …
COUNT(*)	download_url
13	fxp:://10.241.224.0/ssms.exe
9	fxp:://10.252.215.142/ssms.exe
7	fxp:://10.224.252.47/ssms.exe
7	fxp:://10.142.138.33/ssms.exe
5	hxxp:://10.181.184.21/i/1i2.zip
3	fxp:://10.197.169.135/ssms.exe
3	fxp:://10.179.162.134/ssms.exe
3	fxp:://1:1@10.241.224.0:64459/ssms.exe
3	fxp:://1:1@10.87.54.69:42751/ssms.exe
3	hxxp:://zonetech.info/58.exe
3	hxxp:://zonetech.info/61.exe
2	fxp:://10.145.185.98/ssms.exe
2	fxp:://10.87.54.69/ssms.exe
2	fxp:://1:1@10.65.34.231:8218/ssms.exe
2	fxp:://1:1@10.193.67.162:61062/ssms.exe
1	fxp:://10.65.34.231/ssms.exe
1	fxp:://10.29.109.144/ssms.exe
1	fxp:://10.18.166.63/ssms.exe
1	fxp:://1:1@10.29.109.144:52275/ssms.exe
1	fxp:://1:1@10.29.109.144:53336/ssms.exe
1	fxp:://1:1@10.241.224.0:43153/ssms.exe
1	fxp:://1:1@10.224.252.47:38794/ssms.exe
1	fxp:://1:1@10.142.138.33:12654/ssms.exe
1	fxp:://1:1@10.142.138.33:64375/ssms.exe
1	fxp:://1:1@10.193.67.162:41851/ssms.exe
1	fxp:://1:1@10.145.185.98:10721/ssms.exe
1	fxp:://1:1@10.145.185.98:16110/ssms.exe
1	fxp:://1:1@10.179.162.134:10901/ssms.exe
1	hxxp:://gutgal.com/sh.php
1	hxxp:://privcash.cc/r2.exe
addressed dcerpc calls

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;

COUNT(*)	dcerpc_uuid	dcerpc_opnum
1002	12345778-1234-abcd-ef00-0123456789ac	62
511	3919286a-b10c-11d0-9ba8-00c04fd92ef5	9
131	000001a0-0000-0000-c000-000000000046	4
34	4d9f4ab8-7d1c-11cf-861e-0020af6e7c57	0
30	367abb81-9844-35f1-ad32-98f038001003	27
21	a0010000-0000-0000-c000-000000000046	4
12	4b324fc8-1670-01d3-1278-5a47bf6ee188	31
11	78573412-3412-cdab-ef00-0123456789ac	62
4	8d9f4e40-a03d-11ce-8f69-08003e30051b	54
1	6a281939-0cb1-d011-9ba8-00c04fd92ef5	9
1	c84f324b-7016-d301-1278-5a47bf6ee188	31
fingerprints

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

Count of operating system genre:

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

COUNT(*)	p0f_genre
4491	Windows
419	
3	Linux

Windows dominates, 10% unknown …
operating system

Split by operating system version:

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

COUNT(*)	p0f_genre	p0f_detail
1539	Windows	XP/2000 (RFC1323+, w+, tstamp-)
1457	Windows	2000 SP2+, XP SP1+ (seldom 98)
709	Windows	XP SP1+, 2000 SP3
615	Windows	2000 SP4, XP SP1+
419		
120	Windows	XP/2000 (RFC1323+, w, tstamp-)
49	Windows	2000 SP4, XP SP1+ (2)
3	Linux	2.6 (newer, 3)
2	Windows	2003 (1)

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.
ports attacked from linux hosts

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; 

COUNT(*)	local_port
3	80

So, something was accessing the webserver on port 80.
attacks are trees

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. 


==Referensi==

* http://carnivore.it/2009/11/06/dionaea_sql_logging