Sending an email with pie charts and graphs
The following steps explain how to send an email with chart image attachments from the command line. First we must install the necessary email tools in linux:
Install Email Sending on Linux
apt-get install mutt
vi ~/.muttrc
That last line, opens the configuration for editing. For gmail you will have details like this:
account-hook imap://gmail/ “set
set imap_user = 'user@domain.com'
set imap_pass = 'PASSWORD'
set smtp_url = 'smtp://user@domain.com@smtp.gmail.com:587/'
set smtp_pass = 'PASSWORD'
set from = 'user@domain.com'
set realname = 'John'
set folder = 'imaps://imap.gmail.com:993'
set mbox= '+Inbox'
set spoolfile= '+Inbox'
set postponed= '+[Gmail]/Drafts'
set trash = '+[Google Mail]/Trash'
set header_cache=~/.mutt/cache/headers
set message_cachedir=~/.mutt/cache/bodies
set certificate_file=~/.mutt/certificates
set move = no
set timeout=15
auto_view text/html
To test this, first run “mutt”, it should display your gmail inbox. Then send an email using the command:
mutt -s "test" receiver@domain.com <<< "test message"
Install sqlChart and generate pie charts
- Download sqlDashboards.jar
- Copy it to your /usr/local/bin directory
- Create an alias so we can run it with a shorter name
- Run command to generate chart
wget http://www.sqldashboards.com/files/sqldashboards.jar
mv sqldashboards.jar /usr/local/bin/
alias sqlchart="java -cp /usr/local/bin/sqldashboards.jar com.timestored.sqldash.SqlChart"
sqlchart --servertype mysql --chart piechart --user USERNAME --password PASSWORD --database DATABASENAME--out pie.png --execute "select DATE(time),count(*) from qstudioreg group by DATE(time) ORDER BY DATE(time) DESC limit 9"
Voila we have our chart:
Send email with attachments
Use mutt to send the email, with -a to specify attachments like so:
mutt -s "sql pie chart reports" ryan@timestored.com -a pie.png <<< "Here is your database report"
Done. So we successfully configured mutt for sending email, then generated our pie chart from mysql using sqlchart then we emailed it as an attachment. If you have any problems please let me know and I'll try to help. Here's the email in my inbox:
Hi, I’ll like to know if this solution us scalable say for sending Up of 500 000 emails with charts from an enterprise environment.