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 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 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 get the login failed attempt details (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports tab > New Query Report, use the below query to get the login failed attempts details, select principal "User Name", LONGTODATE(timestamp), HOSTNAME, OPERATIONNAME from AuditRecord ar LEFT ...