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
-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.
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
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
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
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.
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.
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 firstname.lastname@example.org 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
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