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 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 ...
                      • 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 ...
                      • Workaround for #8610553 - Prevent conversation creation without inline images

                        Issue: Due to some exception in writing inline images stream to file, request / conversation is created without the inline images as shown below This leads to data loss and may not be noticed immediately. So, we couldn't get the relevant traces all ...