Zimbra data exposition through DB

[caio80]

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:

zmprov getMailboxInfo <account_name>

Not very DB-friendly...

Any past experience anybody?

Thanks in advance.

[uxbod]

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

[caio80]

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?...):

<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:

<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:

mysql> use mboxgroup8;

And now get some of the calendar stuff I just needed.

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:

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!

[sysinternals]

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