Last reply by from technician or requester

Last reply by from technician or requester

This report gives the last Conversation of Technician and requester.  


SELECT wo.WORKORDERID "Request ID",
       max(aau.FIRST_NAME) "Requester",
       max(wo.TITLE) "Subject",
       max(ti.FIRST_NAME) "Technician",
       longtodate(max(wo.CREATEDTIME)) "Created Time",
       CASE
           WHEN max(wos.notificationstatus) = 'REQ_REPLY' THEN max(c.description)
           WHEN max(wos.notificationstatus) = 'TECH_REPLY' THEN max(n.description)
           ELSE NULL
       END "Last conversation" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN
  (SELECT con.workorderid,
          cond.description FROM conversation con
   LEFT JOIN aaauser au1 ON con.requesterid=au1.user_id
   LEFT JOIN conversationdescription cond ON con.CONVERSATIONID= cond.CONVERSATIONID) c ON c.workorderid=wo.workorderid
LEFT JOIN
  (SELECT nw.workorderid,
          nod.description FROM notify_workorder nw
   LEFT JOIN notification NO ON nw.notificationid=no.notificationid
   LEFT JOIN aaauser au2 ON no.senderid=au2.user_id
   LEFT JOIN notificationtodesc nod ON no.NOTIFICATIONID=nod.NOTIFICATIONID
   WHERE no.senderid !=1) n ON n.workorderid=wo.workorderid
WHERE std.ispending='1'
GROUP BY wo.WORKORDERID

 

Note : Login to ServiceDesk Plus, go to Reports tab > New Query Report > Copy the query to the query editor and run the report. 

          • Related Articles

          • Last work log added

            This report is used to get the latest worklog added in the request. To make any changes to a query, refer to the KB article below. https://pitstop.manageengine.com/support/manageengine/ShowHomePage.do#Solutions/dv/24000633501275 SELECT wo.WORKORDERID ...
          • Technician change from history

            This report is used to find who changed the Technician and how many technician handled the request.  To make any changes to a query, refer to the KB article below. ...
          • How to send periodic notifications to requester(s) about their pending requests and accordingly update their status.

            This post describes the use of a python script to notify requester(s) about their requests through email and move those to a specific status. Use Case: Let us assume a scenario where the technicians reply to a request and put it in a custom status ...
          • Technician

            This report is used to find the Technician complete details.  SELECT AaaUser.USER_ID, AaaUser.FIRST_NAME "FullName", AaaLogin.NAME "LoginName", AaaLogin.DOMAINNAME "Domain", AaaContactInfo.EMAILID "Email", DepartmentDefinition.DEPTNAME "Department", ...
          • Last scanned time

            SELECT resource.resourcename                       "Asset Name",         Max(net.ipaddress)                          "IP Address",         Longtodate(Max(LASTSUCCESSAUDIT.audittime)) "Last success Scan Date",  ...