Archive

Archive for May, 2009

submissions2mysql.py

Utility script in a similar vein to submissions2csv.py, the script reads Nepenthes’ logged_submissions file from stdin and dumps the information into a MySQL database table.

Initially this serves the same purpose as it’s CSV counterpart, importing the date into system with powerful search and filter functionality. However this may be useful if wanting to work with the data in more complex tools as SQL databases form powerful backends and can be manipulated easily with almost programming language.

(again, apologises for formatting. I’m working on a resource repository for code and tools, hopefully available soon)

UPDATE: Code available from InfoSanity

#!/usr/bin/python
import sys
import MySQLdb

#
# Reads Nepenthes logged_submissions file and inserts data to mysql table
#

#connect to database
db = MySQLdb.connect( host="localhost", user="neplog", passwd="neplog123", db="nepenthes")

#create cursor
cursor = db.cursor()

#read from stdin
while 1:
      line = sys.stdin.readline()
      if not line:
              break

      logData = line.split(' ');

      timestamp = logData[0].strip('[]')
      date = timestamp.split('T')[0]
      time = timestamp.split('T')[1]
      sourceIP = logData[1]
      sourceMalware = logData[4]
      malwareMD5 = logData[5]

      #Insert row
      cursor.execute("insert into submissions values (\"%s\",\"%s\",\"%s\",\"%s\",\"%s\")" %( date, time, sourceIP, sourceMalware, malwareMD5) )

Database creation (I’m sure this can be improved, but it works):

CREATE TABLE `submissions` (
`logdate` date default NULL,
`logtime` time default NULL,
`ip` char(15) default NULL,
`url` varchar(64) default NULL,
`MD5` char(32) default NULL
)

Andrew Waite

Advertisements
Categories: InfoSec, Nepenthes, Python

submissions2csv.py

2009/05/02 Comments off

Whenever I’m analysing large amounts of data I prefer to start the analysis within a spreadsheet as I find the built in capabilities invaluable for some quick and dirty data diving. This typically allows for a good overall understanding of the data set and available statistics without spending time coding before the required statistics are fully understood. A prime example of this was the data I analysed from wireless connections. In this scenario the existing tools are very helpful, airodump-ng’s standard output format is csv, making importing the captured data to a spreadsheet straight forward.

Unfortunately, the same ease of data transfer is not available when working with the logs generated by Nepenthes. To aid this I’ve coded a small python script to read the logged_submissions log file and output the interesting data in .csv format. Admittedly the script is nothing special and can likely be improved on as my coding skills are a bit rusty, but this may be useful to others, or provide a starting point in similar situations.

(n.b. apologises for the rendering, I’m working on it. In meantime cut&paste is the quick and dirty way to view all code.)

UPDATE: Code downloadable from InfoSanity

#!/usr/bin/pythonimport sys

## Reads Nepenthes logged_submissions file and outputs data as comma-seperated value## Typical usage:#   cat logged_submissions | submissions2csv.py > outputfile.csv## Author: Andrew Waite (aka RoleReversal)# http://www.infosanity.co.uk#

#write 'headers to stdout'sys.stdout.write("Date,Time,Source IP Address,Malware Source,Malware MD5\n")

#read from stdinwhile 1: line = sys.stdin.readline() if not line:         break

 logData = line.split(' ');

 timestamp = logData[0].strip('[]') date = timestamp.split('T')[0] time = timestamp.split('T')[1] sourceIP = logData[1] sourceMalware = logData[4] malwareMD5 = logData[5]

 out = "%s,%s,%s,%s,%s" %(date, time, sourceIP, sourceMalware, malwareMD5) sys.stdout.write(out)

Hopefully some will find this useful. More nepenthes statistics to come.

Andrew Waite

P.S. thanks to Python.com and O’Reilly for providing good on-line references used in the coding of this tool.

Categories: Malware, Nepenthes, Python