000033889 - How to identify RSA Archer Applications or Questionnaires that have Notifications using the One Email or Separate Emails option

Execute the following SQL queries against the Archer Instance database.

Identify Applications and Questionnaires where the Notification is using the One Email option:

SELECT mt.module_name, template_name, 'One Email'
FROM tblNotificationTemplate nt
JOIN tblLevel l ON l.level_id = nt.level_id
JOIN tblModule m ON l.module_id = m.module_id
JOIN tblModuleTranslation mt ON mt.module_id = m.module_id
WHERE email_recipient_option_id = 2
ORDER BY mt.module_name, template_name

Sample results:

Identify Applications and Questionnaires where the Notification is using the Separate Emails option:

SELECT mt.module_name, template_name, 'Separate'
FROM tblNotificationTemplate nt
JOIN tblLevel l ON l.level_id = nt.level_id
JOIN tblModule m ON l.module_id = m.module_id
JOIN tblModuleTranslation mt ON mt.module_id = m.module_id
WHERE email_recipient_option_id = 1
ORDER BY mt.module_name, template_name