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