This Report is used to find the request, problem and change association with the status of the association.
To make any changes to a query, refer to the KB article below.
Incidents caused by Change.
SELECT chdt.CHANGEID "Change ID",
chdt.TITLE "Change Title",
longtodate(chdt.CREATEDTIME) "Change Created Time",
longtodate(chdt.COMPLETEDTIME) "Change Completed Time",
orgaaa.FIRST_NAME "Change Requested by",
ownaaa.FIRST_NAME "Change Technician",
stagedef.NAME "Change Status",
wo.WORKORDERID "Request ID",
aau.FIRST_NAME "Requester",
qd.QUEUENAME "Group",
ti.FIRST_NAME "Request Technician",
longtodate(wo.CREATEDTIME) "Request Created Time",
longtodate(wo.COMPLETEDTIME) "Request Completed Time",
std.STATUSNAME "Request Status" FROM ChangeDetails chdt
LEFT JOIN SDUser orgsd ON chdt.INITIATORID=orgsd.USERID
LEFT JOIN AaaUser orgaaa ON orgsd.USERID=orgaaa.USER_ID
LEFT JOIN SDUser ownsd ON chdt.TECHNICIANID=ownsd.USERID
LEFT JOIN AaaUser ownaaa ON ownsd.USERID=ownaaa.USER_ID
LEFT JOIN StageDefinition stagedef ON chdt.STAGEID=stagedef.STAGEID
INNER JOIN IncidentCausedByChange icm ON chdt.CHANGEID=icm.CHANGEID
LEFT JOIN WorkOrder wo ON icm.WORKORDERID=wo.WORKORDERID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
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
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
Changes that were caused by Incidents.
SELECT chdt.CHANGEID "Change ID",
chdt.TITLE "Change Title",
longtodate(chdt.CREATEDTIME) "Change Created Time",
longtodate(chdt.COMPLETEDTIME) "Change Completed Time",
orgaaa.FIRST_NAME "Change Requested by",
ownaaa.FIRST_NAME "Change Technician",
stagedef.NAME "Change Status",
wo.WORKORDERID "Request ID",
aau.FIRST_NAME "Requester",
qd.QUEUENAME "Group",
ti.FIRST_NAME "Request Technician",
longtodate(wo.CREATEDTIME) "Request Created Time",
longtodate(wo.COMPLETEDTIME) "Request Completed Time",
std.STATUSNAME "Request Status" FROM ChangeDetails chdt
LEFT JOIN SDUser orgsd ON chdt.INITIATORID=orgsd.USERID
LEFT JOIN AaaUser orgaaa ON orgsd.USERID=orgaaa.USER_ID
LEFT JOIN SDUser ownsd ON chdt.TECHNICIANID=ownsd.USERID
LEFT JOIN AaaUser ownaaa ON ownsd.USERID=ownaaa.USER_ID
LEFT JOIN StageDefinition stagedef ON chdt.STAGEID=stagedef.STAGEID
INNER JOIN IncidentToChangeMapping icm ON chdt.CHANGEID=icm.CHANGEID
LEFT JOIN WorkOrder wo ON icm.WORKORDERID=wo.WORKORDERID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
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
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
Note : Login to ServiceDesk Plus, go to Reports tab > New Query Report > Copy the query to the query editor and run the report.