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
(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?
Related Articles
Query to find technicians with login enabled along with sites and account associated (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Please go to Reports-New Query reports and execute this query. select aau.User_id,aau.first_name "Technician Name", aal.name "Login Name", aal.Domainname "Domain",sdo.name "Associated ...Query to show technicians and associated groups (Not working )
Doesnt work in builds from PGSQL (14300) ERROR: missing FROM-clause entry for table "helpdeskcrew" SELECT AaaUser.FIRST_NAME "Technician Name", (sdo.NAME) "Associated Site", array_to_string(array_agg(distinct(qd.queuename)), ',') "Associated Group" ...Query to list account wise technicians and their respective support group details
Tested in: 14610 Compatible with: Both Postgres and MSSQL DB. Query 1: To return technicians and respective support group details from all accounts: SELECT ad.ORG_NAME AS "Account Name", au.FIRST_NAME AS "Technician Name", qd.QUEUENAME AS "Support ...Query to List down Account and corresponding sites and its requesters (MSSQL & PGSQL)
*Tested in builds from PGSQL (14300) or MSSQL (14306) *Applicable for 14500 builds For Accounts and Site: select adef.org_name "Account",sdo.name "Site Name" from accountdefinition adef left join accountsitemapping asm on adef.org_id=asm.accountid ...Query to show support groups and its individual custom attributes (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT ci.CINAME AS "Support Group Name", su.first_name "Owned By", ci.DESCRIPTION AS "Description",ia.attributename "Additional Attributes Name",ia.attributevalue "Additional Attributes ...