Article ID: 3122, created on Nov 30, 2007, last review on Apr 17, 2012
Before getting user-related information out of system database, make sure to do the following:
1. Start PostgreSQL on CP server.
2. Log into the H-Sphere system database before running queries:
1.) Login as the cpanel user to the server where the system database is located (usually, CP server).
2.) Enter the hsphere database (usually, under the wwwuser user name):
# psql hsphere [user_name]
3. Run the query you need. For more information on it, go to http://www.psoft.net/HSdocumentation/sysadmin/understanding_cp_server_configuration.html#hs_db
a. To get user emails knowing their MySQL database names, run the following query:
select c.email from mysqldb m, parent_child pc, accounts a, contact_info c where m.db_name='DBNAME' and m.id=pc.child_id and pc.child_type=6001 and pc.account_id=a.id and a.ci_id=c.id;
b. To get the list of email addresses of active customers ignoring resellers' end users, run the following query:
select c_i.email from contact_info c_i join accounts a on (c_i.id=a.ci_id and a.reseller_id=1);
Even more information is at: http://www.psoft.net/HSdocumentation/devel/db_query.html