Change roles

Change roles




SELECT chdt.changeid               "Change ID", 
       chdt.title                  "Title", 
       orgaaa.first_name           "Change Requester", 
       ownaaa.first_name           "Change Owner", 
       cmDef.first_name            "Change Manager", 
       stageDef.displayname        "Stage", 
       statusDef.statusdisplayname "Status", 
       implementer.NAME            "Change Implementer", 
       line.NAME                   "Change line manager", 
       rev.NAME                    "Change  Reviewer", 
       app.NAME                    "Change  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 change_stagedefinition stageDef 
              ON chdt.wfstageid = stageDef.wfstageid 
       LEFT JOIN change_statusdefinition statusDef 
              ON chdt.wfstatusid = statusDef.wfstatusid 
       LEFT JOIN aaauser cmDef 
              ON chdt.changemanagerid = cmDef.user_id 
       LEFT JOIN (SELECT role.changeid, 
                         Max(us.first_name) AS NAME 
                  FROM   changeroleusermapping ROLE 
                         LEFT JOIN aaauser us 
                                ON ROLE.userid = us.user_id 
                  WHERE  ROLE.roleid = 7 
                  GROUP  BY ROLE.changeid)implementer 
              ON chdt.changeid = implementer.changeid 
       LEFT JOIN (SELECT role.changeid, 
                         Max(us.first_name) AS NAME 
                  FROM   changeroleusermapping ROLE 
                         LEFT JOIN aaauser us 
                                ON ROLE.userid = us.user_id 
                  WHERE  ROLE.roleid = 6 
                  GROUP  BY ROLE.changeid)line 
              ON chdt.changeid = line.changeid 
       LEFT JOIN (SELECT role.changeid, 
                         Max(us.first_name) AS NAME 
                  FROM   changeroleusermapping ROLE 
                         LEFT JOIN aaauser us 
                                ON ROLE.userid = us.user_id 
                  WHERE  ROLE.roleid = 8 
                  GROUP  BY ROLE.changeid)rev 
              ON chdt.changeid = rev.changeid 
       LEFT JOIN (SELECT role.changeid, 
                         Max(us.first_name) AS NAME 
                  FROM   changeroleusermapping ROLE 
                         LEFT JOIN aaauser us 
                                ON ROLE.userid = us.user_id 
                  WHERE  ROLE.roleid = 5 
                  GROUP  BY ROLE.changeid)app 
              ON chdt.changeid = app.changeid 


          • Related Articles

          • How to automatically assign users to different roles in a change request.

            This script is applicable only for builds prior to 11138. This is a sample python script to read the value in the Change Owner field, when a Change Request is created and set him/her as the Change Implementer automatically, using Change Custom ...
          • Update Change Roles using Change Custom Function

            This is a sample Deluge script to set Line Manger field. When a Change Request is created, ChangeRequester's reporting_to user is set as the Line Manager automatically, using Change Custom Triggers. This is achieved by submitting a update request to ...
          • How to assign CAB members to a change request automatically.

            This is a sample script to add CAB Members to a Change Request through Change Custom Triggers.The CAB Members can only be added manually to the Change Request ,under the Approval Tab, after it has been created.By using this script we will be able to ...
          • Technician Roles

            This report is used to get the roles of the technicians. For version 10.5 and below SELECT au.FIRST_NAME "Technician", (ar.NAME) "Role" FROM HelpdeskCrew hdc LEFT JOIN SDUser sd ON hdc.TECHNICIANID=sd.USERID LEFT JOIN AaaLogin al ON ...
          • Change approval

            SELECT chdt.changeid                           AS "Change ID",         chdt.title                              AS "Title",         orgaaa.first_name                       AS "Change Requester",  ...