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%'