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 M365 Manager Plus?
        New to M365 Manager Plus?
          New to RecoveryManager Plus?
          New to RecoveryManager Plus?
            New to Exchange Reporter Plus?
            New to Exchange Reporter Plus?
              New to SharePoint Manager Plus?
              New to SharePoint Manager Plus?
                  See all integrations
                  New to ADManager Plus?
                  See all integrations
                  New to ADManager Plus?

                    New to ADSelfService Plus?

                    Start your free trial
                    Start your free trial

                      • Related Articles

                      • Query to show roles assigned to a technician with created date, status and department name (MSSQL & PGSQL)

                        Tested in builds from PGSQL (14300) or MSSQL (14306) Query: select auser.first_name as "TECHNICIAN", max(sd.employeeid) as "Employee ID", longtodate(auser.createdtime) as "Creation Date", STRING_AGG (arole.name, ',') "Current Assigned Role", ...

                      • 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 ...

                      • Converting a requester to a technician

                        Please follow the below steps to convert a requester to a technician. Step 1 (If the requester is already added under MSP Requester): 1) Log in as an administrator. 2) Go to Admin tab>>MSP Details>>MSP Requester(Left hand column). 3) Click on the ...

                        Related Products