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 sdef ON sdef.siteid=qd.siteid LEFT JOIN sdorganization sdo ON sdo.org_id=sdef.siteid LEFT JOIN accountsitemapping asm ON asm.siteid=sdef.siteid LEFT JOIN accountdefinition adef ON adef.org_id=asm.accountid where qe.email like '%test@test.com%'
OR
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 sdef ON sdef.siteid=qd.siteid LEFT JOIN sdorganization sdo ON sdo.org_id=sdef.siteid LEFT JOIN accountsitemapping asm ON asm.siteid=sdef.siteid LEFT JOIN accountdefinition adef ON adef.org_id=asm.accountid where qe.email like '%test@test.com%'
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 list technician name, site, email and login name (MSSQL & PGSQL)
The below PGSQL query doesnt work in builds below PGSQL (14000) ERROR: missing FROM-clause entry for table "helpdeskcrew" Pgsql: SELECT HelpDeskCrew.TECHNICIANID "Technician ID", AaaUser.FIRST_NAME "Technician Name",AaaContactInfo.EMAILID "Email", ...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 ...Query to show request details along with technician's and requester's department (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Along with technician's department: 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 ...Query to pull Account ID and Site ID (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Go to Reports Tab >> New Query Report and run the below query. For Postgres & MS SQL select adef.org_id "Account ID", adef.org_name "account",sdef.siteid "Siteid", sdo.name "Site" from sitedefinition ...