Query to retrieve the Change Details

Query to retrieve the Change Details

Tested in: 14504, 14610

QUERY:

SELECT chdt.CHANGEID AS "Change ID", cmDef.FIRST_NAME AS "Change Manager", ownaaa.FIRST_NAME AS "Change Owner", approvaldef.STATUSNAME AS "Approval Status", qd.QUEUENAME AS "Group", statusDef.STATUSDISPLAYNAME AS "Status", LONGTODATE(chdt.CREATEDTIME) AS "Created Time", orgaaa.FIRST_NAME AS "Change Requester", tempDef.NAME AS "Change Template",appr.id AS "Approval_level#id",appr.name AS "Approval_level#name",au.FIRST_NAME AS "Approver"  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 ApprovalStatusDefinition approvaldef ON chdt.APPR_STATUSID=approvaldef.STATUSID LEFT JOIN QueueDefinition qd ON chdt.GROUPID=qd.QUEUEID LEFT JOIN Change_StatusDefinition statusDef ON chdt.WFSTATUSID=statusDef.WFSTATUSID LEFT JOIN ChangeTemplate tempDef ON chdt.TEMPLATEID=tempDef.TEMPLATEID LEFT JOIN AaaUser cmDef ON chdt.CHANGEMANAGERID=cmDef.USER_ID left join change_approvallevel chl on chdt.changeid = chl.entityid left join approvallevel appr on appr.id = chl.levelid left join ApprovalDetails ad on ad.approval_level_id = appr.id left join sduser sd on ad.approverid = sd.userid left join aaauser au on sd.userid = au.user_id WHERE (( chdt.DELETEDTIME IS NULL ) )

The above query returns the following columns,



In which the Approval Level ID, Approval Level Name, and the Approver Name cannot be retrieved using a custom report. 

                    New to ADSelfService Plus?

                      • Related Articles

                      • Query to retrieve the requests details

                        Tested in: 14610, 14301 QUERY: SELECT wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESPONDEDTIME) AS "Responded Date and Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Date and Time", ...
                      • Query to retrieve Account details along with additional attributes, postal address and additional fields data

                        TESTED IN BUILDS: 14700 (Postgres) QUERY 1 - Without Account Additional Fields: select ad.ORG_NAME As "Account", org.description AS "DESCRIPTION", ad.LOGIN_WEBURI AS "LOGIN WEB URL", ad.LOGIN_URI AS "LOGIN URI", ad.SUPPORT_EMAIL AS "SUPPORT EMAIL", ...
                      • Query to retrieve worklog details

                        Tested in: 14610 (Postgres) Query: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", sdo.NAME AS "Site", ti.FIRST_NAME AS "Request Technician" ,au1.FIRST_NAME AS "Worklog Technician", TO_CHAR(((sum(ct.TIMESPENT))/1000 || ' ...
                      • Query to retrieve the active and expired CPH contracts details

                        Tested in: 14620, 14610 and 14306 Query 1: To return the Active CPH contracts details: select ad.org_name "Account", ad.org_name "Account", sp.serviceplanname "Service Plan", ac.CONTRACTNO "Contract No", longtodate(ac.startdate) "Contract Start ...
                      • Query to retrieve both the live and archived requests details

                        Database: Pgsql Query: SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", id.NAME AS "Impact", ud.NAME AS "Urgency", rtd.NAME AS "Request Type", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved ...