Mailtraq - the Complete Email Server
   

Enstar for Mailtraq email server sales & support
Making world-class Internet technology affordable  

Search for:

Advanced search

KB10052701 AFXutils - Advanced AFX file manipulation

AFXutils is a utility for working with AFX files

You can get a copy here (Version 1.7):  AFXUTIL

Usage:
Download and extract afxutils.exe and place it in the folder in which you will be working.
This is normally a dedicated folder outside the Mailtraq folder structure.

It is an application, used from the Command prompt, that can also create new mailstore files, correct structures and indices and manipulate the data using SQL.

Also available: AFX Viewer

This utility with a user friendly GUI makes it possible to use archived AFX files for searching and accessing messages and directly managing AFX message stores.  It makes it easy to do common tasks, such as Compressing an AFX file after a large number of deletions.

Read more and get the AFX Viewer here...


Working with AFXUTIL

Here are some example usages.
Remember Mailtraq MUST be shutdown when manipulating the files direct, and ALWAYS create a copy file BEFORE you start any manipulation - so it is easy to 'step-back' if you need to.

In these examples, replace "mailbox" with the appropriate mailbox name. 
Use the name as it appears in the directory (e.g. may be "susan_bunbury$2D" instead of "susan.bunbury")

Manual Expiry (Expunge)

Say a mailbox - 'admin' - has a large number of messages that have been expired to Archive, but have not yet been deleted from the mailbox. This would be indicated by messages being in a Console mailbox but not assigned to a Folder, and is caused by having too short a Maintenance period. The Expunge command will remove these messages. A backup of the original is created. This should be removed to a safe place (different folder) and retained until you are sure it is no longer required.

afxutil -expunge admin.afx

If you want to iterate through an entire folder of AFX files:

for %n in (*.afx) do afxutil -expunge %n 

Direct deletion of Folder contents.

Say a mailbox - 'mailbox' - has become choked with spam or deleted emails due to a misconfiguration or trojan incident. Copy mailbox.afx to a new directory and put afxutil.exe in there. At the command prompt, type

afxutil -sql "DELETE FROM mailbox WHERE FOLDER = 3 OR FOLDER = 4"

This will empty the trash and junk mail folders. If you have relatively few messages in the other folders, it may be faster to copy that data instead. In which case you would do this:

afxutil -create fixed.afx
afxutil -sql "INSERT INTO fixed SELECT * FROM mailbox WHERE FOLDER < 3 OR FOLDER > 4"

then replace the original mailbox.afx with fixed.afx

Tip:
You can detemine folder numbers by examining in Notepad the entry at:
Path:\Program Files\Mailtraq\database\configuration\mailfolders

Manual Expire (Split)

This command allows you to split a message store before and after number of days. It creates two new files (before n days and after n days) and swaps the after with the original, so no deleting is involved. The target could be a secondary mailbox or in the mailbox archive:  archive\mailbox.afx
If the target exists it appends, otherwise it creates it.

afxutil -expire days target.afx source.afx

Finding folder identifiers and message counts

This provides a list of folder numbers and how many messages are in each.

afxutil -sql "SELECT DISTINCT(FOLDER), COUNT(*) FROM mailbox GROUP BY
FOLDER"

Emptying folders

afxutil -sql "DELETE FROM mailbox WHERE FOLDER = 1"

That's how you delete folder number "1"

However!  It is slightly faster to add a message than removing it.
Therefore if you are going to delete 280000 messages from a mailbox with 300000 then it is much faster to create a new mailbox.  Here's how you do that:

(2b)

ren mailbox.afx mailbox_old.afx
afxutil -create mailbox.afx
afxutil -sql "INSERT INTO mailbox SELECT * FROM mailbox_old WHERE FOLDER <> 1"

 

Recovering messages marked 'Deleted'

Q: "How do I recover messages that have 'disappeared' from the Inbox, but I can still see in the Console marked as Deleted?"

Message deletion in Mailtraq is a two step process. Messages are first marked as 'Deleted' and are removed from view. A second scheduled Maintenance task then Expunges these marked messages. You can reset the flag on marked messages and so recover them. You cannot recover messages that have been Expunged.

