Reload Status Emails using the Qlik Notification Connector

    A useful way of keeping across your reloads is to have each application send you an email as it starts and finishes a reload. This can include some basic information such as the document name, the timestamp of the activity and whether the reload completed with any errors. Below are some steps to get this functionality working using the Qlik Notification Connector (this is a free connector in the Qlik Web Connectors package).

     

    I have also attached an example application but please note this is not something you can simply download and run. The Web Connectors package stores the credentials for the mail server so you will need to review the steps below to recreate this for yourself.

     

    Please note. In the example there are two script parts; one at the start and one at the end of the script of the application that you wish to receive status alerts about. This allows emails to be generated on commencing and on completing the reload with different content in each email.

     

     

    Setting up the Web Connector

     

    1. Install the Qlik Web Connector package which can be found in the Qlik Market.

     

    2. Once installed run the Web Connector package by double clicking on the [QlikWebConnectors] application in the ‘C:\QlikWebConnectors2.2.0’ location. This is the default location and 2.2.0 is the version number so your application may be in a different location.

    qwc01.JPG

     

    3. Open the web connector page at http://localhost:5555/web (localhost may be replaced by your server name).

     

    4. Select the ‘Standard’ option and select the ‘Qlik Notification Connector’

    qwc02.JPG

     

    5. Select the ‘Send Mail’ option (there is only one at this time) and click the ‘Parameters’ button.

    qwc03.JPG

     

    6. Enter the details of the email, mail server, and message that you want to create

    (please note that you can make these dynamic later but we do need to enter these details as a first step as the Web Connector package stores the mail server details). When completed click on the ‘Save Inputs & Run Table’ button.

    qwc04.JPG

     

    7. If you have all the details correct, you should see an OK status as below. You should also receive an email after a short delay (dependent on your mail server).

    qwc05.JPG

     

    8. Click on the Qlik Sense option and copy the generated script to the clipboard using the button provided.

    9. Paste the script into your application and you can now reload and test this.

     

     

    Perhaps this is enough for you and you can create different email subject and body messages that suffice for you without any dynamic content. If so, you can run through the steps above multiple times changing the subject and body as you require and copying each individually.

     

     

    Making the content a little more dynamic

     

    If you’d like a script that is a little more generic so that it can be copied into multiple applications without change then follow these further steps below:


    10. Download and open the template attached to this post

     

    11. Open the Data Load Editor and review the ‘>> Reload Commenced Email’ script part

     

    12. You will note that there are a number of variables created. There are also some additional variables that are required to url encode the text strings which should not be edited.

     

    // Identify the recipients of the alert emails. Multiple recipients should be separated by a semicolon ;

    Let vAlertRecipients = 'test@yourcompany.com;testanother@yourcompany.com';

     

    // Setup the sent from email account - This can be a dummy account if required.

    Let vAlertFromName = 'Qlik Alerts';

    Let vAlertFromEmail = 'noreply@yourcompany.com';

     

    /**** Manipulate these variable text strings to URL Encode the text ****/

    /*DO NOT EDIT*/ Let vRecipients = replace(replace('$(vAlertRecipients)','@','%40'),';','%3b'); // replace @ and semicolon ; in the string

    /*DO NOT EDIT*/ Let vFromName = replace('$(vAlertFromName)',' ','+'); // replace spaces with + signs

    /*DO NOT EDIT*/ Let vFromEmail = replace('$(vAlertFromEmail)','@','%40'); // replace @ with %40

     

     

    // Enter the text that you wish to send in the RELOAD START email

    Let vTimestampSTART = timestamp(now());

    Let vAlertSubjectInputSTART = 'RELOAD COMMENCED: '&documentname()&' at '&'$(vTimestampSTART)';

    Let vAlertMessageInputSTART = '<p>Application reload: <br><br>'&documentname()&'<br><br>'&'$(vTimestampSTART)'&'</p>';

     

    /**** Manipulate these variable text strings to URL Encode the text ****/

    /*DO NOT EDIT*/ Let vSubject = replace('$(vAlertSubjectInputSTART)',' ','+');

    /*DO NOT EDIT*/ Let vMessage = replace('$(vAlertMessageInputSTART)',' ','+');



    13. Review the script for the NOTIFICATIONS table. This now has a number of variables in place of the text strings in the URL. This allows the URL to be constructed as the script runs which means that the document name and timestamp can be automatically assigned.

     

    NOTIFICATIONS:

    LOAD

        '$(vTimestampSTART)' as TimestampSTART,

        status as status_START,

        result as result_START//,

    //    filesattached as SendEmail_filesattached_START

    FROM

    [http://localhost:5555/data?connectorID=NotificationConnector&table=SendEmail&SMTPServer=mail.yourcompany.com%3a587&to=$(vRecipients)&subject=$(vSubject)&message=$(vMessage)&html=True&fromName=$(vFromName)&fromEmail=$(vFromEmail)&appID=]

    (qvx);


     

    14. You will need to apply similar changes to your copied script from step 8 above or you could use this code but please change the mail server and port reference. In the example below the mail server shows as ‘mail.yourcompany.com%3a587’ as the ‘:’ has been changed to ‘%3a’ in the URL encoding so please ensure your changes reflect this. The credentials of the mail account are stored in your web connectors repository which is why you need to recreate steps 1-9.



    15. In the example there is a second script part that would follow after your main application script ‘>> Reload Completed Email’. This provides information about the reload just prior to completion and provides the reload duration and error count (should you have errormode = 0 so that reloads can complete even when some elements of the script fail - e.g. an unresponsive source). The same logic applies as per the Reload Commenced example with variables driving the content of the message which is passed in the URL of the connection. 


    The output is the following two emails:

     

    qwc09.JPG

    qwc08.JPG



     



    Limitations found in this exercise

     

    • I would have liked to have included information about any errors. There is a function called scripterrorlist which returns the concatenated list of errors. However, this list contained many special characters that were not able to be parsed through the URL without a great deal more encoding. Perhaps someone is willing to extend it in this way but I did not have the time.
    • Formatting of the email is limited. You will note that it is HTML based so you may be able to do a lot more than I have.