Query to display Accounts and Sub Accounts (8.1)
Execute the below query from the Reports Tab -> New Query (Applicable for 8.1 version)
SELECT acc.NAME AS "Account",sorg.NAME AS "Sub Account" FROM Customer cust
LEFT JOIN AaaOrganization acc ON cust.CUSTOMER_ID=acc.ORG_ID
LEFT JOIN SubAccount subacc on cust.CUSTOMER_ID=subacc.parentaccountid
LEFT JOIN AaaOrganization sorg ON subacc.subaccountid=sorg.ORG_ID
LEFT JOIN Department_Account dep_acc ON cust.CUSTOMER_ID=dep_acc.ACCOUNTID
WHERE (cust.customer_id not in (select subaccountid from SubAccount) and dep_acc.DEPARTMENTID = 1) order by acc.NAME, sorg.NAME
New to ADSelfService Plus?
Related Articles
Query to find out who created Accounts (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) 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 ...Query to find contact associated in Sub-accounts
SELECT org.NAME "Account Name",aaauser.FIRST_NAME "Contact Name",ucinfo.EMAILID "Contact Email", au1.first_name "Subaccount Contact's name", aci.emailid "Subaccount's contact's email address" FROM AaaUser aaauser LEFT JOIN AaaUserContactInfo ...Query to take the Email domain added in Account Domain field
Query to take the Domain field data - Email domain added in Account Domain Name) select accountid, count(maildomain) from emaildomainaccountmapping group by accountid; Go to Reports -> New query to execute the same.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 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 ...