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 user_contact ON aaauser.USER_ID=user_contact.USER_ID LEFT JOIN AaaContactInfo ucinfo ON user_contact.CONTACTINFO_ID=ucinfo.CONTACTINFO_ID LEFT JOIN SDUser sduser ON aaauser.USER_ID=sduser.USERID LEFT JOIN Customer_Requester custreq ON sduser.USERID=custreq.REQUESTER_ID LEFT JOIN Customer cust ON custreq.CUSTOMER_ID=cust.CUSTOMER_ID LEFT JOIN AaaOrganization org ON cust.CUSTOMER_ID=org.ORG_ID LEFT JOIN Customer_Product cust_prd ON cust.CUSTOMER_ID=cust_prd.CUSTOMER_ID LEFT JOIN ComponentDefinition prd ON cust_prd.PRODUCT_ID=prd.COMPONENTID LEFT JOIN ComponentType prdType ON prd.COMPONENTTYPEID=prdType.COMPONENTTYPEID LEFT JOIN Department_Account dep_acc ON cust.CUSTOMER_ID=dep_acc.ACCOUNTID LEFT JOIN Customer_Fields account_udf ON dep_acc.DEPARTMENT_ACCOUNTID=account_udf.DEPARTMENT_ACCOUNTID LEFT JOIN DepartmentUser dep_user ON sduser.USERID=dep_user.USERID LEFT JOIN subaccount sac on org.org_id=sac.parentaccountid left join customer_requester cr on sac.subaccountid=cr.customer_id left join aaauser au1 on cr.requester_id=au1.user_id left join aaausercontactinfo auci on au1.user_id=auci.user_id left join aaacontactinfo aci on auci.contactinfo_id=aci.contactinfo_id WHERE (((((aaauser.DESCRIPTION LIKE '%Hosted%') AND (account_udf.UDF_CHAR1 = 'Active')) AND (prdType.COMPONENTTYPENAME = 'Hosted')) AND (prd.COMPONENTNAME = 'SMSC')) AND ((((dep_user.DEPARTMENTID = 1) AND ( dep_user.USERTYPE = 'Contact')) AND ((prd.DEPARTMENTID = 1) OR (prd.DEPARTMENTID IS NULL))) AND ((dep_acc.DEPARTMENTID = 1) OR (dep_acc.DEPARTMENTID IS NULL)))) AND (sduser.STATUS='ACTIVE') ORDER BY 1
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 roles
All requests from their account and sub account SELECT aaauser.FIRST_NAME "Contact Name",org.NAME "Account Name",ucinfo.EMAILID "Contact Email",ucinfo.LANDLINE "Contact Phone",ucinfo.MOBILE "Contact Mobile",ucinfo.FAX "Contact Fax" FROM AaaUser ...query to find the unapproved contacts -> PGSQL
Below is the query to find the unapproved contacts -> PGSQL-> can be used from version 11 SELECT aaauser.User_id AS "User ID", aaauser.first_name "First name", aaauser.last_name "Last name", hd.id "Portal ID", hd.displayname "Portal Name", ...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 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 ...