Article ID: 119830, created on Jan 27, 2014, last review on May 8, 2014

  • Applies to:
  • H-Sphere 3.5

Symptoms

The list of the DB query to get information from the Hsphere DB.

Resolution

Selecting user info:

    select users.* from users, user_account where users.id=user_account.user_id and user_account.account_id= 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= 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= 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= USER_ACCOUNT_ID;   

Selecting reseller's users credit card info:

This is possible if you don't encrypt credit card numbers in Parallels 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;

Selecting credit card types:

  • All supported credit cards:

        select * from cc_brands;
    
  • 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= 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 = 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 = USER_ACCOUNT_ID and plans.id = accounts.plan_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 = USER_ACCOUNT_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 = USER_ACCOUNT_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';

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 = USER_ACCOUNT_ID;

Selecting emails of all users, excluding resellers' end users:

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 database 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 Parallels H-Sphere:

    select id from users where username='USER_NAME' and password='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 = 'IP_ADDRESS';
    
  • by logical server:

    select p_server_id from l_server where id = 'LOGICAL_SERVER_ID';
    
  • selecting info about physical server:

    select * from p_server where id = '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 =USER_ACCOUNT_ID;

Billing periods for plans:

All information including discounts if present:

    select * from plan_value where name like '_PERIOD%' and plan_id=plan_id;

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.

Getting plan pricing details:

    select * from plan_value where plan_id=plan_id;

Getting domain registration pricing details:

select * from tld_prices;
select * from plan_value where name like '_TLD_%';

Search Words

DB query

f51a27b0a406fdfb3fcda8033c7f914d 6311ae17c1ee52b36e68aaf4ad066387 f213b9fa8759d57bee5d547445806fe7 2e39a5e5b1423cc126cf735bac076008

Email subscription for changes to this article
Save as PDF