Report on Conversation

Report on Conversation

This report gives an overview of all Conversation of Technician and requester. 


SELECT wo.WORKORDERID "Request ID",
       (aau.FIRST_NAME) "Requester",
       (wo.TITLE) "Subject",
       (ti.FIRST_NAME) "Technician",
       longtodate(wo.CREATEDTIME) "Created Time",
       longtodate(c.createdtime)"Conversation CreatedOn",
       c.title "Conversation title",
       longtodate(n.notificationdate)"Technician reply CreatedOn",
       c.first_name "conversation by",
       c.description "conversation Description",
       n.notificationtitle "NOtification title",
       n.description "Notification Description",
       n.first_name "Sender name" 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,
          con.title,
          con.createdtime,
          au1.first_name,
          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,
          no.notificationtitle,
          no.notificationdate,
          au2.first_name,
          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 wo.CREATEDTIME >= <from_thisweek> and wo.createdtime <= <to_thisweek>
 

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

          • Software Compliance Report

            Number of products out of compliance select swct.compliancetype "Compliance Type",count(swcd.softwareid) "Number of Products" from SWComplianceDetails swcd left join swcompliancetype swct on swcd.swcompliancetypeid=swct.swcompliancetypeid where ...
          • Report on Organizational roles

            DB Compatibility : PGSQL & MSSQL Build Compatibility : Builds above 10000 select  r.orgrolename "orgrolename", r.description "Description", au.FIRST_NAME "Name", DepartmentDefinition.DEPTNAME "Department", SDOrganization.NAME "Site"  from aaauser au ...
          • Mail the conversation to a particular user

            Use Case:  When the conversation count is exceeding a certain number, the technician might want to escalate the ticket to their higher official.  We can configure this as a request custom menu and invoke this at our ease as per requirement. ...
          • Report on Notes

            This report gives an overview of all Notes added by the Technician. This report helps to find a specific word in the Notes, specific technician name etc..  To make any changes to a query, refer to the KB article below. ...
          • Archived and Active Request in a same report

            This Report is used to get the complete list of request both active and archived request in the same report.  To make any changes to a query, refer to the KB article below. ...