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

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 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
where aau.user_id in (select technicianid from helpdeskcrew) order by 2,6


ORDER BY ACCOUNT:

select adef.org_name "Associated Accounts", sdo.name "Associated Sites", aau.first_name "Technician Name" 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
where aau.user_id in (select technicianid from helpdeskcrew) order by 1

          • Related Articles

          • 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 ...
          • Query to show Technicians' login name, email, role and last login_ PGSQL & MSSQL

            SELECT AaaUser.FIRST_NAME "Technician Name", aal.name "Login Name", aci.emailid "Email ID", SDUSER.STATUS "Status(Active/Inactive)", aar.name "Role", longtodate(MAX(AaaAccSession.OPENTIME)) "Logged In Time" FROM AaaUser left JOIN SDUser ON ...
          • Query to find out who created Accounts

            Execute the below queries under  Reports->New  Query Report. select org_id, org_name from accountdefinition ad where ad.org_name in('Requester Name', 'Palanivel Palras','Muhammad Nadeem Khan') Note down the Org_IDs of the above query from the result ...
          • 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 request details along with technician's department

            PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", qd.QUEUENAME AS "Group",ti.FIRST_NAME AS "Technician", dpt.DEPTNAME AS " Technician ...