1. Domain Information
2. Domains and IP addresses
3. Domain Users accounts and passwords
4. Client usernames/passwords
5. FTP accounts
6. ftp users(with domain)
7. Logrotate config for all domains
8. DNS records for a domain
9. DNS primary A-records for all domains
10. Statistics application per domain
11. SSL certificates installed under domains
12. SSL certificate files associated with default domain on IP
13. SSL certificate files associated with IP address
14. SSL certificate files not in use by any domain
15. Domains expiration in UNIX time
16. Domains expiration in human readable time
17. Bandwidth by service for the month(change date string accordingly)
18. Disk usage per service by domain
19. Mail Info
20. Mail accounts
21. All enabled mailboxes (local or redirect)
22. List bounces
23. List status of all mail to non-existent users:
24. All (singular) email info
25. List all Mail redirect/forwards:
26. List all Mail redirect/forwards to external domains:
27. Email Aliases
28. Email Groups
29. Email Autoresponders
30. Mailbox quota size per domain:
31. Databases
32. Show databases by domain
33. Show database users and passwords created in Plesk
34. User Accounts
35. ftp users(with domain):
36. ftp users with additional details(shell,quota):
37. database users(with domain):
38. web users:
39. subdomains usernames/passwords:
40. protected directories (htpasswd):
41. One Time Use
42. Redirect update from previous install

Domain Information
Domains and IP addresses
select domains.name,IP_Addresses.ip_address from domains,hosting,IP_Addresses where domains.id=hosting.dom_id and hosting.ip_address_id=IP_Addresses.id order by IP_Addresses.ip_address,domains.name;

Domain Users accounts and passwords
mysql psa -uadmin -p`cat /etc/psa/.psa.shadow` -e ‘select domains.name, accounts.password from domains, accounts, dom_level_usrs where domains.id=dom_level_usrs.dom_id and accounts.id=dom_level_usrs.account_id order by domains.name;’

select domains.name,sys_users.login,accounts.password from domains,sys_users,hosting,accounts where domains.id=hosting.dom_id and hosting.sys_user_id=sys_users.id and sys_users.account_id=accounts.id order by domains.name;

Domain Users accounts and passwords and email.
select domains.name,sys_users.login,accounts.password,clients.email from domains,sys_users,hosting,accounts,clients where domains.id=hosting.dom_id and hosting.sys_user_id=sys_users.id and sys_users.account_id=accounts.id and clients.id=domains.cl_id order by domains.name;

Client usernames/passwords
select clients.login, accounts.password from clients,accounts where clients.account_id=accounts.id;

FTP accounts
mysql psa -uadmin -p`cat /etc/psa/.psa.shadow` -e ‘select sys_users.home,sys_users.login,accounts.password from sys_users,accounts where sys_users.account_id=accounts.id order by home;’

ftp users(with domain)
select domains.name,sys_users.login,accounts.password from domains,sys_users,hosting,accounts where domains.id=hosting.dom_id and hosting.sys_user_id=sys_users.id and sys_users.account_id=accounts.id order by domains.name;

Logrotate config for all domains
select domains.name,log_rotation.period_type,log_rotation.period,log_rotation.max_number_of_logfiles,log_rotation.turned_on from domains,dom_param,log_rotation where domains.id=dom_param.dom_id and dom_param.param=”logrotation_id” and dom_param.val=log_rotation.id;

DNS records for a domain
select domains.name,dns_recs.host,dns_recs.type,dns_recs.val from domains,dns_recs where domains.dns_zone_id=dns_recs.dns_zone_id and domains.name=’nicktailor.com’;

DNS primary A-records for all domains
select dns_recs.host,dns_recs.type,dns_recs.val from domains,dns_recs where domains.dns_zone_id=dns_recs.dns_zone_id and dns_recs.type=’A’ and domains.name=substring_index(dns_recs.host,’.’,2) order by domains.name;

Statistics application per domain
select domains.name,hosting.webstat from domains, hosting where domains.id=hosting.dom_id;

List subdomains by domain
select subdomains.name,domains.name as domain from domains,sys_users,subdomains,accounts where domains.id=subdomains.dom_id and subdomains.sys_user_id=sys_users.id and sys_users.account_id=accounts.id;

select subdomains.name,domains.name as domain from domains,sys_users,subdomains,accounts where domains.id=subdomains.dom_id and subdomains.sys_user_id=sys_users.id and sys_users.account_id=accounts.id and domains.name = ‘test.com’;

