Mailgun provides an easy way to send out emails that can be integrated in your scripts and in turn helps with automated reporting. Often times we have to run some ad-hoc reports that are short-lived (hopefully) but need to be automated so that nobody has to go through the pain of running them manually. Working with large datasets of information, this becomes a requirement for me every now and then. Most of the times it is someone requesting for information that is not exposed using a web interface or API. While making the information using a web interface or API is definitely a good long-term solution, in order to be agile, I use mailgun and a simple shell script to automate the reporting.
In this blog post, we will look at a script that will
- Connect to a PostgreSQL database
- Execute a select query
- Exports the information into a CSV file
- Email the CSV file to a list of email addresses that have been specified.
You can download the script from GitHub. The script has comments that will help guide you through the configuration process. It also has an example of executing this script every Friday at 4 am using cron. If you have used bash scripts in the past, I recommend skipping rest of the article.
Following are the requirements to execute the script.
- Mailgun Account – Head over to https://www.mailgun.com
- Optional – .pgpass setup if using PostgreSQL authentication – See blog post at https://blog.sleeplessbeastie.eu/2014/03/23/how-to-non-interactively-provide-password-for-the-postgresql-interactive-terminal/ for details
The sections below describe the variables, and commands used within the script.
In this section we look at the various email related variables that can be configured in the script.
# Add a comma separate list of email addresses firstname.lastname@example.org'
The to variable allows you to configure multiple email addresses to which the email will be delivered.
# The from email address email@example.com'
The from variable is the email address from which the email will originate. Ideally, this should be something that is read by you or someone else for follow up if someone in the to field responds.
# Subject subject='My adhoc report'
subject variable allows you to define the subject of the email that is being sent.
#Any text that should be part of the email text='Please find attached the adhoc report.'
text variable can be used to add text to the body of the email. This can potentially be enhanced by adding information from the SQL query.
#The report file - This should be a friendly name # of the csv file that is generated by the SQL query reportFile='givemeafriendlyname.csv'
reportFile is the name of the file that will be sent with the email. The information from SQL query is stored in CSV format in this file and is sent as an attachment.
In this section we look at the PostgreSQl variables.
#PostgreSQL Database Server IP/Hostname pserver='192.168.1.2'
pserver variable hosts the IP address of the database server. Hostname can be used as well as long as DNS/host files are configured properly.
#PostgreSQL Database Name pdb='dbname'
pdb variable holds the name of the database.
#PostgreSQL Database Server Username puser='pgsqluser'
puser variable holds the name of the user that will connect to the PostgreSQL database. You might see an error similar to one below if the user specified is incorrect.
psql: FATAL: role "postgres1" does not exist
#SQL query to execute; replace with what makes sense to you. pquery='COPY (SELECT ts AS Timestamp, orderid AS Order, qty AS Quantity, sale AS Sale FROM orderlog LIMIT 10) TO STDOUT WITH CSV HEADER'
pquery holds the SQL statement that the script will execute. In this one, we are retrieving timestamp, orderid, quantity, and sale information from a table called orderlog and the output is being sent to standard output with the CSV headers. This allows us to build a CSV file that can be read easily using an application such as Microsoft Excel.
This section describes the mailgun variables. In order to get the variables, login to the mailgun dashboard, click on domains and the select the domain that you want to use for the script. All the information is in the “Domain Information” section.
# Mailgun API key mapikey='key-REPLACE-WITH-YOUR-OWN-KEY'
mapikey variable holds the api key. This is used for authentication and is used as part of the curl command, which is described in the next section of this blog post.
# Mailgun domain - Replace with your domain name mdomain='mailgun.example.com'
mdomain variable holds the domain name that you configured in mailgun.
# Mailgun URL murl="https://api.mailgun.net/v3/$mdomain/messages"
murl variable is computed directly. It creates a URL that curl will send a POST message to in order to send the email. This variable doesn’t needs to be changed.
PostgreSQL and curl commands
The meat of the script consists of two commands.
# Report has header as per the query. output=$(psql -U $puser -h $pserver -d $pdb -c "$pquery" > $reportFile)
Execute the psql command and store the output to the report file.
# Send mail via mailgun sendmail=$(curl -s --user api:$mapikey $murl -F from="$from" -F to="$to" -F subject="$subject" -F text="$text" -F h:Reply-To="$from" -F attachment="@$reportFile")
Execute curl command and POST to mailgun. There are three mail curl options that are being used in the script.
- -s – Makes curl silent and doesn’t shows progress or errors.
- –user – sends api as the username and $mapikey as the password for server authentication
- -F – This allows curl to emulate filling up form fields. In this script the various form filds are from, to, subject, text, Reply-To and attachment. Mailgun Documentation has details on all the fileds that can be sent using HTTP POST.
One of the easiest ways to troubleshoot bash scripts, is to run them using -x command.
bash -x psqlReport.sh
will print details on script execution and will allow you to debug issues.
Few potential enhancements that I would like to do is to write a subroutine that gets triggered if there is an error during psql or during mailgun API call. In case of an error:
- Make the script more robust. E.g.: It will still try to send an email if psql query fails.
- store the error in a log file.
Hope this helps in your quest to generate ad-hoc reports!