Group change from history

Group change from history

This report is used to find who changed the group



SELECT wo.WORKORDERID "Request ID",
       aau.FIRST_NAME "Requester",
       dpt.DEPTNAME "Department",
       std.STATUSNAME "Request Status",
       wo.TITLE "Subject",
       rtdef.NAME "Request Type",
       sdo.NAME "Site",
       LONGTODATE(wo.CREATEDTIME) CREATEDTIME,
       aau1.FIRST_NAME PERFORMEDBY,
       LONGTODATE(woh.OPERATIONTIME) OPERATIONTIME,
       qd1.queuename "Changed From",
       qd2.queuename "Changed To" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN WorkOrderHistory woh ON wo.WORKORDERID = woh.WORKORDERID
LEFT JOIN WorkOrderHistoryDiff wohd ON woh.HISTORYID = wohd.HISTORYID
LEFT JOIN AaaUser aau1 ON aau1.USER_ID = woh.OPERATIONOWNERID
LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID
LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID
LEFT JOIN QueueDefinition qd1 ON cast(cast(wohd.prev_value AS varchar) AS int) =qd1.QUEUEID
LEFT JOIN QueueDefinition qd2 ON cast(cast(wohd.current_value AS varchar) AS int) =qd2.QUEUEID
WHERE wohd.COLUMNNAME IN ('QUEUEID')
  AND wo.CREATEDTIME >= <from_thisweek>
  AND wo.CREATEDTIME <= <to_thisweek>
ORDER BY 1,
         woh.OPERATIONTIME DESC

 

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

                    • Change Status history

                      SELECT chdt.changeid                       "Change ID",         chdt.title                          "Title",         orgaaa.first_name                   "Requested by",         ownaaa.first_name                   "Technician",  ...
                    • Change stage history

                      SELECT chdt.changeid                       "Change ID",         chdt.title                          "Title",         orgaaa.first_name                   "Requested by",         ownaaa.first_name                   "Technician",  ...
                    • Query to retrieve users login and logout history and find their current status

                      REQUIREMENT: To generate a report that contains the login and logout history of users and to determine the current user status. TESTED IN: Builds 14503 (Postgres) QUERY 1: The following query provides the login and logout history of users. SELECT ...
                    • Priority change from history

                      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 "Request ID", wo.TITLE "Subject", qd.QUEUENAME "Group", ...
                    • Query to show request history for technicians, group and status changes (MSSQL & PGSQL)

                      Last tested on 14500 and above too Technician select woa.workorderid "RequestID", ti1.FIRST_NAME "From technician", ti2.FIRST_NAME "To technician", longtodate(wti.timespent) "Time Spent" from workorder wo left join wo_assessment woa on ...