Find workflows/mailers with particular subject

Sometimes I get support requests quoting an email received from HR.Net and asking to modify that email text or add more recipients. And then I need need to find that workflow containing the mailer that sent out that particular message. And given thousands of workflows, it can take days to find the exact workflow (unless you know exactly where to look for)

So I wrote this little SQL query that matches email subject to a workflow name/folder:


select 
	pppf.folder_name as [Folder],
	ppf.folder_name as [Folder],
	pf.folder_name as [Folder],
	f.folder_name as [Folder],
	flow.workflowname,
	flow.description,
	flow.allowstart
 from OC_WORKFLOWMAILER mailer
  inner join OC_WORKFLOWS flow on mailer.workflowid = flow.workflowid
  left join OC_ADMINCONSOLE_FOLDERS f on flow.folderid = f.folder_id
  left join OC_ADMINCONSOLE_FOLDERS pf on f.parent_id = pf.folder_id
  left join OC_ADMINCONSOLE_FOLDERS ppf on pf.parent_id = ppf.folder_id
  left join OC_ADMINCONSOLE_FOLDERS pppf on ppf.parent_id = pppf.folder_id
where mailer.htmlsubject like '%my subject%'
This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *