Results 1 to 4 of 4

Thread: Zimbra data exposition through DB

  1. #1
    Join Date
    Mar 2007
    Location
    Ferrara, Italy
    Posts
    55
    Rep Power
    8

    Default Zimbra data exposition through DB

    Hi there,

    we've just went live with a shining new ZCS 5.0.10 installation yesterday, and we are already wondering whether anybody has already tried any kind of integration between Zimbra and BI apps such as the Pentaho suite.

    By searching through the forums and docs, I got the point: the SOAP interface is the suggested way to access data regarding calendars, contacts and so on. Unfortunately that wouldn't suit our integration requirements with other apps that just reason in DB terms... as Pentaho does indeed... :-(

    I've therefore started looking around in zimbra's mysql, but I saw there are way too many distributed pieces of information to be reconstructed, and even some are still not clear to me: for instance, mboxgroupX is numbered after the UserUID, but the only way to retrieve it is through:

    Code:
    zmprov getMailboxInfo <account_name>
    Not very DB-friendly...

    Any past experience anybody?

    Thanks in advance.

  2. #2
    Join Date
    Nov 2006
    Location
    UK
    Posts
    8,017
    Rep Power
    24

    Default

    Could you not write a SOAP -> DB interface in say Perl or PHP and perform the translation in there.

  3. #3
    Join Date
    Mar 2007
    Location
    Ferrara, Italy
    Posts
    55
    Rep Power
    8

    Default

    Oh, I'm sure I could, uxbod. But actually I'm not that sure this is the best way to proceed, but it could work, no doubt about it...

    Incidentally I'm a huge fan of the motto: "Don't reinvent the wheel", that's why I'm here posting in the forums...

    Furthermore, I've never worked with SOAP before, I've never wrote a wrapper (not that's an impossible thing to do, bear me with that), and in the end I think that for what I need (read only, SQL ready apps to work it with, etcetera) the SQL query route is the way to go.

    And I'm not that far away from what I want... After a little peeking, I came up with the following (I just wrote a small walkthrough, just for the sake of it):



    first of all, find out what's your target uid (anybody found out where this info is stored in MySQL, if is it there indeed?...):
    Code:
    <log in as root on the server>
    su - zimbra
    zmprov getMailboxInfo <account_name>
    Retrieve the zimbra mysql pwd and log on MySQL CLI with it:

    Code:
    <log in as root on the server>
    # su - zimbra
    # zmlocalconfig -s |grep mysql |grep zimbra_mysql_password
    # mysql -p
    now select the user section in MySQL you are interested in:
    Code:
    mysql> use mboxgroup8;
    And now get some of the calendar stuff I just needed.
    Code:
    mysql> SELECT mi.subject, ap.start_time, ap.end_time from mail_item as mi,appointment as ap where mi.id=ap.item_id;
    For instance, what I've found is that (by trial&guess&error) mail_item.type is something that belongs to one of the following sections:
    Code:
    1 Mail folder
    4 Mail
    5 Mail - intercomm by zimbra(EN)
    6 contacts
    7 documents
    11 appointment ?
    13 contact book
    14 boh ?
    15 appointment
    16 chat

    I am already happy enough so far, but as you can see, little is left to be found. Once I understand how I can "SQL-find" user ids, it's pretty much done! In other words, I want to switch users by SQL doing it, by joining tables and stuff like that...

    Otherwise, I will need to write from scratch a SOAP wrapper as uxbod suggested...

    Anybody willing to help me out on this? Even by PM if you don't wanna share the info (why wouldn't you anyways!?!?!)

    Brrr!

    P.S. uxbod: If you already have created a wrapper for this (!!!), wouldn't you mind sending it over to us, would you? In that case, that would become my favourite route to follow, being it already done by somebody else!
    Last edited by caio80; 11-06-2008 at 07:14 AM.

  4. #4
    Join Date
    Jan 2009
    Posts
    12
    Rep Power
    6

    Default

    has anyone made any progress with pentaho integration?
    I'd also need it so I can use pentaho reports on zimbra.

Similar Threads

  1. slapd message error
    By smoke in forum Administrators
    Replies: 7
    Last Post: 04-27-2008, 03:23 PM
  2. [SOLVED] Error Installing Zimbra on RHEL 5
    By harris7139 in forum Installation
    Replies: 10
    Last Post: 09-25-2007, 11:39 AM
  3. Replies: 8
    Last Post: 02-27-2007, 03:10 AM
  4. svn version still won't start
    By kinaole in forum Developers
    Replies: 0
    Last Post: 10-04-2006, 06:47 AM
  5. Replies: 16
    Last Post: 09-07-2006, 06:39 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
  •