SecurID® Governance & Lifecycle Recipes
RSA IGL Version: 7.1.x & 7.2.x
Modules: Governance/Lifecycle
Product Area: Workflows/Emails
Time to apply: <10 minutes (dependent on number of emails and SQL complexity)
Summary:
This guide uses the SQL Nodes within RSA IGL workflows to create HTML tables that can be used within the body of emails to improve how data is displayed.
Example of HTML Table within email:
This is a very simple solution that helps to improve end user experience.
For this solution to work, 'Sanitized HTML' must be allowed for Email body Templates, Review Emails and Workflow Emails.
This is configured under Admin > System > Security
By default, all settings are set to 'No markup allowed' to prevent cross-site scripting attacks. Allowing Sanitized HTML is done so at your own risk and must be in line with your companies security standards. Further details on this can be found in the online help under 'Managing System Security'.
Background
Within RSA IGL email notifications are sent to specific end users containing details of items that require action, for example Approvals or Manual Fulfillment.
Although the out of the box email templates contain all the required details, it’s not displayed in the most user friendly format.
This solution will present the details in a user-friendly table format.
Challenge
Out of the box emails can sometimes contain a large number of items which can become confusing for the recipient. This can result in processing mistakes occurring or items being missed entirely.
Additionally, there’s no option to apply default sorting/ordering to the list in line with customer requirements. For example, order the items by the user name.
Solution
As mentioned above, for this solution to work 'Sanitized HTML' must be allowed for email bodies.
Using SQL XMLELEMENT within SQL Select nodes, HTML tables can be created to display the data in a more user-friendly way and also allow for results to be ordered/sorted.
The HTML table is created as a Variable which is then referenced in the body of the email.
Implementation
Make sure you thoroughly test any changes in lower environments before promoting to Production.
The SQL Query used to extract the details required should be written using a database query tool such as SQL Developer or SQuirrel SQL and referencing the RSA IGL Public Database Schema Reference document found under the Documentation menu of the Community.
Once happy with the query, this can be used to create the HTML table.
If you’re adding this solution to an out of the box workflow, always create a copy and add to that. Do not update the original workflow as the changes may be overwritten when patching/upgrading.
The table is created using the following query which is added to a SQL Select node:
SELECT
XMLELEMENT(
"table",
XMLATTRIBUTES(
'1' AS "border" --, 'border-collapse: collapse;' AS "style"
), XMLELEMENT(
"thead", XMLELEMENT(
"tr", XMLFOREST(/* Add your column headers in format: '<ColumnHeader>' as "th" */ 'Column 1' AS "th", 'Column 2' AS "th", 'Column 3' AS "th", 'Column 4' AS "th")
)
), XMLELEMENT(
"tbody", XMLAGG(XMLELEMENT(
"tr", XMLFOREST(/* Add your selected columns here in format: '<ColumnName>' as "td" */ COLUMN1 AS "td", COLUMN2 AS "td", COLUMN3 AS "td", COLUMN4 AS "td")
))
)
) act_table
FROM
(
-- Insert SQL statement here
)
As highlighted by the comments in the above, it’s then simply a case of adding the query needed to return the required results under the ‘Insert SQL statement here’ section.
The ‘Add your selected columns here’ section is used to determine which columns from the query you wish to display within the table.
The ‘Add your column headers’ section is then used to display a friendly name as the column header of the table. For example, ‘Column 1’ instead of ‘COLUMN1’.
The table is created with the variable act_table as highlighted in Yellow. Set the Variable Type on the SQL Select Node to ‘Job’ and Save the workflow.
Add a Send Email node to the workflow and join the nodes together with a transition. Within the body of the email, add the ‘act_table’ variable:
Depending on the configuration of the workflow, you may also need to disable emails being sent from the approval/activity node otherwise this could result in duplication.
Save the workflow.
That’s it!
Example
The below image shows how the data is currently displayed using the out of the box 'Task Assigned' email notification.
Using the below query will return the data in a table format making it much more user friendly. Additionally, results are sorted/ordered as part of the SQL. For example, this table shows the users full name in ascending order (ORDER BY pUSR.FIRST_NAME||' '||pUSR.LAST_NAME ASC)
SELECT
XMLELEMENT(
"table",
XMLATTRIBUTES(
'1' AS "border" --, 'border-collapse: collapse;' AS "style"
), XMLELEMENT(
"thead", XMLELEMENT(
"tr", XMLFOREST(/* Add your column headers in format: '<ColumnHeader>' as "th" */ 'Action' AS "th", 'User Full' AS "th", 'Application' AS "th", 'App-Role Name' AS "th")
)
), XMLELEMENT(
"tbody", XMLAGG(XMLELEMENT(
"tr", XMLFOREST(/* Add your selected columns here in format: '<ColumnName>' as "td" */ ACTION AS "td", USRNAME AS "td", APPNME AS "td", APRNAME AS "td")
))
)
) act_table
FROM
(
-- Insert SQL statement here
SELECT
pCRD.TYPE AS ACTION,
pUSR.FIRST_NAME||' '||pUSR.LAST_NAME as USRNAME,
pBSC.NAME as APPNME,
pCRD.VALUE_NAME as APRNAME
FROM PV_CHANGE_REQUEST_DETAIL pCRD
LEFT JOIN PV_USERS pUSR
ON pCRD.AFFECTED_USER_ID = pUSR.ID
LEFT JOIN PV_BUSINESS_SOURCE pBSC
ON pCRD.VALUE_BUSINESS_SOURCE_ID = pBSC.ID
WHERE pCRD.CHANGE_REQUEST_ID = '${access_request_requestID}'
AND TYPE = 'AddUserToAppRole'
ORDER BY pUSR.FIRST_NAME||' '||pUSR.LAST_NAME ASC
)
