Email Queries, SES & Certificates: MailQuery Troubleshooting

MailQuery Troubleshooting

mailQuery Event Email - created without an email icon

An event email can be created without a corresponding Mail Queue record in the following circumstances

1. It was created from a mailQuery scheduler job
2. The recipient1to_address in the query is blank.

An email record cannot be created in this situation, because you can't re-send a mailQuery email from an Event tab.

To fix this, check where the required email address comes from and update the source record. (E.g. Client contact, Driver, etc).

AWS SES Setup

Amazon Simple Email Service (SES) is our recommended emailing method.
DomainKeys Identified Mail (DKIM) is the authentication method we use for SES.
Each Domain Name that will send emails needs 4 domain keys listed against the Domain Name.

Warning: Once your SES keys are set-up, they must remain in place.
If these keys are removed, your emails will not send and drivers and employees will receive error messages in the Driver Portal.

Set-up Steps

1. Send an email to Catch-e Support listing the domains you want to use to email from Catch-e.
2. Catch-e Support will:
3. Raise a service request task
4. Check that any existing entries in the table are listed.
5. List all the required URLs on the task and assign to the System Administrator
6. Create the 4 entries (per domain) for the listed URLs and document the domain keys (DKIM) into the task. Assign to the Analyst
7. Email the client with the domain key (DKIM) details.
8. When you receive a return email from us with the details of the domain key entries, enter or arrange for them to be added to your Domain Name records.
9. There will be 4 DKIM entries per domain.
10. Let Catch-e Support know when the DKIM entries have been added to your domain.
11. Catch-e Support will:
12. Note the confirmation on the task and task and assign to the System Administrator
13. Check and confirm that the activation has been successful (can take up to 72 hours - but typically completed within 1 hour of client DNS change) and assign to the Analyst
14. Update the relevant controls to use SES
15. Run confirmation email tests to confirm the update.
16. The set-up can finish here.
17. Before you finish, check to see if you have any processes that use the email return path to authenticate the origin of emails. E.g. SMSGlobal for email to sms services.
18. If you are using any processes like this, you will need to nominate a sub-domain to use for the return path.
19. This should be a sub-domain, and not your main domain name, so the configuration does not disrupt your organisations settings. E.g. use ses.catch-e.com.au, not catch-e.com.au
20. Advise Catch-e Support of the sub-domain name you want to use
21. Catch-e will:
22. Email you the entries that need to be added to your DNS
23. Add or forward these entries to your Domain Manager to update
24. Advise Catch-e Support when this has been done
25. Catch-e will:
26. Configure and verify the sub-domain name in AWS
27. Email confirmation of this to you.

Update Global Controls

Once the domain keys have been verified, Catch-e Support will activate the Amazon SES service by updating the controls below to use the values shown.

Run Test Emails

Catch-e Support will conduct the following tests;

1. Send a test email to yourself to confirm reception.
2. Send a test email to "bounce@simulator.amazonses.com"
3. Check that the associated record has been updated to 'bounced'
4. Send a test email to "complaint@simulator.amazonses.com"
5. Check that the associated record has been updated to 'complaint'

Useful SQL

-- All 'enrolled' domains will need to have their DNS records updated.SELECT * FROM gb_mail_sender_domains;-- Returns all recipients for the nominated mail_batch_no and their respective status_flag.SELECT * FROM gb_mail_recipientsWHERE mail_batch_no = 'nnnnnn'-- AND mail_item_no = 1-- ADN recipient_no = 1;-- Provides additional information for the nominated mail_batch_no and their respective delivery_status & delivery_status_message.SELECT * FROM gb_mail_recipient_statusesWHERE mail_batch_no = 'nnnnnn'-- AND mail_item_no = 1-- ADN recipient_no = 1;

AWS Documentation for verifying a DKIM domain identity with your DNS provider

Follow guidelines by your DNS hosting provider which is listed on table after step 5

Domain Setup

Overview

If you've ever received an email claiming to be from your bank, PayPal, or a company you do business with, but it's really from someone else, then you've seen first-hand how easy it is to create fraudulent email. Authentication helps legitimate senders prove that their email isn't a forgery, and can help receiving servers like ISPs and corporate email servers control inbound spam.

If any of our clients send emails from Catch-e where there is no SPF Record , eventually our mail servers will be treated as spammers and get blacklisted. This means any emails you send from Catch-e may randomly not get delivered.

The first step to ensure the reliability in the email service from Catch-e is by registering the domains you use when sending email and creating an SPF Record.

Creation of the SPF record on your domain is by far the best option, however if you are unable arrange this we can set-up a sender email address that uses the Catch-e domain e.g. noreply@catch-e.com.au or client@catch-e.com.au. This is not ideal for you, but it will avoid the spam problem.

