Report for Techinician and associated Groups,Sites ,roles

Technician Role Site Group

For mysql , you could use the query below.

select au.FIRST_NAME 'Technician', group_concat(distinct(ar.NAME)) 'Role',group_concat(distinct(SDOrganization.NAME)) 'Site' ,group_concat(distinct(qd.queuename)) 'Group' 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 usersitemapping usm on usm.userid=au.user_id left join sitedefinition sdt on sdt.siteid=usm.siteid left join SDOrganization ON sdt.SITEID=SDOrganization.ORG_ID LEFT JOIN Queue_Technician qt on qt.TECHNICIANID=au.USER_ID LEFT JOIN QueueDefinition qd ON qt.QUEUEID=qd.QUEUEID group by au.FIRST_NAME

For mssql , use the following 3 queries which are for roles ,sites and groups separately .

For the technician and associated groups :

Technician Group

select au.FIRST_NAME 'Technician', qd.QUEUENAME 'Group' from QueueDefinition qd left join Queue_Technician qt on qd.QUEUEID=qt.QUEUEID left join SdUser sd on qt.TECHNICIANID=sd.USERID left join AaaUser au on sd.USERID=au.USER_ID order by 1

For the technician and roles :

Technician Role

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 order by 1

For the technicians and sites :

Technician Site

SELECT au.FIRST_NAME 'Technician', SDOrganization.NAME 'Site' FROM HelpdeskCrew hdc LEFT JOIN SDUser sd ON hdc.TECHNICIANID=sd.USERID LEFT JOIN AaaUser au ON sd.USERID=au.USER_ID LEFT JOIN usersitemapping usm ON usm.userid=au.user_id LEFT JOIN sitedefinition sdt ON sdt.siteid=usm.siteid LEFT JOIN SDOrganization ON sdt.SITEID=SDOrganization.ORG_ID order by 1

regards,

Rachana