SSL certificates installed under domains
select domains.name as domain_name,IP_Addresses.ip_address,certificates.name as cert_name,certificates.cert_file from domains,IP_Addresses,certificates,hosting where domains.cert_rep_id != “NULL” and domains.id=hosting.dom_id and hosting.ip_address_id=IP_Addresses.id and domains.cert_rep_id=certificates.id;

SSL certificate files associated with default domain on IP
select domains.name as domain,IP_Addresses.ip_address,certificates.name,certificates.cert_file from domains,certificates,IP_Addresses where IP_Addresses.ssl_certificate_id=certificates.id and IP_Addresses.default_domain_id=domains.id order by domains.name;

SSL certificate files associated with IP address
select IP_Addresses.ip_address,certificates.cert_file from certificates,IP_Addresses where IP_Addresses.ssl_certificate_id=certificates.id;

SSL certificate files not in use by any domain
select IP_Addresses.ip_address,certificates.name,certificates.cert_file from certificates,IP_Addresses where IP_Addresses.ssl_certificate_id=certificates.id and IP_Addresses.default_domain_id < 1 and certificates.name not like “%default%”;

Domains expiration in UNIX time
select domains.name, Limits.limit_name, Limits.value from domains, Limits where domains.limits_id=Limits.id and Limits.limit_name=”expiration” and Limits.value != -1;

Domains expiration in human readable time
mysql psa -uadmin -p`cat /etc/psa/.psa.shadow` -e ‘select domains.name, Limits.limit_name, from_unixtime(Limits.value) from domains, Limits where domains.limits_id=Limits.id and Limits.limit_name=”expiration” and Limits.value != -1;’

Bandwidth by service for the month(change date string accordingly)
select domains.name as domain, SUM(DomainsTraffic.http_out)/1024/1024 as HTTP_out_MB, SUM(DomainsTraffic.ftp_out)/1024/1024 as FTP_out_MB, SUM(DomainsTraffic.smtp_out)/1024/1024 as SMTP_out_MB, SUM(DomainsTraffic.pop3_imap_out)/1024/1024 as POP_IMAP_out_MB from domains,DomainsTraffic where domains.id=DomainsTraffic.dom_id and date like “2009-10%” group by domain;

Disk usage per service by domain
select domains.name,disk_usage.*,httpdocs+httpsdocs+subdomains+web_users+anonftp+logs+dbases+mailboxes+webapps+maillists+domaindumps+configs+chroot as total from domains,disk_usage where domains.id=disk_usage.dom_id order by total;

Mail Info
Mail accounts
mysql psa -uadmin -p`cat /etc/psa/.psa.shadow` -e ‘select concat(mail.mail_name,”@”,domains.name) as address,accounts.password from mail,domains,accounts where mail.dom_id=domains.id and mail.account_id=accounts.id order by address;’

mysql> select pname,email from clients;(list all clients name and emails)

All enabled mailboxes (local or redirect)
SELECT mail.mail_name,domains.name,accounts.password,mail.postbox, mail.redirect, mail.redir_addr FROM mail,domains,accounts WHERE mail.dom_id=domains.id AND mail.account_id=accounts.id and (mail.postbox=’true’ or mail.redirect=’true’) ORDER BY domains.name,mail.mail_name;

List bounces If checking for backscatter, be sure to check for autoresponders too. 
select domains.name from domains,Parameters,DomainServices where DomainServices.type=’mail’ and Parameters.value = ‘bounce’ and domains.id = DomainServices.dom_id and DomainServices.parameters_id=Parameters.id order by domains.name;

List status of all mail to non-existent users:
select domains.name,Parameters.value from domains,Parameters,DomainServices where DomainServices.type=’mail’ and Parameters.value in (‘catch’,’reject’,’bounce’) and domains.id=DomainServices.dom_id and DomainServices.parameters_id=Parameters.id order by Parameters.value,domains.name;

All (singular) email info
SELECT mail.mail_name,domains.name,accounts.password,mail.redir_addr FROM mail,domains,accounts WHERE mail.dom_id=domains.id AND mail.account_id=accounts.id ORDER BY domains.name,mail.mail_name;

