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" 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
New to ADSelfService Plus?
Related Articles
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 ...Query to show time elapsed in a ticket with SLA associated (PGSQL)
Tested in PGSQL build (14300) PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS "Category", std.STATUSNAME AS "Request Status", LONGTODATE(wo.CREATEDTIME) "Created Time", ...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 Contract details with assets associated (PGSQL)
Tested in PGSQL build (14300) SELECT mcdt.customcontractid "Contract ID", mcdt.CONTRACTNAME AS "Contract Name", mcdt.customcontractid "Contract ID", contractcategory.Categoryname "Contract Type", LONGTODATE(mcdt.CREATEDDATE) AS "Created Time", ...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 ...