Skip Navigation

You are here: Home > Projects > Procmail Log to SQLite Database

Procmail Log to SQLite Database

Pmlog2db will extract useful information from a Procmail log file and add it to an existing SQLite database, where it can be processed in a meriod of wonderful ways all involving SQL.

Procmail Log Files

This script processes log files from Procmail, which have entries similar to the following.
From Wed May 26 12:30:49 2004
Subject: Pharmacy with smart prices
Folder: /net/u/1/c/ccn/Mail/spam/sa 4290
This entry contains information for a single message, including the originating email address, date of receipt, subject, folder where the message was placed, and size in bytes. The script expects each series of three lines to be information for a single message, and that the log file to be processed will contain no extraneous lines.

Script Actions

The script expects one command-line argument, which is the name of a Procmail log file. It expects a SQLite database named msgs.db to be in the current directory. Provided that these conditions are met, the log file is processed.

  1. Does the file passed as a command-line argument exist?
    • If so, continue.
    • If not, exit.
  2. Does each set of three lines look like it relates to a single message?
  3. Does the msgs.db file exist in the current directory?
    • If so, continue.
    • If not, exit.
  4. Re-scan the file and, for each message, extract useful information.
    • If information looks valid, continue.
    • If not, save the lines involving that message to problems.log for later review by the user.
  5. Add the information for the individual message to the SQLite database.
  6. Print statistics on number of messages processed and number successfully added to the database.


The database is composed of only one table. The database and table can be created with a command similar to the following.
sqlite msgs.db 'create table msg(addr, mon, day, hour, min, sec, year, sub, folder, size);' The fields in table msgs have these meanings:

The email address which the message was sent from
The month in which the message was received
The day on which the message was received
the hour in which the message was received
the minute in which the message was received
the second at which the message was received
The year in which the message was received
the message subject
the folder in which Procmail deposited the message
the size of the message in bytes


This script is available as, and is made available under the BSD license.


Given the amount of data retained about each message, and the pouwer of SQL, it is possible to ask questions such as:


I hope this script is useful in some way. Feel free to send a message if you find it useful or have a comment.