Query to show technicians associated accounts, sites and Support groups - (MSSQL & PGSQL)

Query to show technicians associated accounts, sites and Support groups - (MSSQL & PGSQL)

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 

                  New to ADSelfService Plus?