Last work log added

Last work log added

This report is used to get the latest worklog added in the request.

SELECT wo.WORKORDERID "Request ID",
       max(aau.FIRST_NAME) "Requester",
       max(wo.TITLE) "Subject",
       max(qd.QUEUENAME) "Group",
       max(ti.FIRST_NAME) "Assigned Technician",
       max(ct.TIMESPENT)/1000/3600 "Time Spent",
       Longtodate(max(ct.TS_STARTTIME)) "Time Spent Starttime",
       max(rctd.FIRST_NAME) "Time Spent Technician",
       max(ct.DESCRIPTION) "Time Spent Description",
       max(std.STATUSNAME) "Request Status" FROM WorkOrder wo
LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID
LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID
LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
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
WHERE (wo.ISPARENT='1')
  AND ct.chargeid=
    (SELECT max(chargeid)
     FROM workordertocharge
     WHERE workorderid=wo.workorderid)
  AND wo.createdtime >= <from_thisweek>
  AND wo.createdtime <= <to_thisweek>
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 scanned time

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

            This report is used to find the resolution added by technician. 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 ...
          • Last reply by from technician or requester

            This report gives the last Conversation of Technician and requester.   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 ...
          • How Public and Private solutions work?

            ServiceDesk Plus allows you to publish knowledge base solutions as either public (available for all requesters and technicians) or private (accessible only by technicians). To make a solution public, while adding/editing the solution select the ...
          • Custom Logging for com.adventnet.servicedesk.setup package

            Add these to conf/logging.properties: Append this to the first line (to handlers):  , 13setup.com.adventnet.mfw.logging.AsyncExtendedJDKFileHandler Add the following: 13setup.com.adventnet.mfw.logging.AsyncExtendedJDKFileHandler.level = ALL ...