Reporting on emails sent by technician?
I have built a report to provide a listing of notes added to requests in the past 7 days, but have identified a gap where emails sent via the system are not recorded. How can I report on emails a given technician sends?
Incase it helps anyone else, here is the query Ive constructed to pull down the work notes:
- SELECT wo.WORKORDERID "Request ID",aaauser.first_name "Note Added By", note.NOTESTEXT "Notes", longtodate(notesdate) "Note Added date", std.STATUSNAME "Request Status", sdo.NAME "Site", qd.QUEUENAME "Group", longtodate(wo.COMPLETEDTIME) "Completed", longtodate(wos.LAST_TECH_UPDATE) "Last Update" FROM WorkOrder wo
- LEFT JOIN Notes note ON wo.WORKORDERID=note.WORKORDERID
- LEFT JOIN aaauser ON note.userid=aaauser.user_id
- LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
- LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
- LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
- LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
- LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID
- LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
- WHERE dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wos.LAST_TECH_UPDATE/1000), '1970-01-01 00:00:00') >= DATEADD(day, DATEDIFF(day, 0, GETDATE())-7, 0)
- AND dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (notesdate/1000), '1970-01-01 00:00:00') >= DATEADD(day, DATEDIFF(day, 0, GETDATE())-7, 0)
- Order By 2 DESC
New to ADSelfService Plus?