System Email Reporter

This app generates reports that are sent via email. It can handle plain text or HTML email. It will handle only one message format at a time. This app runs on a Linux server.

This application is very “dumb” and does no validation on the message. All parameters need to be checked carefully. You need to understand the various email headers and their usage as well as HTML if it is being used. There are no “shortcuts” built into the application.

How to use

sysmailrep [options]

-h, --help     display online help
-d, --debug    enable debugging mode
-t, --test     enable testing mode (will not send the message)
-c, --conf     alternate configuration file to use
-f, --report   report file

To generate a report, the minimum is to use the “-f <reportfile>” option.

Configuration

The default configuration file is “/etc/min-sysmailrep.conf”.

This is an INI file consisting of:

[conf]
server=         FQDN or IP address of database server
path=           path to the database or an alias
userid=         the USERID to connect to the database
password=       the password to connect to the database
emailserver=    FQDN or IP address of email server
emailuserid=    user ID to connect to the email server
emailpassword=  password to connect to the email server
fromaddress=    email address used for FROM
fromname=       display name used for FROM

Report File

The report file is also an INI file consisting of:

[conf]
subject=
to=
cc=
bcc=
content-type=
content-transfer-encoding=
content-disposition=

The “content-type” defaults to “text/plain;charset=iso-8859-1” and content-transfer-encoding defaults to “8bit”.

To use multiple email addresses separate them with commas.

[Params]
count=   number of parameters
[SQL]

This section defines the query used against the database. The results if which form the message body. All lines in this section are read at once so just the raw SQL query is needed.

[Msg]
Title=           prints before the query
TitleSeparator=  prints after Title but before the query
DataFormat=      n/a
Fields=          number of fields in the output
Footer=          prints after the query

[BodyHeader]

The optional BodyHeader section is read at once and inserted at the top of the message body. This is where you would define the styles and any HTML headers. The “<!DOCTYPE html>” would go here as well as any text that is to show before the query data.

[BodyFooter]

The ioptional BodyFooter section is read at once and appended to the message body. This is where you would insert any security clauses, and close the HTML.

[AdditionalHeaders]

This optional section is read at once and all lines are inserted into the message headers following the standard headers (From, To, Cc, Date, Subject, X-Mailer, Content-Type, and Content-Transfer-Encoding). You could have client specific headers, priority, tracking.

The “X-Mailer” header will contain “MoveItNow Automated mailer (version)”.

[Msg_Field]
Format=  formatting for this field
Field=   field number in query
Type=    type of data to pass to format command

The report file will contain multiple “Msg_Field” sections. These sections will be numbered starting from one. The system will loop through these sections for each row returned in the database query.

The “Format” entry is fed into a format command (a.k.a. “printf”) with the parameters expected to match the Type specified.

The “Type” entry specifies how the field from the database query is passed to the format command. It has the following values: string, integer, float, date, time, timestamp.

The “Field” entry identifies the result field in the database query by number starting at zero.

If using HTML and the data is to be in a TABLE then the “Msg_Field1” section’s “Format” entry should start with “<TR><TD>” and the last “Msg_Field” section’s “Format” entry should end with “</TD></TR>”.

Example Report File

[conf]
subject=Customer List
to=someone@example.com
cc=
bcc=
content-type=text/html
content-transfer-encoding=8bit
content-disposition=

[Params]
count=0

[SQL]
select * from mstr_customer

[Msg]
Title=<h2>Customer List</h2><table border=1><tr><th>ID</th><th>Account</th><th>Name</th></tr>
TitleSeparator=
DataFormat=
Fields=3
Footer=**end of list**

[AdditionalHeaders]
[BodyHeader]
[BodyFooter]

[Msg_Field1]
Format=<TR><TD align="right">%s</TD>
Field=0
Type=string

[Msg_Field2]
Format=<TD>%s</TD>
Field=1
Type=string

[Msg_Field3]
Format=<TD>%s</TD>
Field=3
Type=string

Known Issues

Only supports basic SMTP options. Does not support changing ports, enabling SSL or TLS.

Only a single query is supported. There is no summary option (i.e. Totals at end of query). Maybe I should add a “summary” option to the [Msg] section that would be inserted after the query data but before the footer.

No automation and no error checking. This is currently handled via test and debug options. Maybe I should add a command line option for validating the report file

Comments are closed.