[SOLVED] message flags

[cdenley]

After migrating to zimbra, all the users have messages in their Sent folder which do not have the "sent by me" message flag set. I was in the middle of writing a script to solve this problem when I realized the zmmailbox command's "flag" functions relate to importance flags, not zimbra's message flags. How can I mark existing messages with the "sent by me" flag using CLI tools, or how can I do it in bulk (or at all) from the web interface (or any)?

Message Flags - Zimbra :: Wiki

[cdenley]

I managed to do this by accessing the database directly. However, now the "To" field is empty while viewing the Sent folder in the web interface. Anyone know where that field is pulled from?

#!/usr/bin/python
import popen2,MySQLdb,sys

def CmdArg(arg):
    arg=arg.replace("\r","")
    arg=arg.replace("\\","\\\\")
    arg=arg.replace("\"","\\\"")
    arg=arg.replace("$","\\$")
    arg=arg.replace("`","\\`")
    arg="\""+arg+"\""
    arg=arg.strip(" \r\n\t\r")
    return arg

def ZimbraSearch(user,query):
    ret=[]
    cmd="zmmailbox -z -m "+CmdArg(user)
    cmd+=" search -l 999999 -t message "
    cmd+=CmdArg(query)
    stdout,stdin=popen2.popen2(cmd)
    for line in stdout:
        line=line.strip(" \n")
        while line.find("  ")!=-1:
            line=line.replace("  "," ")
        parts=line.split(" ")
        if len(parts)>2 and parts[2]=="mess":
            ret.append(parts[1])
    stdout.close()
    stdin.close()
    return ret

def GetEmails(user):
    ret=[]
    cmd="zmprov ga "+CmdArg(user)+" mail"
    stdout,stdin=popen2.popen2(cmd)
    for line in stdout:
        line=line.strip(" \n")
        if line[:6]=="mail: ":
            ret.append(line[6:])
    stdout.close()
    stdin.close()
    return ret

def DefaultDomain():
    ret=""
    cmd="zmprov gacf zimbraDefaultDomainName"
    stdout,stdin=popen2.popen2(cmd)
    for line in stdout:
        line=line.strip(" \n")
        if line[:25]=="zimbraDefaultDomainName: ":
            ret=line[25:]
    stdout.close()
    stdin.close()
    return ret

def GetPass():
    ret=""
    cmd="zmlocalconfig -s zimbra_mysql_password"
    stdout,stdin=popen2.popen2(cmd)
    for line in stdout:
        line=line.strip(" \n")
        if line[:24]=="zimbra_mysql_password = ":
            ret=line[24:]
    stdout.close()
    stdin.close()
    return ret

def Esc(data):
    return "\""+MySQLdb.escape_string(data)+"\""

def GetMbox(conn,user):
    cursor = conn.cursor ()
    sql="SELECT id FROM mailbox WHERE comment="+Esc(user)
    #print sql
    cursor.execute(sql)
    row = cursor.fetchone ()
    if row is None:
        print "Failed to find account "+user
        sys.exit(1)
    cursor.close()
    return row[0]

def GetGroup(conn,user):
    cursor = conn.cursor ()
    sql="SELECT group_id FROM mailbox WHERE comment="+Esc(user)
    #print sql
    cursor.execute(sql)
    row = cursor.fetchone ()
    if row is None:
        print "Failed to find account "+user
        sys.exit(1)
    cursor.close()
    return row[0]

def MakeSent(conn,mboxid,groupid,msg):
    cursor = conn.cursor ()
    sql="UPDATE mboxgroup"+str(groupid)+".mail_item"
    sql+=" SET flags=3 WHERE id="+msg
    sql+=" AND mailbox_id="+str(mboxid)
    #print sql
    cursor.execute(sql)
    cursor.close ()

if len(sys.argv)<2:
    print "You must give at least one user!"
    sys.exit(1)
defdom=DefaultDomain()
for i in range(1,len(sys.argv)):
    user=sys.argv[i]
    if user.find("@")==-1:
        user+="@"+defdom
    print "checking "+user+"..."
    emails=GetEmails(user)
    print "getting ID's..."
    conn=MySQLdb.connect(unix_socket='/opt/zimbra/db/mysql.sock',user='zimbra',passwd=GetPass(),db='zimbra')
    mbox=GetMbox(conn,user)
    gid=GetGroup(conn,user)
    query="from:("
    first=True
    for email in emails:
        if not first:
            query+=" or "
        first=False
        query+=email
    query+=") in:Sent is:received"
    print "searching..."
    msgs=ZimbraSearch(user,query)
    print "fixing..."
    for msg in msgs:
        MakeSent(conn,mbox,gid,msg)
    conn.commit ()
    conn.close ()

[cdenley]

After exporting then importing the Sent folder, everything seems to be correct. I wonder if that would have fixed the flags as well.

[cmargena]

I have the same problem, but 150000 accounts. Sent folder has not been imported with "sentByMe" flag and every email in sent folder is shown with the incorrect recipient by the web interface. I know this post is old but does anybody has the answer as to how to correct this flag in order for the email to be display correctly by the web interface? Remember that I have 150k email accounts and I is feasible to reimport all sent folders again.

[phoenix]

Originally Posted by cmargena

I have the same problem, but 150000 accounts. Sent folder has not been imported with "sentByMe" flag and every email in sent folder is shown with the incorrect recipient by the web interface. I know this post is old but does anybody has the answer as to how to correct this flag in order for the email to be display correctly by the web interface? Remember that I have 150k email accounts and I is feasible to reimport all sent folders again.

This thread is marked as 'Solved', doesn't the script in the second post do what you want (it seems to have worked for the o/p)?

[cmargena]

I think that the status is solved because cdenley said that he export and import the sent folder, if you see the script comment it says that after running it the "To" field is empty. The only interesting thing that the scripts does is an update of mail_item table setting flags field with value 3.
I want to know the bitmap value of flags field and if there is another "adjustment" in order to fix this thing.