Article ID: 3334, created on Nov 30, 2007, last review on May 10, 2014

  • Applies to:
  • H-Sphere

Resolution

To get user-specific information from the system database:

  1. Start PostgreSQL on CP server.
  2. Log into the H-Sphere system database
  3. Run below queries to get the following info:

Selecting user info:
select users.* from users, user_account where users.id=user_account.user_id and user_account.account_id= n
(n = user account id)

Selecting mail accounts info:
select m.* from mailboxes m, parent_child p where p.child_type=1002 and p.child_id=m.id and p.account_id= n

(n = user account id)

 Selecting resellers info:
select u.* from users u, resellers r, user_account a where u.id=r.admin_id and r.id=a.user_id and account_id= n
(n = user account id)

Selecting credit card info:
select c.* from users u, user_billing_infos u_b, credit_card c, user_account a
where u.id = u_b.user_id and c.id = u_b.billing_info_id and u.id=a.user_id and a.account_id= n

(n = user account id)

Selecting reseller's users credit card info:

This is possible if you don't encrypt credit card numbers in H-Sphere database. In this case run the following query:
select cc.cc_number, cc.name, cc.exp_year, cc.exp_month, cc.type from accounts as acc join credit_card as cc on (cc.id=acc.bi_id) where acc.reseller_id=RESELLER_ACCOUNT_ID;

Make sure to replace RESELLER_ACCOUNT_ID with the this reseller's account ID.

Selecting credit card types:
a) All supported credit cards: select * from cc_brands;
b) Credit cards allowed in Merchant Gateway: select * from active_merch_gateway;

 Selecting virtual FTP info:
select f.* from ftp_vuser f, parent_child p where p.child_type=2003 and p.child_id=f.id and p.account_id= n
(n = user account id)

Selecting user's home directory

To select user's home directory from the system database and connect it to account:
select * from unix_user where unix_user.id = parent_child.child_id and parent_child.account_id = n
(n = user account id)

Selecting user's website directory

Website directory always equals user's home directory and domain name.

Selecting user's hosting plan

To select the name of the plan, run the following query:
select plans.description from plans, accounts where accounts.id = n and plans.id = accounts.plan_id;
(n = user account id)

Selecting domain name

To obtain data on domain names, run the following query:
select domains.name from domains, parent_child where domains.id = parent_child.child_id and parent_child.account_id = n
(n = your id)

Selecting IP address

To retrieve data on all IPs, run the following query:
select IP from l_server_ips, parent_child where child_id = l_server_ips.r_id and parent_child.child_type = 8 and parent_child.account_id = n
(n = your id).

There can be multiple IPs per domain.

Selecting IP of the domain:

To retrieve IP for the given domain name, run the following command:
select IP from l_server_ips, domains, parent_child where domains.id = parent_child.parent_id and parent_child.child_id = l_server_ips.r_id and domains.name ='YOUR_DOMAIN_NAME'
(enter your domain name)

Selecting account ID for the domain:

To get the data about account ID of the given domain, execute the query:
select account_id from domains, parent_child where domains.id = parent_child.child_id and domains.name ='DOMAIN_NAME'

Selecting logical server of the domain:

To retrieve IP for the given domain name, run the following command:

select hostid from unix_user, parent_child where parent_child.account_id = YOUR_ACCOUNT_ID and child_id = unix_user.id

Selecting shared IP of the domain:

To get the information about shared IPs for the domain, run:

select IP from l_server_ips where where l_server_id ='HOSTID' and flag =' SHARED_IP'

Selecting user's e-mail address:

To receive data on clients contact emails, run the following query:
select * from contact_info, accounts where contact_info.id = accounts.ci_id and accounts.id = n
(n = user account id);

Selecting emails of all users but resellers':

To receive the list of all email addresses 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);

Getting user emails by their MySQL DB names

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;

Verify users:

To verify whether a user exists in H-Sphere:

select id from users where username='USER_NAME' and password='PASSWORD'
(enter user name and password)

Defining physical/logical servers:

Run the following commands to get info about physical/logical servers:
- by IP address:
select l_server_id from l_server_ips where l_server_ips = 'YOUR_IP_ADDRESS'
(enter your IP address)

- by logical server:

select p_server_id from l_server where id = 'YOUR__L_SERVER_ID'
(enter your logical server id)

- selecting info about physical server:

select * from p_server where id = 'YOUR_P_SERVER_ID_'
(enter your physical server id)

Selecting user's billing info:

To select billing info from accounts, run the following query:
select * from billing_info, accounts where billing_info.id = accounts.bi_id and accounts.id =n
(n = user account id);

Billing periods for plans:

a) All information including discounts if present:
select * from plan_value where name like '_PERIOD%' and plan_id=plan_id;
b) Discounts:
select * from plan_value where name like '%_DISC_%' and plan_id=plan_id;

Payment modes:
select billing from plans where id=plan_id;

plans.billing takes the following values:

0 - without billing;
1 - billing mode;
2 - billing with trial period.

Plan pricing details:
select * from plan_value where plan_id=plan_id;

Domain registration pricing details:
a) select * from tld_prices;
b) select * from plan_value where name like '_TLD_%';

f213b9fa8759d57bee5d547445806fe7 6311ae17c1ee52b36e68aaf4ad066387

Email subscription for changes to this article
Save as PDF