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. 


      New to ADSelfService Plus?

        Resources

            • 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 to generate HAR file

              To generate the HAR file for Chrome Open Google Chrome and go to the page where the issue is occurring. Look for the Vertical ellipsis button () and select More Tools > Developer Tools. From the panel opened, select the Network tab. Look for a round ...
            • SAML Auto Login with ADFS (in Intranet)

              Step 1: In the AD FS server, under Authentication Methods, make sure that Windows Authentication is selected. Step 2: Run the below powershell query to check if "Chrome" is present in the supported WIA agents: Get-AdfsProperties | Select ...