This is a quick and dirty Ruby script that I threw together years ago to parse emails and jam their contents, line by line, into a MySQL database. It was originally written to handle NetGear firewall logs hence some of the nomenclature. Very simple but it did the job quite well. I’m posting it here as someone asked for an example implementation of doing this and I just found this sitting in the code repo. The key variables are blanked out, just fill them in as needed. This uses IMAP to connect to the mail server so be sure that IMAP is available.
#!/usr/bin/ruby -w
#Netgear Log IMAP Connector by Scott Alan Miller, 0.3
require 'net/imap'
require 'mysql'
# Connection Details
usessl = true
certs = nil
verify = false
port = "993"
server = ''
uname = ''
pass = ''
mbox = 'INBOX'
bbox = 'TRASH'
db_server = ""
db_user = ""
db_pass = ""
db_name = ""
subsearch = ""
# Query Constructors
queryheader = "INSERT INTO events (date_received, subject, details) VALUES ('"
querypiece = "', '"
queryfooter = "')"
system("echo [nglogger] $(date) Starting >> /var/log/nglogger.log")
imap = Net::IMAP.new(server, port, usessl, certs, verify)
imap.login(uname, pass)
#imap.examine(mbox)
imap.select(mbox)
system("echo [nglogger] $(date) Processing #{ imap.responses["EXISTS"]} Messages >> /var/log/nglogger.log")
# Process IMAP to MySQL Logging
dbsession = Mysql.real_connect(db_server, db_user, db_pass, db_name) # Create MySQL Connection
imap.search(["SINCE", "17-Jan-2008"]).each do |message_id|
envelope = imap.fetch(message_id, "ENVELOPE")[0].attr["ENVELOPE"]
msg = imap.fetch(message_id, "(UID RFC822.SIZE ENVELOPE BODY[TEXT])")[0]
msgBody = msg.attr["BODY[TEXT]"]
msgBody.chomp!();
msgBody.split(/\n/).each do |msgLine|
msgLine.chomp!();
if msgLine != "" && msgLine != "End of Log ----------" && msgLine != nil
msgLine.gsub!(/\'/, "") # Remove single quotes from SQL Insert
query = queryheader + envelope.date + querypiece + envelope.subject + querypiece + msgLine + queryfooter
dbsession.query(query)
end
end
imap.copy(message_id, bbox) # Copy to Processed Folder
imap.store(message_id, "+FLAGS", [:Deleted])
end
imap.expunge
system("echo [nglogger] $(date) Exiting After IMAP Expunge >> /var/log/nglogger.log")