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. 






                New to ADManager Plus?

                  New to ADSelfService Plus?

                    • 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 ...
                    • Unauthorized Access CSV Report for Users Not Present in the Application

                      Report Details: Currently, the application has not been recording data regarding login attempts when the username used is not available in the application. This report will store the login attempts with usernames who do not exist in the application. ...
                    • 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 ...
                    • 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. ...
                    • Fix - Add conversation only based on id in the subject

                      Issue: Recently, many customer have asking for a requirement to add conversation to request only when the request id is present in the subject of the mail in the desired format. Meaning, conversation addition shouldn't add based on mail headers like ...