1. Please take a little time for this simple survey! Thank you for participating!
    Dismiss Notice
  2. Dear Pleskians, please read this carefully! New attachments and other rules Thank you!
    Dismiss Notice
  3. Dear Pleskians, I really hope that you will share your opinion in this Special topic for chatter about Plesk in the Clouds. Thank you!
    Dismiss Notice

Extract "all valid emails"?`

Discussion in 'Plesk for Linux - 8.x and Older' started by janjoh, Sep 11, 2008.

  1. janjoh

    janjoh New Pleskian

    22
    73%
    Joined:
    Aug 9, 2008
    Messages:
    18
    Likes Received:
    0
    Hello!

    I am looking at integrating PLESK within a corporate enviroment. I would need to be able to extract a list of _Every_ mail adress that the system would accept in order to send to their external antispam solution.

    I have found "normal" mail accounts by doing something like this

    select accounts.id, concat(mail.mail_name,'@',domains.name), accounts.password from mail left join domains on domains.id = mail.dom_id left join accounts on mail.account_id = accounts.id;


    But, what other sources do i need to analyse? Aliases i have found, but i cannot figure it out. mail_aliases has a mn_id, that i cannot link to a domain...

    Also, mailing lists.. where can i extract that?

    Am i missing any other types of mail adresses?
     
  2. shall

    shall Regular Pleskian

    26
    57%
    Joined:
    Apr 2, 2007
    Messages:
    226
    Likes Received:
    0
    determining valid email addresses

    As long as you don't have to have the other information (like passwords) from your original SQL query, this should do the trick for you:

    Code:
    SELECT CONCAT(mail.mail_name,'@',domains.name) 
    FROM mail LEFT JOIN domains ON domains.id = mail.dom_id;
    
    SELECT CONCAT(mail.mail_name,'@',domain_aliases.name) 
    FROM mail LEFT JOIN domain_aliases ON domain_aliases.dom_id = mail.dom_id
    WHERE domain_aliases.mail='true';
    
    SELECT CONCAT(maillists.name,'@',domains.name) 
    FROM maillists LEFT JOIN domains ON domains.id = maillists.dom_id;
    
    SELECT CONCAT(maillists.name,'@',domain_aliases.name) 
    FROM maillists LEFT JOIN domain_aliases ON domain_aliases.dom_id = maillists.dom_id
    WHERE domain_aliases.mail='true';
    
    SELECT CONCAT(mail_aliases.alias,'@',domains.name) 
    FROM mail_aliases 
    LEFT JOIN mail ON mail.id = mail_aliases.mn_id
    LEFT JOIN domains ON domains.id = mail.dom_id;
    
    SELECT CONCAT(mail_aliases.alias,'@',domain_aliases.name) 
    FROM mail_aliases
    LEFT JOIN mail ON mail.id = mail_aliases.mn_id
    LEFT JOIN domain_aliases ON domain_aliases.dom_id = mail.dom_id
    WHERE domain_aliases.mail='true';
    These statements build the email addresses for the 'normal' addresses, normal addresses on domain aliases, mailing lists, mailing lists on domain aliases, and mail aliases on primary domain, and mail aliases on aliased domains.

    WARNING!: This does NOT take into account those domains that may have configured a catch-all address. Most third-party spam filters forbid the use of catch-alls, though, so it's assumed that you're not using any catch-all addresses. To determine if you are using catchalls, and on what accounts, you can run this SQL:

    Code:
    SELECT domains.name
    FROM domains LEFT JOIN domainservices ON domains.id = domainservices.dom_id
    WHERE domainservices.id IN (
    SELECT parameters.id
    FROM parameters 
    WHERE parameters.parameter='nonexist_mail'
    AND parameters.value='catch'
    )
    ORDER BY domains.name;
     
  3. janjoh

    janjoh New Pleskian

    22
    73%
    Joined:
    Aug 9, 2008
    Messages:
    18
    Likes Received:
    0
    Wow.. thanks!

    A couple of statements failed though...

    mysql> SELECT CONCAT(mail.mail_name,'@',domain_aliases.name)
    -> FROM mail LEFT JOIN domain_aliases ON domain_aliases.dom_id = mail.dom_id WHERE domain_aliases.mail='true';
    ERROR 1146 (42S02): Table 'psa.domain_aliases' doesn't exist

    mysql> SELECT CONCAT(maillists.name,'@',domains.name)
    -> FROM maillists LEFT JOIN domains ON domains.id = maillists.dom_id;
    ERROR 1146 (42S02): Table 'psa.maillists' doesn't exist
    mysql>


    mysql> SELECT CONCAT(maillists.name,'@',domain_aliases.name)
    -> FROM maillists LEFT JOIN domain_aliases ON domain_aliases.dom_id = maillists.dom_id WHERE domain_aliases.mail='true';
    ERROR 1146 (42S02): Table 'psa.maillists' doesn't exist
    mysql>

    mysql> SELECT CONCAT(mail_aliases.alias,'@',domain_aliases.name)
    -> FROM mail_aliases
    -> LEFT JOIN mail ON mail.id = mail_aliases.mn_id LEFT JOIN domain_aliases ON domain_aliases.dom_id = mail.dom_id WHERE domain_aliases.mail='true';
    ERROR 1146 (42S02): Table 'psa.domain_aliases' doesn't exist
    mysql>


    Any idea why i do not seem to thave those aliases?

    And no, catch-alls would be a violation of policy.
     
  4. shall

    shall Regular Pleskian

    26
    57%
    Joined:
    Apr 2, 2007
    Messages:
    226
    Likes Received:
    0
    Looks like the *nix version has a couple tables named differently than the Win server where I use this. Here's a modified version that ought to work:

    Code:
    SELECT CONCAT(mail.mail_name,'@',domains.name) 
    FROM mail LEFT JOIN domains ON domains.id = mail.dom_id;
    
    SELECT CONCAT(mail.mail_name,'@',domainaliases.name) 
    FROM mail LEFT JOIN domainaliases ON domainaliases.dom_id = mail.dom_id
    WHERE domainaliases.mail='true';
    
    SELECT CONCAT(MailLists.name,'@',domains.name) 
    FROM MailLists LEFT JOIN domains ON domains.id = MailLists.dom_id;
    
    SELECT CONCAT(MailLists.name,'@',domainaliases.name) 
    FROM MailLists LEFT JOIN domainaliases ON domainaliases.dom_id = MailLists.dom_id
    WHERE domainaliases.mail='true';
    
    SELECT CONCAT(mail_aliases.alias,'@',domains.name) 
    FROM mail_aliases 
    LEFT JOIN mail ON mail.id = mail_aliases.mn_id
    LEFT JOIN domains ON domains.id = mail.dom_id;
    
    SELECT CONCAT(mail_aliases.alias,'@',domainaliases.name) 
    FROM mail_aliases
    LEFT JOIN mail ON mail.id = mail_aliases.mn_id
    LEFT JOIN domainaliases ON domainaliases.dom_id = mail.dom_id
    WHERE domainaliases.mail='true';
    windows: domain_aliases, maillists
    nix: domainaliases, MailLists
     
  5. janjoh

    janjoh New Pleskian

    22
    73%
    Joined:
    Aug 9, 2008
    Messages:
    18
    Likes Received:
    0
    That worked well enough!

    I am most grateful!
     
  6. faris

    faris Guest

    0
     
    This is extremely useful info. Thank you.

    It would be nice if this could be added to the Knowledgebase.

    Faris.
     
Loading...