Configuration Management - report for support groups and roles associated to technicians
This report returns the support groups associated to 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 DepartmentDefinition ON UserDepartment.DEPTID=DepartmentDefinition.DEPTID
LEFT JOIN AaaUserContactInfo ON AaaUser.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
LEFT JOIN SiteDefinition ON DepartmentDefinition.SITEID=SiteDefinition.SITEID
LEFT JOIN SDOrganization ON SiteDefinition.SITEID=SDOrganization.ORG_ID
INNER JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID
INNER JOIN HelpDeskCrew ON SDUser.USERID=HelpDeskCrew.TECHNICIANID
LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID
LEFT JOIN Queue_Technician qt ON qt.TECHNICIANID=AaaUser.USER_ID
LEFT JOIN QueueDefinition qd ON qt.QUEUEID=qd.QUEUEID
WHERE (SDUser.STATUS = 'ACTIVE') order by 1
Below report returns the roles associated to the technicians.
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
Click this link to navigate to the next report.