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:

• It was created from a mailQuery scheduler job

• The recipient_1_to_address in the query is blank

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

To fix this, check where the required email address comes from and update the source record (for example: 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:

   • Raise a service request task

   • Check that any existing entries in the gb_mail_sender_domains table are listed

   • List all the required URLs on the task and assign to the System Administrator

   • Create the 4 entries (per domain) for the listed URLs and document the domain keys (DKIM) into the task. Assign to the Analyst

   • Email the client with the domain key (DKIM) details

3. 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. There will be 4 DKIM entries per domain.

4. Let Catch-e Support know when the DKIM entries have been added to your domain.

5. Catch-e Support will:

   • Note the confirmation on the task and assign to the System Administrator

   • 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

   • Update the relevant controls to use SES

   • Run confirmation email tests to confirm the update

6. The set up can finish here.

   Before you finish, check to see if you have any processes that use the email return path to authenticate the origin of emails (for example: SMSGlobal for email to sms services).

7. If you are using any processes like this, you will need to nominate a sub-domain to use for the return path.

   This should be a sub-domain, and not your main domain name, so the configuration does not disrupt your organisations settings. For example: use ses.catch-e.com.au, not catch-e.com.au

8. Advise Catch-e Support of the sub-domain name you want to use.

9. Catch-e will:

   • Email you the entries that need to be added to your DNS

   • Add or forward these entries to your Domain Manager to update

10. Advise Catch-e Support when this has been done.

11. Catch-e will:

    • Configure and verify the sub-domain name in AWS

    • 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.

Setup / Reference Data > gb_controls

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_recipients WHERE mail_batch_no = 'nnnnnn' -- AND mail_item_no = 1 -- AND recipient_no = 1; -- Provides additional information for the nominated mail_batch_no and their respective delivery_status and delivery_status_message. SELECT * FROM gb_mail_recipient_statuses WHERE mail_batch_no = 'nnnnnn' -- AND mail_item_no = 1 -- AND recipient_no = 1; 

For more information, refer to AWS Documentation for verifying a DKIM domain identity with your DNS provider.

Domain Setup

Overview

If you have ever received an email claiming to be from your bank, PayPal, or a company you do business with, but it is really from someone else, then you have seen first hand how easy it is to create fraudulent email. Authentication helps legitimate senders prove that their email is not 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 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 to arrange this we can set up a sender email address that uses the Catch-e domain (for example: 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.

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 gb_mail_sender_domains table if the status_flag 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 error_data field.

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

If the mail_sender_enabled 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:

• If your Domain is enrolled, the "From" address will display your stored sender email

• 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:

• Sent without change

• Sent with the sender_address updated to noreply@catch-e.com.au

• 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

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

2. 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 mail_sender_enabled control must be set to 'yes'.

Below is the structure for gb_mail_sender_domains:

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 an 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 have 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:

Test Email Format

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. 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 have completed the above section you will be ready to create a mailQuery scheduled job that can send SMSs. The remainder of this section 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 have 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 gb_queries (also populate the required gb_template 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', -- Placeholders SUBSTRING_INDEX(d.given_name, ' ', 1) AS driver_name, c.reg_no AS reg_no, DATE_FORMAT(c.reg_renewal, '%d/%m/%Y') AS reg_renewal FROM fm_contracts AS c INNER JOIN fm_drivers AS d ON d.driver_id = c.driver_id INNER 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 30 AND 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' OR 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:

Field Purpose Reference

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 Jane, this is a reminder that the registration fee for XX00AA 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. Refer to http://support.smsglobal.com/customer/en/portal/articles/630697-why-is-my-message-split-into-multiple-parts-1-2-

Query Logic

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 cannot be suspended and must have the Reg Managed checkbox checked on

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

3) Create the Scheduled Job

Create a mailQuery scheduler job selecting the query created in step 2 above.

Set the following parameters to create the contract event records:

• Create Event: checked

• Event Table: Contract

• Event Type: REGNSMS

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

Additional Notes

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

• Drivers with managed registration renewals that have an 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:

(Email display format similar to regular emails with To, From, Subject, and Body fields)

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