Request change association

Request change association

This Report is used to find the request, problem and change association with the status of the association.


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. 


                  New to ADSelfService Plus?

                    • Related Articles

                    • Request Problem association.

                      This Report is used to find the request, problem association. 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 ...
                    • How to create a new change request automatically from a request.

                      This script is applicable only for builds prior to 11138. This is a sample python script that triggers the creation of a Change Request in ServiceDesk Plus using the Change API.The request ID of the parent request is appended  to the subject line of ...
                    • How to automatically create a project and associate it to a change using change custom triggers.

                      This script is applicable only for builds prior to 11138. This post describes the use of a sample python script you to create a Project automatically from a Change request and create a association between them.This script can be configured under ...
                    • Close the request upon adding a note.

                      Kindly follow the steps mentioned below: 1) Go to Admin >> Developer Space >> Custom Function >> Global Functions >> Copy & Paste the attached GF_Content.txt and save it with a name. Update the URL and technician key as per your instance. Refer to ...
                    • Query to get all the requests associated to a change (MSSQL & PGSQL)

                      Tested in builds PGSQL (14300) or MSSQL (14306) Postgres Query: select chd.ChangeID "Change ID", chd.title "Change name", wo.workorderid "Request ID", wo.title "Request Subject" from incidentcausedbychange incach left join ChangeDetails chd on ...