Deleting appointments from command-line

Revision as of 21:39, 5 September 2007 by Czaveri (talk | contribs) (Initial Article)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

PART 1: DELETING APPOINTMENTS

It is best to delete appointments/meetings from the GUI, using Zimbra Web Client, Zimbra Connector for Outlook, Entourage, etc. However, in cases when you are unable to do so for whatsoever the reason, you will need to use the command-line to delete appointments.

This document explains how you can delete appointments through the command-line interface. Typically, you need to do this when you are hitting some bug or need to automate something using a script. If you are hitting a bug, it is also a good idea to grab the blob of the appointment that you are deleting by hand.

An appointment or meeting in Zimbra is a “mailItem”. (All emails are “mailItem”, too)

So, deleting appointments through command-line is simple. All you need is zmmailbox.

The complex part is deriving the itemNumber. Here is an example which explains this.

In the below given example: user1@example.com is the user facing this issue. He has an appointment with Subject "Test Appointment1" and Body "This is sample appointment. For testing purposes only." The appointment is scheduled for 5th Sep 2007, 12:30 PM upto 5th Sep 2007, 1:30 PM.

I have embedded comments in-line by using "#" at the start of the line.

Before proceeding with this exercise, please ensure that you have the Subject, Body, Start and End Times of the appointments accurately. If possible, please keep a screenshot handy.

Log-in to the system.

# Fetch the user's mailbox Id

[root@supp01 ~]# su - zimbra
[zimbra@supp01 ~]$ zmprov getMailboxInfo user1@example.com
mailboxId: 11
quotaUsed: 30620

# Fetch the user's mboxgroup Id

$ expr 11 % 100
11

# We concatenate the group ID with the term "mboxgroup" to get the the user's database. In this case, it is “mboxgroup11”.

[zimbra@supp01 ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 205
Server version: 5.0.33-log Source distribution

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

mysql> use mboxgroup11;
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

# From the information that we already have we can now create the following query. In the following query please do not forget to put % as I have done so. 

mysql> select * from mail_item where mailbox_id="11" and subject like "%Sample Appointment1%" and metadata like "%This is sample appointment%";
+------------+-----+------+-----------+-----------+----------+---------+------------+------+-----------+------------------------------+--------+-------+------+-------------+---------------------+------+-----------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+-------------+
| mailbox_id | id  | type | parent_id | folder_id | index_id | imap_id | date       | size | volume_id | blob_digest                  | unread | flags | tags | sender      | subject             | name | metadata                                                                                                                          | mod_metadata | change_date | mod_content |
+------------+-----+------+-----------+-----------+----------+---------+------------+------+-----------+------------------------------+--------+-------+------+-------------+---------------------+------+-----------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+-------------+
|         11 | 263 |    5 |       266 |         3 |      263 |     263 | 1191243364 | 4300 |         1 | Wnl6PwOTDf0DeuHjOuu4BmFHP38= |      0 |     4 |    0 | Fname Lname | Sample Appointment1 | NULL | d3:aisld1:ai264e1:ci0eee1:f54:This is sample appointment. For testing purposes only.1:s31:Fname Lname <user2@example.com>1:vi10ee |           13 |  1191243402 |           7 |
+------------+-----+------+-----------+-----------+----------+---------+------------+------+-----------+------------------------------+--------+-------+------+-------------+---------------------+------+-----------------------------------------------------------------------------------------------------------------------------------+--------------+-------------+-------------+
1 row in set (0.00 sec)

mysql> \q
Bye
[zimbra@supp01 ~]$

From the last output, pick-up the “id” and “mod_content” and concatenate them with a “-” (hyphen). So in the above case, it will be “263-7”. This is our itemId.

So, to delete this appointment, you need to run the command:

[zimbra@supp01 ~]$ zmmailbox -z                 

mbox> sm user1@example.com

mailbox: user1@example.com, size: 35.36 KB, messages: 9, unread: 2

mbox user1@example.com> deleteItem 264-9

mbox user1@example.com> quit

[zimbra@supp01 ~]$

If you also want to get the blob, please see Bobby's article http://wiki.zimbra.com/index.php?title=Account_mailbox_database_structure

PART 2: DELETING DUPLICATE APPOINTMENTS

(Please read PART 1 in order to understand the following text)

There have been some instances of users reporting that they have duplicate appointments. Deleting these is the same process as the above one. However, individually tracing out each itemId of duplicate mail items only is very cumbersome. For this, here's what you could do.

1. Define the criteria for "duplicate". It can be as simple as having the same subject, or you may need to look at more of the data. - More data may include description/body of the appointment, start and end times, etc.

2. Whatever the criteria, you can apply them to the REST-dump of each mailbox's calendar. That will give you the list of appointment UIDs to delete. - Here's a blog entry (http://www.zimbra.com/blog/archives/2006/01/rest_update.html) that talks about how you can download the ICS using REST. Grab the UIDs from this. You need to do some scripting here or just manually lookup.

3. The item id for the UIDs can be looked up from the appointment table. - E.g. select * from mboxgroup11.appointment;

Use “zmmailbox” for deleting. Please see PART 1.

Jump to: navigation, search