How to identify the source of a workflow email in RSA Identity Governance and Lifecycle

[#1]

Article Number

000034266

Applies To

RSA Product Set: Identity Governance and Lifecycle, Via Lifecycle and Governance, Identity Management and Governance
RSA Version/Condition: All current supported releases
 

Issue

In complex workflows with multiple different nodes sending emails, it can be difficult to backtrace specific emails to their sources.

Task

A general purpose query for this information looks like the below. This example would be run by avuser.

SELECT wi.name     AS NodeName,
  job.cr_id        AS CR_ID,
  cr.name          AS CR_NAME,
  process.name     AS Process_Name,
  emailLog.subject AS subject
FROM avuser.t_email_log emailLog
JOIN wp_work_item wi
ON emailLog.msg_id_within_msg_type = wi.acti_id
  || ':'
  || wi.acti_db
  || ':'
  || wi.wi_iteration
JOIN wp_proci job
ON job.proci_id  = wi.proci_id
AND job.proci_db = wi.proci_db
JOIN wp_proc process
ON job.proc_ref = process.proc_ref
JOIN t_av_change_requests cr
ON job.cr_id = cr.id
WHERE msg_id_within_msg_type LIKE '%:WPDS:%'
ORDER BY CR_NAME