Request closed by and Resolved by from history

Request closed by and Resolved by from history

This report is used to find the closed by and resolved by technician from the history. In some cases the request may be assigned to some technician and it is closed by a different technician. This information is captured in the history. 


SELECT wo.WORKORDERID "Request ID",
       wo.title "Subject",
       ti.FIRST_NAME "Technician",
       aau.FIRST_NAME "Requester",

  (SELECT max(re.first_name) FROM workorderhistory woh1
   LEFT JOIN aaauser re ON woh1.operationownerid=re.user_id
   WHERE woh1.workorderid=wo.workorderid
     AND woh1.operation='RESOLVED') "Resolved by",

  (SELECT max(ce.first_name) FROM workorderhistory woh2
   LEFT JOIN aaauser ce ON woh2.operationownerid=ce.user_id
   WHERE woh2.workorderid=wo.workorderid
     AND woh2.operation='CLOSE') "Closeby" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID
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
WHERE (wo.ISPARENT='1')
  AND wo.CREATEDTIME >= <from_lastmonth>
  AND wo.CREATEDTIME <= <to_lastmonth>
 

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

          • How to close linked requests when a parent request is closed.

            This post describes the use of a python script to close all the linked request when a parent request is closed using custom triggers. Use Case: We get tickets which are getting reported for the same issue but different end-users so that once we ...
          • Close parent request only if all the linked request are closed.

            Requirement: Parent ticket can be closed only if all the linked requests are closed Usecase: In many organizations, the parent ticket is divided into many sub-requests, each of which is handled by a different technician/group. All of these ...
          • Close all linked requests when a parent request is closed - Deluge

            Requirement: All the linked requests need to be closed when the parent request is closed. Refer to this link for Python equivalent:  https://pitstop.manageengine.com/portal/en/kb/articles/how-to-close-linked-requests-when-a-parent-request-is-closed ...
          • How to update a request based on certain values.

            This post describes the use of a python script to update requests based on values given in the request_properties.json file. This action can be performed using Custom Triggers. UseCase:  We have a Service template for account lockout/password reset. ...
          • How to close associated incident requests of change request automatically

            This post describes the use of a python script to close change that are in a specific stage using Custom Schedules. Use Case: There may be a scenario where number of Change Requests that have got closed and the associated incident is still in open ...