In this explanation we use two placeholder mailbox names: oldmailbox and newmailbox
Replace 'oldmailbox' with the mailbox name you are working with.
ONLY WORK on a COPY of the AFX file in question!

Use the Mailtraq Console to create a new secondary mailbox for the User and call it 'newmailbox'.

You can also manually create a new mailbox
  afxutil -create "newmailbox.afx"

Copy deleted mail over
  afxutil -sql "INSERT INTO newmailbox SELECT * FROM oldmailbox WHERE FOLDER
= 65535"

You can inspect the mailbox to find out how the folders break down with this
  afxutil -sql "SELECT FOLDER, COUNT(*) FROM oldmailbox GROUP BY FOLDER"
which will count how many messages are in each folder ID

You can also use the separate AFXviewer utility to examine the mailbox

You can undelete messages by moving them to the Inbox with
  afxutil -sql "UPDATE oldmailbox SET FOLDER = 0 WHERE FOLDER = 65535"

You can then use the Console 'move' tool to select and move the messages back to the oldmailbox.


Moving messages before a specific date

Q: "After the removal of all spam I want to create an archive AFX file  using AFXUTIL. Could you give me an SQL string to use with AFXUTIL for "move all messages before 30-03-2018 to an other archive.afx file"?

afxutil -create archive.afx
afxutil -sql "INSERT INTO archive SELECT * FROM mailbox WHERE CDATE <
TIMESTAMP'2018-03-30 00:00:00'"
afxutil -sql "DELETE FROM mailbox WHERE CDATE > TIMESTAMP'2018-03-30 00:00:00'"

How to manually restore a(n AFX) database:

Go to the backup location and you will find copies of all the data files (configuration, mail, etc). Simply copy these over the existing files. The only files that may need to be modified are the mail (.afx) files. If you did not check the Quick Restore option in the backup configuration, the .afx files will have been backed up without indices. Use the afxutil application to prepare these files for use.

Tip: 
 AFX Viewer
The AFX Viewer utility has a GUI interface that makes it possible to index AFX message stores without using the Command Line . Read more and get the AFX Viewer here...

 

You can easily re-create the indices using afxutil. At the command prompt enter:
afxutil mailbox.afx
which will perform various maintenance functions including reconstructing the missing indices so it can be browsed.

How do you prepare every .afx file in a directory? Simple, from the command prompt...

cd "\Program Files\Mailtraq\database\mail\inbox\" for %n in (*.afx) do "c:\Program Files\Mailtraq\AfxUtil.exe" %n

 

Multiple message delete

Q: "I have a need to search for a particular message in multiple mailboxes  and then delete the messages. How can I do that?"

Assuming the messages all share the same subject line, it should be possible to use:

afxutil -sql "DELETE FROM mailboxname WHERE HSUBJECT = 'subject here'"

For an entire directory you can do

for %a in (*.afx) do afxutil -sql "DELETE FROM %~na WHERE HSUBJECT =
'subject here'"

(%~na removes the .afx file extension)

If the subject is inconsistent or not unique to this message, you can find
out the MD5 hash of the message id and use it against the MSGID field. 

Tip:
Any online MD5 encoder will get you the correct text: look here...

Index/Structure repair  and Compress

      Restructure and optionally compress an AFX file
      Use this function on backup files that do not have
      a valid index and for files that have damaged indices

  [-compress] filename.afx
 


AFXUtils Available Field Names

ID, HSUBJECT, HFROM, HTO, CDATE, MSGSIZE, FLAGS, FOLDER, KEYTXT, SYMBOLS, MSGID, THREADID, MSGDATA, KEYWORDS

 

 

 


Keywords: kb troubleshooting
Mailtraq Highlights...
 SMTP Server     Mailtraq SMTP email server video IMAP Server     Mailtraq IMAP email Server video
 POP3 Server     Mailtraq POP3 email server video Proxy Server     Mailtraq proxy email server video
 Webmail Server     Mailtraq webmail email server video Mailing-list Server     Mailing list email server video
 Groupware Services     Mailtraq groupware email services video Spam and Virus control     Spam and virus control email server video

 

   Copyright © 2003 - 2011 Enstar Ltd, Enstar LLC & Fastraq Ltd. All rights reserved. Privacy policy.
   Mailtraq® is a registered trademark of Fastraq Limited.