List all Mail redirect/forwards:
SELECT mail.mail_name,domains.name,mail.redir_addr FROM mail,domains WHERE mail.redirect=’true’ AND mail.dom_id=domains.id AND mail.redir_addr!=” ORDER BY mail.mail_name;

List all Mail redirect/forwards to external domains:
SELECT mail.mail_name,domains.name,mail.redir_addr FROM mail,domains WHERE mail.redirect=’true’ AND mail.dom_id=domains.id AND mail.redir_addr!=” AND SUBSTRING_INDEX(mail.redir_addr,’@’,-1) NOT IN (SELECT name from domains) ORDER BY domains.name,mail.mail_name;

Email Aliases
select mail.mail_name, domains.name, mail_aliases.alias from mail, domains, mail_aliases where mail.dom_id=domains.id and mail.id=mail_aliases.mn_id;

Email Groups
select mail.mail_name as group_mailbox,domains.name,mail_redir.address as group_member from mail,domains,mail_redir where mail.dom_id=domains.id and mail.id=mail_redir.mn_id and mail.mail_group=’true’ order by domains.name,mail.mail_name,mail_redir.address;

Email Autoresponders
select mail.mail_name, domains.name as domain, mail_resp.resp_name, mail_resp.resp_on, mail_resp.key_where as filter, mail_resp.subject, mail_resp.reply_to from mail,domains,mail_resp where mail.dom_id=domains.id and mail.id=mail_resp.mn_id and mail.autoresponder=’true’ and mail_resp.resp_on=’true’;

Mailbox quota size per domain:
select domains.name,Limits.limit_name,Limits.value/1024/1024 as “quota MB” from domains,Limits where Limits.limit_name=’mbox_quota’ and domains.limits_id=Limits.id;
Databases

Show databases by domain
select domains.name as Domain, data_bases.name as DB from domains, data_bases where data_bases.dom_id=domains.id order by domains.name;

Show database users and passwords created in Plesk
select name,login,password from psa.db_users, psa.accounts, psa.data_bases where psa.db_users.account_id=psa.accounts.id and psa.data_bases.id=psa.db_users.db_id;

User Accounts
ftp users(with domain):
select domains.name,sys_users.login,accounts.password from domains,sys_users,hosting,accounts where domains.id=hosting.dom_id and hosting.sys_user_id=sys_users.id and sys_users.account_id=accounts.id order by domains.name;

ftp users with additional details(shell,quota):
select domains.name,sys_users.login,accounts.password,sys_users.shell,sys_users.quota from domains,sys_users,hosting,accounts where domains.id=hosting.dom_id and hosting.sys_user_id=sys_users.id and sys_users.account_id=accounts.id order by domains.name;

database users(with domain):
select domains.name as domain_name, data_bases.name as DB_name,db_users.login,password from db_users, accounts, data_bases,domains where domains.id=data_bases.dom_id and db_users.account_id=accounts.id and data_bases.id=db_users.db_id order by domains.name;

web users:
select domains.name, sys_users.login, web_users.sys_user_id from domains,sys_users,web_users where domains.id=web_users.dom_id and web_users.sys_user_id=sys_users.id;

subdomains usernames/passwords:
select subdomains.name,domains.name as domain, sys_users.login, accounts.password from domains,sys_users,subdomains,accounts where domains.id=subdomains.dom_id and subdomains.sys_user_id=sys_users.id and sys_users.account_id=accounts.id;

protected directories (htpasswd):
select domains.name, protected_dirs.path, pd_users.login, accounts.password from domains, protected_dirs, pd_users, accounts where domains.id=protected_dirs.dom_id and protected_dirs.id=pd_users.pd_id and pd_users.account_id=accounts.id;

One Time Use
Redirect update from previous install This was for an instance where redirects were brought over from a previous installation, but the previous migration failed to check if the redirects were active or not. This compares the two, and only updates the differences. 

UPDATE mail SET redirect=’false’ WHERE id IN (SELECT mail_copy.id FROM mail_copy,domains WHERE mail_copy.redirect=’true’ AND mail_copy.dom_id=domains.id AND mail_copy.redir_addr!=” and CONCAT(mail_copy.mail_name,’@’,domains.name) IN (SELECT CONCAT(mail.mail_name,’@’,domains.name) AS address FROM psa_orig.mail,psa_orig.domains WHERE mail.redirect=’false’ AND mail.dom_id=domains.id AND mail.redir_addr!=”));