[Dbmail] Finding aliases that point to aliases that point to users or ...

[Matt Salerno]

I am looking for a way to find aliases that point to aliases that
point to users.  Or aliases that point to users that forward to
another user.  I was just wondering what other people have done to
keep the sanity.  I have a query that pretty much takes care of it, I
think.  Does anyone else use anything else to manage situations like
these?  Even dbmail administrator does not display this information,
from what I have seen.

Ex.
[EMAIL PROTECTED] -> [EMAIL PROTECTED] - > [EMAIL PROTECTED] -> forward -> 
[EMAIL PROTECTED]

Here is my solution:

Select
        dbmail_users.userid,
        dbmail_users.user_idnr,
        da2.alias_idnr,
        da1.alias as da1alias,
        case
                when da1.alias = da2.deliver_to then da2.alias_idnr 
                when da2.alias = da1.deliver_to then da1.alias_idnr
        END AS 'Alias_idnr',
        case
                when da1.alias = da2.deliver_to then da2.alias
                when da2.alias = da1.deliver_to then da1.alias
        END AS 'Alias',
        
        case
                when da1.alias = da2.deliver_to then da1.deliver_to
                when da2.alias = da1.deliver_to then da2.deliver_to
        END AS 'Deliver_to',
        
        case
                        when Deliver_to = da2.deliver_to then da1.deliver_to
                        when da2.alias = da1.deliver_to then da2.deliver_to
        END AS 'AliasORForward'
from
        dbmail_users
Left Join
        dbmail_aliases da1 on
                da1.alias = dbmail_users.userid
                or
                da1.alias = dbmail_users.user_idnr
                or
                da1.deliver_to = dbmail_users.userid
                or
                da1.deliver_to = dbmail_users.user_idnr

Left Join
        dbmail_aliases da2 on
                da2.alias = da1.deliver_to
                or
                da2.deliver_to = da1.alias

where dbmail_users.userid = '[EMAIL PROTECTED]'


-- 
Matthew J. Salerno