Technician Roles

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 sd.USERID=al.USER_ID
LEFT JOIN AaaUser au ON al.USER_ID=au.USER_ID
LEFT JOIN AaaAccount aa ON al.LOGIN_ID=aa.LOGIN_ID
LEFT JOIN AaaAuthorizedRole aar ON aa.ACCOUNT_ID=aar.ACCOUNT_ID
LEFT JOIN AaaRole ar ON aar.ROLE_ID=ar.ROLE_ID
LEFT JOIN AaaRoleToCategory ON ar.ROLE_ID=AaaRoleToCategory.ROLE_ID
WHERE (ar.NAME NOT LIKE 'SDApprovePO')
  AND (AaaRoleToCategory.ROLE_ID IS NULL)
ORDER BY 1

For version 11.0 and above



SELECT au.FIRST_NAME "Technician",(ar.NAME) "Role",al.name "Login Name",ac.emailid "Email ID", case when max(ts.status)='1' then 'True' else 'false' end "Current online Status" FROM Portaltechnicians hdc
LEFT JOIN SDUser sd ON hdc.userid=sd.USERID
LEFT JOIN AaaLogin al ON sd.USERID=al.USER_ID
LEFT JOIN AaaUser au ON al.USER_ID=au.USER_ID
LEFT JOIN AaaAccount aa ON al.LOGIN_ID=aa.LOGIN_ID
left join aaausercontactinfo auc on au.user_id=auc.user_id 
left join aaacontactinfo ac on auc.contactinfo_id=ac.contactinfo_id 
LEFT JOIN SDAuthorizedRole aar ON aa.ACCOUNT_ID=aar.ACCOUNT_ID
LEFT JOIN AaaRole ar ON aar.ROLE_ID=ar.ROLE_ID
LEFT JOIN AaaRoleToCategory ON ar.ROLE_ID=AaaRoleToCategory.ROLE_ID
left join portalusers pu on sd.userid=pu.userid 
left join technicianstatus ts on au.user_id=ts.technicianid 
WHERE (ar.NAME NOT LIKE 'SDApprovePO') AND (AaaRoleToCategory.ROLE_ID IS NULL) and sd.status='ACTIVE' and pu.status='ACTIVE' and hdc.id is not nullgroup by au.FIRST_NAME,ar.NAME,al.name,ac.emailid ORDER BY  au.FIRST_NAME

For version 11.1 and above


Select   au.FIRST_NAME  "Technicianname" , ar.NAME "Role" from PortalTechnicians pu
left join AaaUser au on pu.userid=au.USER_ID
left join AaaLogin al on au.USER_ID=al.USER_ID
left join AaaAccount aa on al.LOGIN_ID=aa.LOGIN_ID
left join sdAuthorizedRole aar on aa.ACCOUNT_ID=aar.ACCOUNT_ID
left join AaaRole ar on aar.ROLE_ID=ar.ROLE_ID LEFT JOIN AaaRoleToCategory ON ar.ROLE_ID=AaaRoleToCategory.ROLE_ID where (AaaRoleToCategory.ROLE_ID IS NULL) order by 1


      New to ADSelfService Plus?

        Resources

            • Related Articles

            • Change roles

              SELECT chdt.changeid               "Change ID",         chdt.title                  "Title",         orgaaa.first_name           "Change Requester",         ownaaa.first_name           "Change Owner",  ...
            • Technician

              This report is used to find the Technician complete details.  SELECT AaaUser.USER_ID, AaaUser.FIRST_NAME "FullName", AaaLogin.NAME "LoginName", AaaLogin.DOMAINNAME "Domain", AaaContactInfo.EMAILID "Email", DepartmentDefinition.DEPTNAME "Department", ...
            • 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 ...
            • Technician group

              This report is used to get the associated groups of the technicians. SELECT AaaUser.FIRST_NAME "FullName", (SDOrganization.NAME) "Site", (qd.queuename) "Group" FROM AaaUser LEFT JOIN UserDepartment ON AaaUser.USER_ID=UserDepartment.USERID LEFT JOIN ...
            • Request violated by technician

              This report used to find the technician who violated the request. If the request/incident already has a violation and is reassigned to another technician the new technician assumes the violation instead of the technician that the violation occurred.  ...