The “Reply To” address will still contain your original email address. When recipients reply to the emails, they will return to your original addresses as they do now.

Warning: If your domain is not registered with Catch-e and an SPF Record has not been set up you will be unable to send email from the Catch-e application.

Refer to this diagram to see how the email process is treated by Catch-e.

Note: If the fall_back email is used to send an email, you will not be able to see this in the email or the mail queue. The original email Sender and Address will be shown.

Status Checks

Continuous checks are run in the scheduler) to ensure the SPF records for your domains are correct.

This job checks and verifies the status of SPF records of domains stored in the table if the statusflag of the record is 'enrolled' or 'failed'. All other statuses are ignored.
If the verification fails then the field is changed to 'failed' and information on why it failed is added into the errordata field.

If the verification succeeds for a 'failed' record, the statusflag is updated to 'enrolled' and data in the errordata field is cleared..

If the ) is set to 'yes' and the domain record is not 'enrolled', your emails will either not send, or use the fall_back email if you have one.

Email pop-up behaviour

When an email pop-up is shown;

1. If your Domain is enrolled, the "From" address will display your stored sender email.
2. If your Domain is not enrolled and you have a fallback email, this will display in the "From" address and a "Reply To" address that displays your stored email will also be shown.

Mail Queue behaviour for bulk emails

When bulk emails are created, they will be stored in gb_mail at 'pending' status with the original sender's address recorded.

When the emails are sent by a user, the domain status is rechecked and the emails will be either

1. Sent without change.
2. Sent with the sender_address updated to noreply@catch-e.com.au
3. Set to 'Failed' and the user alerted

Driver Portal Email behaviour

When a driver submits a claim via the Portal, the email confirmation is not mentioned on screen, so a driver will not be aware if an SPF record failure has occurred.

The event email is still created, but the email is displayed as 'Failed' in the Mail Queue.

Client Set-up

Contact your Account Manager to register your Domain with Catch-e in the table gbmailsender_domains.

Read through the SPF Setup page. Set-up an SPF Record on your Domain.

Catch-e Set-up

In order for the domain checking process to be active the ) must be set to 'yes'

Below is the structure for .

• domain_name — The registered client domain names used when mail is sent from Catch-e on behalf of the domain varchar(255)
• domainverificationhash — reserved for future use varchar(100)
• failbackaddress — used where the statusflag is either 'pending', 'pending-spf' or 'failed'. The failback address can be noreply@catch-e.com.au or [client]@catch-e.com.au, or any other option using Catch-e.com.au as the domain name varchar(100)
• created — Time stamp of when the record is created datetime
• error_data — Used for analysis by Catch-e blob
• status_flag — statuses are: blocked, pending, pending-spf, failed and enrolled enum('pending','pending-spf','enrolled','blocked','failed')

SMS Setup

What you will need

To send SMS notifications from a mailQuery scheduled job, you will need to have an account with an SMS Provider who offers a Email-to-SMS feature.
Using an Email-to-SMS feature you can send them specially formatted emails which they convert and send on as SMSs.
There are a number of providers in the market offering this service, and it could be that your organisation already has an account.

The SMS Provider

Once you've opened an account with the SMS Provider, you need to turn on the Email-to-SMS feature and choose your settings.

Here is an example taken from the settings screen of a provider called SMSGlobal.

Notice the address sendsms@catch-e.com.au in the Allowed sender addresses box. Only emails sent from this address will be accepted by SMSGlobal and converted to SMSs.

To test the service you can send an email from your mail client. You first need to add your email address to the Allowed sender addresses box, then send an email similar to this:

The fields are populated as follows:

1 The From: address must be included in the Allowed sender addresses box or equivalent for your SMS Provider.

2 The To: address must consist of the target mobile phone number in the local part (before the @), and the domain part as specified by your SMS Provider.

3 The Subject: is not required and can be left blank.

4 The Body: contains the text that will be sent in the SMS notification.

A reply to this SMS will be emailed to the From: address in this case, but an alternate address can be configured (see above settings). Features like this will vary between providers, you will need to refer to their help documentation for further details.

Setting up a mailQuery scheduled job

Once you've complete the above section you will be ready to create a mailQuery scheduled job that can send SMSs.

The remainder of this page takes you through a worked example to send driver notifications for vehicle registration fees coming up for renewal.

1) Create Contract Event Type

Start by creating a contract event code called 'REGNSMS'.

The scheduled job will be configured to create a contract event record with this event code for each SMS email sent. This record is then used to stop any further SMS notifications being sent for that vehicle for the current year.

We've used 'REGNSMS' in this example, but you can use any event code you like. You might even have an existing event code that you prefer to use.

