Identifying email address used when emailing Service Desk
Hi all
I already use the following report, which returns all tickets that were emailed to a particular address, within a certain time frame:
select wo.workorderid "RequestID",
wor.to_cc_bcc "Parent Request",
cr.TO_CC_BCC " In conversations",
wo.TITLE "Subject",
LONGTODATE(wo.CREATEDTIME) CREATEDTIME,
nr.TO_CC_BCC "Sent mails" from workorder wo
left join conversation c on wo.workorderid=c.workorderid
left join conversationrecipients cr on c.conversationid=cr.conversationid
left join notify_workorder nw on nw.workorderid=wo.workorderid
left join notification_recipients nr on nw.notificationid=nr.notificationid
left join workorder_recipients wor on wo.workorderid=wor.workorderid
where dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00') >= convert(varchar,'2018-01-30 00:00',21) and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00') <= convert(varchar,'2018-02-03 23:59',21) and
(cr.recipient_email='supportdesk@michaelpage.com' or nr.recipient_email='supportdesk@michaelpage.com' or wor.recipient_email='supportdesk@michaelpage.com')
However, the results only show me where the address I enter was used (eg: In the To field, in the Conversation sent, received etc)
I want to report on something different. Over the years, we've pointed several internal email addresses to forward into ServiceDesk+. I'm trying to streamline this down to just one, so I need to report on which addresses are currently being used. The report should show me for each ticket ref where Request Mode = "Email", which email address was used in either the To or CC field.
Eg:
Request ID TO CC
(Email addresses are above are not real - the point is
I don't currently know exactly which addresses forward into ServiceDesk+, so this report would help me identify this)
I'm not interested in the Conversation history, just the initial address the requester used to email us.
This is where the email address used usually appears when you view an individual ticket:
Hope this makes sense!
Database: MSSQL
Version and Build: 9.2 Build 9221