Onnowpurbo: New page: 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 fo...

2013-04-13T22:02:11Z

New page: 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 fo...

New page

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

HoneyPot: Dionaea SQL Logging - Revision history

Onnowpurbo: New page: 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 fo...