2) Create query used by the scheduled job

Next you need to add the following query to gbqueries (also populate the required gbtemplate record):

SELECT c.contract_id AS mail_group_by,t.mail_sender_address AS sender_address,CONCAT('61', SUBSTRING(REPLACE(d.mobile, ' ', ''), 2), '@email.smsglobal.com') AS recipient_1_to_address,'' AS subject,t.mail_message AS 'message',## PlaceholdersSUBSTRING_INDEX(d.given_name, ' ', 1) AS driver_name,c.reg_no AS reg_no,DATE_FORMAT(c.reg_renewal, '%d/%m/%Y') AS reg_renewalFROM fm_contracts AS cINNER JOIN fm_drivers AS d ON d.driver_id = c.driver_idINNER JOIN gb_templates AS t ON t.sub_name ='REGNSMS'WHERE c.reg_managed_flag = 'yes'AND DATEDIFF(c.reg_renewal, CURDATE) BETWEEN 1 and 30AND d.mobile != *AND d.given_name !=*AND NOT EXISTS ( SELECT ce.contract_event_id, ce.contract_id, ce.event_date FROM fm_contract_events AS ce INNER JOIN gl_posting_classes AS pc ON pc.posting_class_id = ce.posting_class_id AND pc.posting_class_code = 'REGNSMS' WHERE ce.contract_id = c.contract_id AND DATEDIFF(CURDATE, ce.event_date) < 60 AND ce.status_flag = 'active')AND (c.suspend_date = '0000-00-00' || c.suspend_date > DATE_ADD(CURDATE, INTERVAL 6 MONTH))ORDER BY mail_group_by

The text for message can be stored in gb_templates and a join added to that table. This technique is commonly used to store all Mail / SMS messages in the one place. Refer to the notes in mailQuery.

Running this query from a database tool such as MySQL Workbench will give you output similar to this:

mail_group_by, sender_address, recipient_1_to_address, subject, message, driver, reg_no, reg_renewal113923, sendsms@catch-e.com.au, 61407111178@email.smsglobal.com, , Hi #driver_name#, this is a reminder that the registration fee for #reg_no# will be due on #reg_renewal#, Edwina, AA14FR, 17/05/2015117716, sendsms@catch-e.com.au, 61419222259@email.smsglobal.com, , Hi #driver_name#, this is a reminder that the registration fee for #reg_no# will be due on #reg_renewal#, Vincent, BB62JS, 28/05/2015117717, sendsms@catch-e.com.au, 61419333359@email.smsglobal.com, , Hi #driver_name#, this is a reminder that the registration fee for #reg_no# will be due on #reg_renewal#, Vincent, CCD80T, 28/05/2015119558, sendsms@catch-e.com.au, 61419444487@email.smsglobal.com, , Hi #driver_name#, this is a reminder that the registration fee for #reg_no# will be due on #reg_renewal#, Michel, DD9KRU, 29/05/2015124123, sendsms@catch-e.com.au, 61418555501@email.smsglobal.com, , Hi #driver_name#, this is a reminder that the registration fee for #reg_no# will be due on #reg_renewal#, Craig, EE92AV, 30/05/2015...

The purpose of each of these fields is as follows, including a cross-reference (where applicable) to the fields listed in the test email above:

At runtime the mailQuery scheduled job will replace the placeholders embedded in message with values from the placeholder fields.
So for the first data row above the actual SMS message sent to mobile 0407 111 178 would be:

Hi Edwina, this is a reminder that the registration fee for AA14FR will be due on 17/05/2015

Note: Word your text carefully as most SMS text is restricted to 160 characters. Factor in the length of any placeholders you use.

http://support.smsglobal.com/customer/en/portal/articles/630697-why-is-my-message-split-into-multiple-parts-1-2-

Also note the following logic from the WHERE clause of the query:

• An SMS email won't be created if a 'REGNSMS' contract event record has been created within the last 60 days
• The registration renewal date must be within the next 30 days
• The driver must have a non-blank name, and mobile phone number (no validation of the mobile number is performed)
• The contract can't be suspended and must have the Reg Managed checkbox checked on

You will need to adjust this query to suit your own requirments.

3) Create the scheduled job

Create a mailQuery scheduler job selecting the query created in step 2)-create-query-used-by-the-scheduled-job) above.

Set the following parameters to create the contract event records:

That's it, the job can now be activated and scheduled as required.

Additonal Notes

You might also want to create a separate exception report to identify issues such as:

• Drivers with managed registration renewals that have a empty or invalid mobile phone number
• Contracts with a registration renewal date that has expired

When viewed from the Mail Queue screen the SMSs will look something like this:

Contact your Catch-e Account Manager if you require further assistance.