Query to show technicians and associated groups_PGSQL

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 SDUser.USERID=HelpDeskCrew.TECHNICIANID 
LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID 
LEFT JOIN Queue_Technician qt on qt.TECHNICIANID=AaaUser.USER_ID 
LEFT JOIN QueueDefinition qd ON qt.QUEUEID=qd.QUEUEID 
LEFT JOIN SDOrganization sdo ON qd.SITEID=sdo.ORG_ID
WHERE (SDUser.STATUS = 'ACTIVE') group by AaaUser.FIRST_NAME,SDO.NAME 
order by 1


PGSQL
          • 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 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 Average response time for Technicians

            PGSQL: SELECT aau.first_name "Technician",        TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg response Time" FROM workorder wo LEFT JOIN workorderstates wos ON wo.workorderid = wos.workorderid ...
          • Query to show Average resolution time for Technicians

            PGSQL: SELECT aau.first_name "Technician",        TO_CHAR(((avg(wo.resolvedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg resolution Time" FROM workorder wo LEFT JOIN workorderstates wos ON wo.workorderid = ...
          • Query to show ticket aging - PGSQL

            PGSQL: select wo.workorderid as "RequestID", modedefinition.modename "Request Mode", queuedefinition.queuename "Group", cd.CategoryName as "Category", scd.name as "SubCategory", itemdefinition.name "Item",  cri.FIRST_NAME AS "Created By", rtd.name ...