Tested in Build PGSQL (14300) or MSSQL (14306)
1.Technicians and associated Accounts/Sites:
select aau.User_id, aau.first_name "First Name", sdu.lastname "Last Name", aal.name "Login Name", AaaContactInfo.EMAILID "Email Address",sdu.jobtitle "Job Title", sdo.name "Associated Sites",adef.org_name "Associated Accounts" from aaauser aau INNER JOIN aaalogin aal on aau.user_id=aal.user_id
LEFT JOIN sduser sdu on sdu.userid=aau.user_id
LEFT JOIN usersitemapping usm on usm.userid=sdu.userid
INNER JOIN sitedefinition sdef on sdef.siteid=usm.siteid
INNER JOIN accountsitemapping asm on asm.siteid=sdef.siteid
INNER JOIN accountdefinition adef on adef.org_id=asm.accountid
INNER JOIN sdorganization sdo on sdo.org_id=sdef.siteid
LEFT JOIN AaaUserContactInfo ON aau.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
where aau.user_id in (select technicianid from helpdeskcrew) order by 2,6
2.Technicians and associated Support Groups:
SELECT AaaUser.FIRST_NAME "Name", AaaContactInfo.EMAILID "Email", AaaLogin.name "Login Name",sduser.jobtitle "Job Title",
(SELECT STUFF((SELECT sdo.NAME + ', ', qd.QUEUENAME +char(10) FROM QueueDefinition qd LEFT JOIN Queue_technician qt ON qd.QUEUEID=qt.QUEUEID LEFT JOIN AaaLogin al ON qt.TECHNICIANID=al.USER_ID LEFT JOIN SDOrganization sdo ON qd.SITEID=sdo.ORG_ID WHERE al.LOGIN_ID=AaaLogin.LOGIN_ID FOR XML PATH ('')), 1, 0, '')) 'Site/Group'
FROM AaaUser INNER JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID INNER JOIN HelpDeskCrew ON SDUser.USERID=HelpDeskCrew.TECHNICIANID LEFT JOIN AaaUserContactInfo ON AaaUser.USER_ID=AaaUserContactInfo.USER_ID LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID
WHERE SDUser.STATUS='ACTIVE'
ORDER BY 1
From 10600 build and above
SELECT AaaUser.FIRST_NAME "Name", AaaContactInfo.EMAILID "Email", AaaLogin.name "Login Name",sduser.jobtitle "Job Title",
(SELECT STUFF((SELECT sdo.NAME + ', ', qd.QUEUENAME +char(10) FROM QueueDefinition qd
LEFT JOIN Queue_technician qt ON qd.QUEUEID=qt.QUEUEID
LEFT JOIN AaaLogin al ON qt.TECHNICIANID=al.USER_ID
LEFT JOIN SDOrganization sdo ON qd.SITEID=sdo.ORG_ID
WHERE al.LOGIN_ID=AaaLogin.LOGIN_ID FOR XML PATH ('')), 1, 0, '')) 'Site/Group'
FROM AaaUser
INNER JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID
INNER JOIN portaltechnicians ON SDUser.USERID=portaltechnicians.userid
LEFT JOIN AaaUserContactInfo ON AaaUser.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID
WHERE SDUser.STATUS='ACTIVE'
ORDER BY 1
1 & 2 Combined
select aau.first_name "First Name",
sdu.lastname "Last Name",
aal.name "Login Name",
AaaContactInfo.EMAILID "Email Address",
sdu.jobtitle "Job Title",
qdef.queuename "Associated Groups",
sdo.name "Associated Sites",
adef.org_name "Associated Accounts" from aaauser aau
INNER JOIN aaalogin aal on aau.user_id=aal.user_id
LEFT JOIN sduser sdu on sdu.userid=aau.user_id
LEFT JOIN usersitemapping usm on usm.userid=sdu.userid
INNER JOIN sitedefinition sdef on sdef.siteid=usm.siteid
LEFT JOIN QueueDefinition qdef on qdef.SITEID=sdef.SITEID
INNER JOIN accountsitemapping asm on asm.siteid=sdef.siteid
INNER JOIN accountdefinition adef on adef.org_id=asm.accountid
INNER JOIN sdorganization sdo on sdo.org_id=sdef.siteid
LEFT JOIN AaaUserContactInfo ON aau.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
where aau.user_id in (select technicianid from helpdeskcrew) order by 2,6