Query to list the Change Requests that were caused by Incidents (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
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",wo.TITLE AS "Subject",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
New to ADSelfService Plus?
Related Articles
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 ...Query to show Problems, its associated incidents and change_ (MSSQL)
Tested in Build MSSQL (14306) SELECT woproblem.PROBLEMID AS "Problem ID", woproblem.TITLE AS "Problem Title", "priodef"."PRIORITYNAME" AS "Problem Priority", "urgdef"."NAME" AS "Problem Urgency", "statdef"."STATUSNAME" AS "Problem Status", ...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", ...Query to list Incidents caused by Change Request (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) 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 ...Query to show shared requests (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) Shared to Tech: SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", LONGTODATE(wo.CREATEDTIME) AS "Created Time", ...