Skip to content

Instantly share code, notes, and snippets.

View siliconmeadow's full-sized avatar

Richard Sheppard siliconmeadow

36 followers · 124 following
  • Nomensa Ltd
  • New York / Paris / Peckham
View GitHub Profile
@siliconmeadow
siliconmeadow / book_wishlist.md
Created November 18, 2013 21:13
List of books I'd like
@siliconmeadow
siliconmeadow / string_replacement_files_path.sql
Created November 4, 2013 14:51
Code to do string replacement on columns in a table. Particularly useful for filename path changes in Drupal 6. Possibly D7?
UPDATE files
SET files.filepath
=
REPLACE(files.filepath,'sites/jarp.aroq.com/files','sites/default/files');
@siliconmeadow
siliconmeadow / num_mailboxes_of_client.sql
Created November 4, 2013 14:47
To find the number of mailboxes a client has on a Plesk server
SELECT COUNT( mail.mail_name )
FROM clients, domains, mail
WHERE clients.id =2
AND clients.id = domains.cl_id
AND domains.id = mail.dom_id;
@siliconmeadow
siliconmeadow / traffic_by_client_in_plesk.sql
Created November 4, 2013 14:45
To find traffic by client per month in Plesk.
SELECT SUM(DomainsTraffic.http_out), SUM(DomainsTraffic.ftp_in), SUM(DomainsTraffic.ftp_out), SUM(DomainsTraffic.smtp_in), SUM(DomainsTraffic.smtp_out), SUM(DomainsTraffic.pop3_imap_in), SUM(DomainsTraffic.pop3_imap_out)
FROM DomainsTraffic, domains, clients
WHERE DomainsTraffic.date < '2008-07-10'
AND DomainsTraffic.date > '2008-05-31'
AND clients.id =1
AND clients.id = domains.cl_id
AND domains.id = DomainsTraffic.dom_id
@siliconmeadow
siliconmeadow / mailboxes_by_client.sql
Created November 4, 2013 14:41
To find mailboxes by client in Plesk
SELECT mail.mail_name,domains.name
FROM clients, domains, mail
WHERE clients.id = <integer>
AND clients.id = domains.cl_id
AND domains.id = mail.dom_id
ORDER BY domains.name;
@siliconmeadow
siliconmeadow / domains_by_client_in_plesk.sql
Created November 4, 2013 14:39
To find domain names and hosting types by client in Plesk.
SELECT domains.name, domains.htype, clients.cname
FROM domains, clients
WHERE clients.id = domains.cl_id
AND clients.id = <(an integer)>
ORDER BY domains.htype ASC;
@siliconmeadow
siliconmeadow / protected_directories_in_plesk.sql
Created November 4, 2013 14:37
To find details about protected directories in Plesk.
SELECT domains.name, protected_dirs.path, protected_dirs.realm, pd_users.login, SUBSTRING(accounts.password, 1), `protected_dirs`.`ssl`, protected_dirs.non_ssl
FROM protected_dirs, pd_users, domains, accounts
WHERE domains.name LIKE '%<domain.dom>%'
AND protected_dirs.dom_id = domains.id
AND pd_users.pd_id = protected_dirs.id
AND pd_users.account_id = accounts.id;
@siliconmeadow
siliconmeadow / database_creds_in_plesk.sql
Created November 4, 2013 14:36
To find the database names, database usernames and passwords which belong to a database in Plesk
SELECT domains.name, data_bases.name, db_users.login, SUBSTRING(accounts.password, 1)
FROM domains, data_bases, db_users, accounts
WHERE domains.name LIKE '%<domain.dom>%'
AND db_users.account_id = accounts.id
AND db_users.db_id = data_bases.id
AND data_bases.dom_id = domains.id
ORDER BY data_bases.id;
@siliconmeadow
siliconmeadow / client_password.sql
Created November 4, 2013 14:33
To find a Plesk client's password, providing you know their login name.
SELECT clients.login , SUBSTRING(accounts.password, 1)
FROM clients, accounts
WHERE clients.login LIKE '<loginname>'
AND clients.account_id = accounts.id;
@siliconmeadow
siliconmeadow / sftp_passwords_on_plesk.sql
Created November 4, 2013 14:31
To find the passwords for an SFTP user on Plesk
SELECT accounts.id, mail.mail_name, domains.name, SUBSTRING(accounts.password, 1) AS pw
FROM accounts, mail, domains
WHERE mail.account_id = accounts.id
AND mail.dom_id = domains.id
AND mail.mail_name LIKE '%'
AND domains.name = '<domainName>';