King0770-Notes-Find Out When Message Was Read
To figure out when a message was read, you'll need to use the cli to get the info.
Basically you'll need to know the mailbox ID, and the ID of the message. More info: http://wiki.zimbra.com/index.php?title=Account_mailbox_database_structure
1) Find the mailbox ID.
zmprov gmi email@example.com
Take the value of "mailboxId" and run this command:
expr <mailboxId> % 100
The value of the expr command is the value you use to connect to the user's mysql data.
Example zmprov gmi firstname.lastname@example.org mailboxId: 5247 expr 5247 % 100 47 mysql mboxgroup47
2) ID of the message
To view all the messages with the Id's in a flat file you could run:
su - zimbra mysql -e "mysql -e "select * from mboxgroup47.mail_item where mailbox_id='5247' \G" >> /tmp/accountInfo.txt
Once you have generated a file, and identified the message, look for the "change_date" line. This should indicate when the message was last touched. The line would show something like this:
To convert 1226347705 into a readable form, put it into a perl command.
Example perl -e 'print localtime(1226347705). "\n"'
Keep in mind the user can mark messages as unread after reading them, so be sure to look at the "unread" line from the mysql output.