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