Delete un-read email older the X days

[drhughes]

I'm looking to delete emails from my users mailboxes that are unread and over X number of days old. Any thoughts on how I might do this? I

[phoenix]

delete messages massively

[drhughes]

Is the query a SQL search into the MySQL DB? Is there a easy way to get a list of the valid fields?

[phoenix]

If you run 'zmmailbox help' it will give you a list of help sections that describe the fields you can use to search.

[dkarp]

Another (probably better) option is to set zimbraPrefInboxUnreadLifetime on the account.

[drhughes]

zimbraPrefInboxUnreadLifetime would be an awesome/easy way to do this, but I need to script something that will fit within our business rules.

Is there anymore docs on the search rather then zmmailbox help search?

[drhughes]

zmmailbox help search doesn't really give any details on how to search for messages within a date range. What I'm doing to find the messages I want to delete is:

1. zmprov getMailboxInfo userid@domain.
2. Get the mboxgrooup#
3. Logging into mysql using a query to find the messages.
4. Then zmmailbox -z -m userid@domain deleteItem #-#

My question is what format is the date field? I see it is a unsigned int.

[drhughes]

Can anybody decipher date: 1209569274

[dustys]

Originally Posted by drhughes

Can anybody decipher date: 1209569274

That is a UNIX time stamp.

1209569274 = Wed, 30 Apr 2008 15:27:54 GMT

Used Epoch Converter - Unix Timestamp Converter to convert it to human readable format.

Hope that helps!
Dusty

[drhughes]

Below is some code I have been playing with in a test environment. It will move unread email that is older then x number of days to the users /Trash folder for the system to clean up later.

I use three mailbox servers so I have designed the code to run on all three machines.

I strongly suggest making a backup before running. Use the following code at your own risk. I take no responsibility. You have been warned.

#!/bin/bash
#Move unread mail older then givendate from users mailboxes to /Trash
#Make a backup before running.  
#
#USE AT YOUR OWN RISK!
#

emaildomain="enter_email_domain_here"

days=$(date -d $1' days ago' '+%D')
hn=`hostname`"."`dnsdomainname`

who=`whoami`
if [ "$who" != "zimbra" ]
then
 echo
 echo "Please su to the zimbra user before running this script"
 echo
 exit
fi

if [ -z "$1" ]; then
 echo
        echo -e "\nUsage: deletemail <number of days ago>"
        echo
        exit
fi

echo "Deleting messages older then $days"

echo "Building user list for $hn"
zmprov gqu $hn | grep $emaildomain |cut -f 1 -d' '> /tmp/del-$hn.tmp

echo "Moving mail to /Trash"
for x in $(tail -1 /tmp/del-$hn.tmp)
do
 echo "Currently working on: $x"
 for y in $(zmmailbox -z -m $x search -t message -l 9999 "(before:$days)(is:unread)"|awk {'print $2'}|grep [0-9]|grep -v ,)
 do
  zmmailbox -z -m $x mm $y "/Trash"
  zmmailbox -z -m $x fm $y 1
 done
done