Results 1 to 7 of 7

Thread: mailbox_id to email address

  1. #1
    Join Date
    Aug 2006
    Location
    San Diego
    Posts
    193
    Rep Power
    9

    Default mailbox_id to email address

    hey everyone,
    does anyone know how i can retrieve the email address associated with a mailbox_id from the zimbra mysql database?

  2. #2
    Join Date
    Aug 2006
    Location
    San Diego
    Posts
    193
    Rep Power
    9

    Default

    I originally started out with some issues saying MESSAGE_BLOB_NOT_FOUND and found out that there were some messages in my database that were associated with a volume that no longer existed.
    i wrote this up really quick to help me figure out what emails were associated with the missing volume. hope it helps someone else out, not a final product you'd need to modify it to suite your needs
    and i'm not a programmer by any means so excuse it if it's ugly

    <?

    $dataArray = array();
    $mailboxIdArray = array();
    $mailboxLostDates = array();
    $counter = 0;

    $link = mysql_connect('mail:7306', 'zimbra', 'XXXXXXXXXXXXXX');
    if (!$link) {
    die('Could not connect: ' . mysql_error());
    }
    echo "Connected successfully\n";

    $db_list = mysql_list_dbs($link);

    while ($row = mysql_fetch_object($db_list)) {
    if(strncmp($row->Database, "mbox", 4) == 0) {
    //echo $row->Database . "\n";
    $db_selected = mysql_select_db($row->Database, $link);
    if (!$db_selected) {
    die ('Can\'t use foo : ' . mysql_error());
    }
    else {
    //echo "Connected to ".$row->Database."\n";
    //now that we've connected to each mboxgroup DB,
    //lets retrieve table information
    $volume3sql = "select * from mail_item where volume_id=3;";
    $result = mysql_query($volume3sql);
    if (!$result) {
    die('Invalid query: ' . mysql_error());
    }
    else {
    while ($row = mysql_fetch_assoc($result)) {
    $counter++;
    if(!in_array($row["mailbox_id"],$mailboxIdArray))
    {
    array_push($mailboxIdArray, $row["mailbox_id"]);
    }
    //we need to start aggregating the data
    //set returned from mysql, prep to email
    //$dataRow = "MailBox : ".$row["mailbox_id"]."\n";
    //$dataRow .= date("F j, Y, g:i a", $row["date"])."\n";
    //$dataRow .= "Sender : ".$row["sender"]."\n";
    //$dataRow .= "Subject : ".$row["subject"]."\n";
    //$dataRow .= "Name : ".$row["name"];
    //$dataRow .= "Metadata: ".$row["metadata"]."\n";
    //echo $dataRow."\n";
    }
    }
    }
    }
    }
    print_r($mailboxIdArray);
    ?>

  3. #3
    Join Date
    Aug 2006
    Location
    San Diego
    Posts
    193
    Rep Power
    9

    Default

    I went to the Wiki page for zmprov, Zmprov - Zimbra :: Wiki, and found that

    zmprov ga can be passed an ID,
    i attempted the following and got this output.

    [zimbra@mail ~]$ zmprov ga 12;zmprov ga 117
    ERROR: account.NO_SUCH_ACCOUNT (no such account: 12)
    ERROR: account.NO_SUCH_ACCOUNT (no such account: 117)

  4. #4
    Join Date
    May 2006
    Location
    USA
    Posts
    6,242
    Rep Power
    21

    Default

    Think your confusing ID's

    System wide user ID = zimbraId
    Per store ID = mailboxId

    zmprov ga user@domain.com | grep zimbraId
    zimbraId: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

    This is the one where you can:
    zmprov ma zimbraIdStringHere zimbraWhateverAttribute valueDesired

    zmproc gmi user@domain.com
    mailboxId: xxxxx
    quotaUsed: xxxxxxxxxxxxxx

    Account mailbox database structure - Zimbra :: Wiki

  5. #5
    Join Date
    Aug 2006
    Location
    San Diego
    Posts
    193
    Rep Power
    9

    Question

    is there a way i can pull the account ID from the database and convert them into an email address? I want to try and use this script to figure who's email is lost.
    thank you,

  6. #6
    Join Date
    Aug 2006
    Location
    San Diego
    Posts
    193
    Rep Power
    9

    Default

    Hey

    given this information here from the database, how do I find out the mailbox associated with this data base row.

    proxy 10:28 PM reza ~/ZimbraWork 6 #mysql -hmail -P7306 -uzimbra -p*************
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 2521
    Server version: 5.0.51a-log Source distribution

    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

    mysql> use mboxgroup22
    Database changed
    mysql> select * from mail_item where volume_id=3 limit 1\G
    *************************** 1. row ***************************
    mailbox_id: 22
    id: 257
    type: 5
    parent_id: NULL
    folder_id: 2
    index_id: 257
    imap_id: 257
    date: 1166346769
    size: 53233
    volume_id: 3
    blob_digest: AU4q1vax0ZPJBvc1A3cCSqNCNC4=
    unread: 0
    flags: 0
    tags: 0
    sender: sales@godaddy.com
    subject: GoDaddy.com Order Confirmation
    name: NULL
    metadata: d1:f121:<http://imagesak.godaddy.com/promos/std/spc_trans.gif> <http://imagesak.godaddy.com/promos/htmlemails/hdr_thanks.gif> ...1:s19:<sales@godaddy.com>1:vi9ee
    mod_metadata: 6
    change_date: 1166411584
    mod_content: 2
    1 row in set (0.02 sec)

    mysql>

  7. #7
    Join Date
    May 2006
    Location
    USA
    Posts
    6,242
    Rep Power
    21

    Default

    Quote Originally Posted by reza225 View Post
    mysql> use mboxgroup22
    Database changed
    mysql> select * from mail_item where volume_id=3 limit 1\G
    *************************** 1. row ***************************
    mailbox_id: 22
    id: 257
    su - zimbra
    mysql
    use zimbra;
    select comment from mailbox where id=257;
    And you'll get back:
    +----------------------+
    | comment |
    +----------------------+
    | user@domain.com |
    +----------------------+
    1 row in set (0.00 sec)

    If you wanted to see all the info for that account:
    select * from mailbox where id=257;
    And you'll get back:
    +-----+----------+--------------------------------------+-----------------+--------------------+---------------+-----------------+-------------------+---------------+---------------+----------------+----------------------+------------------+--------------+--------------------+
    | id | group_id | account_id | index_volume_id | item_id_checkpoint | contact_count | size_checkpoint | change_checkpoint | tracking_sync | tracking_imap | last_backup_at | comment | last_soap_access | new_messages | idx_deferred_count |
    +-----+----------+--------------------------------------+-----------------+--------------------+---------------+-----------------+-------------------+---------------+---------------+----------------+----------------------+------------------+--------------+--------------------+
    | 257 | 71 | xxxxxxxx-xxxx-xxxx-xxxxxxxxxxxxxxxxx | 2 | 257 | 0 | 1832 | 100 | 0 | 0 | NULL | user@domain.com | 1209652908 | 0 | 0 |
    +-----+----------+--------------------------------------+-----------------+--------------------+---------------+-----------------+-------------------+---------------+---------------+----------------+----------------------+------------------+--------------+--------------------+
    1 row in set (0.00 sec)

    There's other ways to map accountId to account name as well, takes another step, but the comment should be fine if you're not having difficulty because you normally look at canonical addresses or something.)
    Last edited by mmorse; 05-22-2008 at 07:43 PM.

Similar Threads

  1. Can't change outgoing email address
    By albanach in forum Zimbra Connector for Outlook
    Replies: 3
    Last Post: 02-06-2008, 12:09 PM
  2. Change Install Admin Email Address
    By soxfan in forum Administrators
    Replies: 4
    Last Post: 11-01-2007, 05:10 AM
  3. need advice on configuring zimbra to work with fax server
    By pheonix1t in forum Administrators
    Replies: 0
    Last Post: 07-11-2007, 08:46 PM
  4. upgrade to 4.0.3 antispam does'nt work
    By lucanannipieri in forum Administrators
    Replies: 14
    Last Post: 11-07-2006, 03:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •