Link to home
Start Free TrialLog in
Avatar of Goutham
GouthamFlag for India

asked on

tracing deleted message in linux zimbra mail server

Dear Experts:

Iam having mail server in linux using zimbra please help in the following
grep -i john.*Trash  mailbox.log.2009-10-10
2009-10-10 20:46:30,498 INFO  [btpool0-21] [name=john@abc.com;aname=admin@abc.com;mid=20;ip=192.168.1.21;ua=ZimbraWebClient - [unknown] (Win)/5.0.18_GA_3011.RHEL5;] mailop - Moving Conversation (id=854) to Folder Trash (id=3).  Affected message ids: 834,853,856,859,860,885,886,888.

[zimbra@mail log]$ grep -i john.*Deleting mailbox.log.2009-10-10
2009-10-10 18:01:43,245 INFO  [MailboxPurge] [name=john@abc.com;mid=20;] mailop - Deleting items: 639.

the above log gives the user , time, Moving and Deleting information with this i can make out the users action but very important is missing that is how to find out for which message the user has performed these actions. Please help me on this, Thanks in advance.
Avatar of jar3817
jar3817

Those entries give you the message ID: "Affected message ids: 834,853,856,859,860,885,886,888", you need to match those to the message IDs in the database.  According to http://wiki.zimbra.com/index.php?title=Account_mailbox_database_structure it looks like you need to match that to the "id" field in the "mail_item" table.

# mysql -u <username> -p
> USE <zimbra-database-name>;
> SELECT * FROM mail_item WHERE id IN (834,853,856,859,860,885,886,888);

Avatar of Goutham

ASKER

Sir, thanks for the reply . as per your instructions i did the following:
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| backup             |
| mboxgroup1         |
| mboxgroup10        |
| mboxgroup11        |
| mboxgroup12        |
| mboxgroup13        |
| mboxgroup14        |
| mboxgroup15        |
| mboxgroup16        |
| mboxgroup17        |
| mboxgroup18        |
| mboxgroup19        |
| mboxgroup2         |
| mboxgroup20        |
| mboxgroup21        |
| mboxgroup22        |
| mboxgroup23        |
| mboxgroup24        |
| mboxgroup25        |
| mboxgroup26        |
| mboxgroup27        |
| mboxgroup28        |
| mboxgroup3         |
| mboxgroup4         |
| mboxgroup5         |
| mboxgroup6         |
| mboxgroup7         |
| mboxgroup8         |
| mboxgroup9         |
| mysql              |
| test               |
| zimbra             |
+--------------------+
33 rows in set (0.00 sec)

mysql> use zimbra;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT * FROM mail_item WHERE id IN (834,853,856,859,860,885,886,888);
ERROR 1146 (42S02): Table 'zimbra.mail_item' doesn't exist
mysql>
---------------------------------------------------------------------------------------------------------------
table mail_item doe not exist , please help me how to find messages pertaining to the mentioned id

for your reference posted the following :

 show tables;
+----------------------+
| Tables_in_zimbra     |
+----------------------+
| config               |
| current_volumes      |
| deleted_account      |
| jiveExtComponentConf |
| jiveGroupProp        |
| jiveGroupUser        |
| jiveID               |
| jiveOffline          |
| jivePrivacyList      |
| jivePrivate          |
| jiveProperty         |
| jiveRemoteServerConf |
| jiveRoster           |
| jiveRosterGroups     |
| jiveSASLAuthorized   |
| jiveUserProp         |
| jiveVCard            |
| jiveVersion          |
| mailbox              |
| mailbox_metadata     |
| mucAffiliation       |
| mucConversationLog   |
| mucMember            |
| mucRoom              |
| mucRoomProp          |
| out_of_office        |
| scheduled_task       |
| service_status       |
| table_maintenance    |
| volume               |
+----------------------+
30 rows in set (0.00 sec)

-------------------------------------------------------------------------------------------------------------
please help



It's in one of the mboxgroup databases. You need to lookup the user's mailboxID:

# zmprov getMailboxInfo john@abc.com

That should give you a number. Take the last 2 digits of that number. For example if it returns "5247" as their maiboxID, use the "47", so they would be in the "mboxgroup47" database.

This is all explained in the first couple paragraphs of this page: http://wiki.zimbra.com/index.php?title=Account_mailbox_database_structure
Avatar of Goutham

ASKER

Thanks for the reply ,
 zmprov getMailboxInfo john@abc.com
mailboxId: 20
quotaUsed: 64749583
--------------------------------
please tell me how to go ahead from here.




Interesting. Well according to that page, it calculates the group number by doing (ID % 100) which in the case 20 is 20. So look in mboxgroup20
Avatar of Goutham

ASKER

Sir also did the following:

logged into mysql :
use mboxgroup20;


mysql> show tables;
+-----------------------+
| Tables_in_mboxgroup20 |
+-----------------------+
| appointment           |
| data_source_item      |
| imap_folder           |
| imap_message          |
| mail_item             |
| open_conversation     |
| pop3_message          |
| revision              |
| tombstone             |
+-----------------------+
9 rows in set (0.00 sec)

now i do not know how to go further please help.
mail_item is the table you want. Try this query in that database:

SELECT * FROM mail_item WHERE id IN (834,853,856,859,860,885,886,888);

It should show you the messages you're looking for, assuming they're still in there; remember they were deleted and zimbra might remove all traces of the messages once they're deleted, I don't know.
Avatar of Goutham

ASKER

Sir , great i got them there with the subject of messages
[name=john@abc.com;aname=admin@abc.com;mid=20;ip=192.168.1.21;ua=ZimbraWebClient - [unknown] (Win)/5.0.18_GA_3011.RHEL5;] mailop - Moving Conversation (id=854) to Folder Trash (id=3).  Affected message ids: 834,853,856,859,860,885,886,888.

but that day i moved only one message but that message has more threads ( chain of messages as reply was happening from the user and the remote user of other domain)

2. [zimbra@mail log]$ grep -i john.*Deleting mailbox.log.2009-10-10
2009-10-10 18:01:43,245 INFO  [MailboxPurge] [name=john@abc.com;mid=20;] mailop - Deleting items: 639.

Please help me in the Deleting items where  and how should i execute the query so that 639 message will be accessed, please help.

Avatar of Goutham

ASKER

Sir i tried
SELECT * FROM mail_item WHERE id IN (639);
Empty set (0.01 sec)

any other method
ASKER CERTIFIED SOLUTION
Avatar of jar3817
jar3817

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Goutham

ASKER

Sir, could not find in other tables ; anyhow will check a while and close this query , great help from you.
Thanks.