User Management - report on reassignment of groups made on a request
This report helps to find the reassignment of groups made on a request.
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
Click this link to navigate to the next report.