Query to list account wise technicians and their respective support group details
Tested in: 14610
Compatible with: Both Postgres and MSSQL DB.
Query 1:
To return technicians and respective support group details from all accounts:
SELECT ad.ORG_NAME AS "Account Name", au.FIRST_NAME AS "Technician Name", qd.QUEUENAME AS "Support Group" FROM AccountDefinition ad INNER JOIN TechnicianAccountMapping tam ON ad.ORG_ID = tam.ACCOUNTID INNER JOIN PortalTechnicians pt ON tam.TECHNICIANID = pt.USERID INNER JOIN AaaUser au ON pt.USERID = au.USER_ID LEFT JOIN Queue_Technician qt ON pt.USERID = qt.TECHNICIANID LEFT JOIN QueueDefinition qd ON qt.QUEUEID = qd.QUEUEID
Query 2:
To return technicians and respective support group details from an individual account:
SELECT ad.ORG_NAME AS "Account Name", au.FIRST_NAME AS "Technician Name", qd.QUEUENAME AS "Support Group" FROM AccountDefinition ad INNER JOIN TechnicianAccountMapping tam ON ad.ORG_ID = tam.ACCOUNTID INNER JOIN PortalTechnicians pt ON tam.TECHNICIANID = pt.USERID INNER JOIN AaaUser au ON pt.USERID = au.USER_ID LEFT JOIN Queue_Technician qt ON pt.USERID = qt.TECHNICIANID LEFT JOIN QueueDefinition qd ON qt.QUEUEID = qd.QUEUEID WHERE ad.ORG_NAME ='MY ORG Inc'
Where, "MY ORG Inc" can be replaced by the desired account name.
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 retrieve Account details along with additional attributes, postal address and additional fields data
TESTED IN BUILDS: 14700 (Postgres) QUERY 1 - Without Account Additional Fields: select ad.ORG_NAME As "Account", org.description AS "DESCRIPTION", ad.LOGIN_WEBURI AS "LOGIN WEB URL", ad.LOGIN_URI AS "LOGIN URI", ad.SUPPORT_EMAIL AS "SUPPORT EMAIL", ...Query to list support groups along with its account , site name , group status ( MSSQL & PGSQL )
Tested in build PGSQL (14300) and MSSQL (14306) select qd.queuename "Group Name",qe.email "Email",sdo.name "Site Name",adef.org_name "Account Name" from queuedefinition qd LEFT JOIN queue_email qe ON qd.queueid=qe.queueid LEFT JOIN sitedefinition ...Query to get the user details (both technicians and requesters) account-wise with User ID (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Tested in builds : 14000, 14201, 14301 1) Query to get the First Name, Last Name, Display Name, Login User ID, Login Name, Email and Phone Number of all users in the application, account-wise, with the ...Query to retrieve the requests details
Tested in: 14610, 14301 QUERY: SELECT wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESPONDEDTIME) AS "Responded Date and Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Date and Time", ...