Archive for December, 2015

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 = ''
set imap_pass = 'PASSWORD'

set smtp_url = 'smtp://'

set smtp_pass = 'PASSWORD'
set from = ''
set realname = 'John'

set folder = 'imaps://'
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" <<< "test message"

Install sqlChart and generate pie charts

  1. Download sqlDashboards.jar
  2. Copy it to your /usr/local/bin directory
  3. Create an alias so we can run it with a shorter name
  4. Run command to generate chart

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:
database piechart

Send email with attachments

Use mutt to send the email, with -a to specify attachments like so:
mutt -s "sql pie chart reports" -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: