Query to show technicians associated accounts, sites and Support groups - MSSQL

Query to show technicians associated accounts, sites and Support groups - MSSQL

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 ADManager Plus?

          New to ADSelfService Plus?

          Start your free trial

            • Related Articles

            • Query to find technicians with login enabled along with sites and account associated. MSSQL & PGSQL

              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 Sites",adef.org_name "Associated Accounts" from aaauser aau  INNER ...

            • Query to show technicians and associated groups_PGSQL

              SELECT AaaUser.FIRST_NAME "Technician Name", (sdo.NAME) "Associated Site", array_to_string(array_agg(distinct(qd.queuename)), ',') "Associated Group" FROM AaaUser  left JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID  inner JOIN HelpDeskCrew ON ...

            • Query to show Problems, its associated incidents and change_ MSSQL

              SELECT woproblem.PROBLEMID AS "Problem ID", woproblem.TITLE AS "Problem Title", "priodef"."PRIORITYNAME" AS "Problem Priority", "urgdef"."NAME" AS "Problem Urgency",  "statdef"."STATUSNAME" AS "Problem Status", "impactdef"."NAME" AS "Problem Impact", ...

            • Query to show support groups and its individual custom attributes

              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 Value"  FROM BaseElement baseci LEFT JOIN CI ci ...

            • Support Groups configuration

              1) The support groups that we are configuring in a template is always based on the sites. When we have more than one account assigned in the template, only the support groups added under “Default Settings” will get displayed in the template. Refer ...

              Related Products