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

          • 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 ...