Script to create a count of email needed

This forum is for discussing Mailtraq's Scripting features. Get help with Mailtraq scripts, templates and external ActiveX scripting.

Script to create a count of email needed

Postby SilvrT » Wed Sep 23rd, 2015 4:48pm

Hello, nube here and just learning the power of Mailtraq.

I found some of the info I need and it works very well --> viewtopic.php?f=15&t=1806

As I am totally unfamiliar with the scripting, it would be much appreciated if someone could show me how to modify that script so it breaks it down to individual folders.

I'd also like to specify that the output is limited to a specific date range.

And how would I save the output to a text file?

A lot to ask for a nube eh? (ya, I'm Canadian lol)

Thanks in advance.
SilvrT
 
Posts: 4
Joined: Wed Sep 23rd, 2015 4:39pm

Re: Script to create a count of email needed

Postby Martin Clayton » Thu Sep 24th, 2015 1:36pm

Hi SilvrT,

I've taken the liberty of ignoring the .asp gubbins and moving to a native script. Standard script files have an .mtq extension, live in 'database\scripts' and are scheduled at Options -> Scripts and Templates -> [ Scripts | Automated Scripting ]. I've not noticed until now but the build I'm using (beta 2.18.337.3578) is missing the 'Run' button on the Scripts tab - so I've been manually setting it as a Scheduled Event, one minute ahead for each run.

The script doesn't restrict results by date-range. For that, you could try a filter with QueryFolders (line 47) acting on '$timestamp' but if that doesn't support suitable logical or comparison operators another way would be to iterate over a date-sorted list of messages.

Code: Select all
<@LANGUAGE=Javascript@>
<%

// slotstats.mtq (database\scripts)
// v0.1 2015-09-24

var logfile = 'E:\\slotstats.txt';   // any path must use '/' or '\\' e.g. 'C:\\reports\\mtq\\slotstats.txt')

function InitialiseOutputFile(name_path) {

    // return text stream object for writing
    var fso         = new ActiveXObject("Scripting.FileSystemObject"),
        iomode      = 8,        // read = 1, write = 2, append = 8
        can_create  = true,     // file creation allowed
        tristate    = 0,        // -2 default, -1 Unicode, 0 ASCII
        text_stream;

        try {
            text_stream = fso.OpenTextFile(name_path, iomode, can_create, tristate);
        } catch (e) {
            // send message to Mailtraq log (defaults to 'alert' message class)
            Server.Mailtraq.Log("slotstats: ! script error attempting to open '" +  logfile + "'");
            text_stream = null;
        }

        return text_stream;

}

var output = InitialiseOutputFile(logfile);
if (output) {

    output.WriteLine("Starting...\r\n");

    // get all mailboxes (use GetAllMailslots() here to include archives, mailing lists, etc)
    var mailslots       = Config.GetAllMailboxes().split(","),
        mailslots_count = mailslots.length,
        i,
        j;

    for (i = 0; i < mailslots_count; i++) {

        output.WriteLine(mailslots[i] + ' (' + (i + 1) + ')\r\n-------------------------------------');

        // count number & size of messages by folder
        var mailslot       = Config.GetMailslot(mailslots[i]),
            folderInfo     = mailslot.QueryFolders().split(","),
            folder_count   = folderInfo.length,
            folderName     = '',
            folderMsgCount = 0,
            folderMsgSize  = 0,
            slotMsgCount   = 0,
            slotMsgSize    = 0;

        // QueryFolders() returns "[indent],[folder-id],[count],[new-count],[size],[name]" for each folder
        for (j = 2; j < folder_count; j += 6) {
            folderMsgCount = (+folderInfo[j]);
            folderMsgSize  = (+folderInfo[j+2]);
            folderName     = folderInfo[j+3];
            output.WriteLine('- ' + folderName + ': count; ' + folderMsgCount + ' size; ' + folderMsgSize);
            // cumulative counts (mailslot level)
            slotMsgCount += (+folderMsgCount);
            slotMsgSize  += (+folderMsgSize);
        }
        output.WriteLine('Total: count; ' + slotMsgCount + ' size; ' + slotMsgSize + "\r\n");
    }

}
%>


Hopefully, this will get you off the ground but post again if you need help.

Sorry to hear about Canada.

;)
User avatar
Martin Clayton
Expert User
 
Posts: 529
Joined: Sat Jan 15th, 2005 8:20am
Location: London, UK

Re: Script to create a count of email needed

Postby SilvrT » Thu Sep 24th, 2015 1:58pm

Thanks Martin ... I'll "play" with that. Interesting though I have just purchased the current release and I don't have a Run button either.
SilvrT
 
Posts: 4
Joined: Wed Sep 23rd, 2015 4:39pm

Re: Script to create a count of email needed

Postby SilvrT » Thu Sep 24th, 2015 5:52pm

OK, while I have no problem writing VBA code in Outlook, Excel, or Access to accomplish what I need, I'm totally lost with Mailtraq's scripting. I can understand what's going on with example scripts as supplied here but when it comes to modifying them using some other method such as QueryFolders for $timestamp, I'm totally lost and there just doesn't seem to be much for examples of use anywhere in the scripting reference.

Martin, that link to QueryFolders doesn't help much at all I'm sorry to say. Reference material is generally nothing more than that :( ... it tells a person what it is -or- what you "can do" with it ... what is needed are real world examples of use.

Maybe I'm not looking in the right places?? :?
SilvrT
 
Posts: 4
Joined: Wed Sep 23rd, 2015 4:39pm

Re: Script to create a count of email needed

Postby Martin Clayton » Fri Sep 25th, 2015 11:18am

SilvrT wrote:OK, while I have no problem writing VBA code in Outlook, Excel, or Access to accomplish what I need, I'm totally lost with Mailtraq's scripting. I can understand what's going on with example scripts as supplied here but when it comes to modifying them using some other method such as QueryFolders for $timestamp, I'm totally lost and there just doesn't seem to be much for examples of use anywhere in the scripting reference.

True, examples are scarce, especially with the javascript based scripting - which, imo, is a big step forward from Mailtraq's proprietary language (as seen in the templates). However, pre-javascript, there were some highly active, very generous contributors, as evidenced by the well illustrated help file.

SilvrT wrote:Martin, that link to QueryFolders doesn't help much at all I'm sorry to say. Reference material is generally nothing more than that :( ... it tells a person what it is -or- what you "can do" with it ... what is needed are real world examples of use.

Sure, you were in at the deep end and I would have taken the script further if there was time. At least, hopefully anyway, you've seen a working example of QueryFolders() which is an efficient way of grabbing per-folder stats.

SilvrT wrote:Maybe I'm not looking in the right places?? :?

I knew next to sod all about javascript when I started Mailtraq scripting and I found the following really useful: Core JavaScript Reference 1.5 & Core JavaScript Guide 1.5 (I have local html copies of these but I think they were originally authored at Netscape), the one-and-only Mailtraq Script Reference, some snippets on this forum and the best examples of all, webmail. Anything webmail can do is up for grabs -- for example, I'm guessing you can search for messages on date range and if so, the source is in the open. Btw, if you want to explore javascript in more depth then http://javascript.crockford.com/ is highly recommended.

But back to the script; I've never tried using filters with QueryFolders() and it may well be that it's not suitable for date ranges - in which case, there will be other ways of doing it (but probably less efficient). I'll try and have a proper look next week but meanwhile fire away with any questions.
User avatar
Martin Clayton
Expert User
 
Posts: 529
Joined: Sat Jan 15th, 2005 8:20am
Location: London, UK

Re: Script to create a count of email needed

Postby SilvrT » Fri Sep 25th, 2015 11:33am

Martin, thanks a ton for all your help so far. Please don't go out of your way on this but I sure would appreciate anything you might come up with. At this point I'm in no rush, I have until the end of October before I really need this but as I mentioned, I can get what I need via Outlook VBA when I connect using IMAP but this is not my preferred solution.

-Rick
SilvrT
 
Posts: 4
Joined: Wed Sep 23rd, 2015 4:39pm

Re: Script to create a count of email needed

Postby Martin Clayton » Thu Oct 01st, 2015 6:14pm

Ok, looking at the Script Reference suggests that QueryFolders() filters only act on To, From, Subject and filename -- sorry, no $timestamp feature -- so we'll have to find another way...

Webmail's mboxsearch.asp shows an undocumented feature of SearchContent() where parameters 7 & 8 are start and end dates but the method doesn't seem suited for a general query, without text to search for.

So, next, QueryMessages(), returns a list of the message properties we're interested in -- size and timestamp (time of arrival at the mailslot, not the values in the fickle Date header) -- for every message in a specified folder. Then, it's just a question of running over the list and collecting stats per folder for messages which are in-date. Inevitably, that's going to be much slower than grabbing summary statistics with QueryFolders() but brief testing suggests that it doesn't unduly affect normal operations. It's worth watching though and may best be scheduled for off-peak hours.

If performance does prove to be an issue there's plenty of scope to optimise: 1) the message list is sorted on date but there's no attempt to break the loop when non-matches start occurring 2) use of new is quite expensive in javascript and the date test creates new Date objects for every message, so, i) VDateFormat may be a better vehicle and ii) rather counter-intuitively, regexp string comparisons may be quicker than numerical date comparisons.

Another server-side possibility is SQL; I don't think you can access the SQL Console in current builds but afxutil.exe could be useful at the command line.

Anyway, not fully tested but see how you get on...
Code: Select all
<@LANGUAGE=Javascript@>
<%
/*  slotstats.mtq (database\scripts)

    report number & size of messages by folder for specified date-range
    v0.2 2015-10-01
    http://forum.mailtraq.com/viewtopic.php?f=15&t=2149
*/

// globals

var CFG = {

        // output file; path separator must be '/' or '\\' e.g. 'C:\\reports\\mtq\\slotstats.txt')
        outfile          : 'E:/slotstats.txt',
        // true to append, false to overwrite
        outfileAppend    : true,

        // display stats for messages in date range - supports formats of Date.parse() in theory
        // but it's *much* safer to use month words & 4-digit years. Time defaults to 00:00:00 (000)
        dateStart        : '01 September 2015',
        dateEnd          : '30 September 2015 23:59:59',   // empty string for 'now'

        // show/hide folders with no messages
        showEmptyFolders : true

    },
    TIMESTART = new Date(),  // timer for script execution duration
    OUTPUT;                  // file handle

// functions

function InitialiseOutputFile(namePath, append) {

    // return text stream object for writing
    // namePath - file name & path
    // append - (Boolean) append or overwrite file

    var fso = new ActiveXObject("Scripting.FileSystemObject"),
        ioMode = (append) ? 8 : 2,    // read = 1, write = 2, append = 8
        canCreate = true,             // file creation allowed
        triState = 0,                 // -2 default, -1 unicode, 0 ascii
        textStream = null;

        try {
            textStream = fso.OpenTextFile(namePath, ioMode, canCreate, triState);
        } catch (e) {
            // send message to Mailtraq log (defaults to 'alert' message class)
            Server.Mailtraq.Log("slotstats: ! script error attempting to open '" +  namePath.toString() + "'");
        }

        return textStream;

}

function leftPad(target, width, padding) {

    // prepend target with padding (default "0") until (at least) the desired length

    var str      = '' + target,
        minWidth = (+width),
        pad      = '' + ((arguments.length < 3 || padding.length === 0) ? "0" : padding);

    if (pad.length) {
        while (str.length < minWidth) {
            str = pad + str;
        }
    }

    return str;

}

function RenderTimeStamp(dateObject) {

    // return log-friendly date time; now() unless a dateObject is provided

    var dateObj = (arguments.length === 1 && (dateObject instanceof Date)) ? dateObject : new Date(),
        year    = dateObj.getFullYear(),
        month   = leftPad(dateObj.getMonth() + 1, 2),     // Date() month numbers are 0-11
        day     = leftPad(dateObj.getDate(), 2),          // leftPad adds leading zero for width 2
        date    = day + "/" + month + "/" + year,
        time    = dateObj.toLocaleTimeString();

    return date + " " + time;

}

function GetDateFromTimeString(timeString) {

    // return date object from a string format 'yyyymmddhhnnss' as
    // used by the $timestamp token in a mailslot.QueryMessages() template

    var year    = (+timeString.substr(0, 4)),
        month   = (+timeString.substr(4, 2)) - 1, // Date() month numbers run 0 to 11
        day     = (+timeString.substr(6, 2)),
        hours   = (+timeString.substr(8, 2)),
        minutes = (+timeString.substr(10, 2)),
        seconds = (+timeString.substr(12, 2)),
        dateObj = new Date(year, month, day, hours, minutes, seconds);

    return dateObj;

}

function main() {

    var dateObjStart = new Date(CFG.dateStart),          // search range date-objects
        dateObjEnd,                                      //
        mailslots = Config.GetAllMailboxes().split(","), // comma separated list of mailboxes (not mailslots), split for array
        mailslotsCount = mailslots.length,               // number of mailslots
        qmTemplate = "$size\t$timestamp",                // template for QueryMessages() to return '[bytes][tab][yyyymmddhhnnss]'
        mailslot,                                        // Mailtraq mailslot object
        folderInfo,                                      // [indent],[folder-id],[count],[new-count],[size],[name] for all folders
        folderInfoCount,                                 // folder attribute count, divide by six for number of folders
        msgData,                                         // result of QueryMessages(): size & timestamp for all messages in the folder
        msgDataCount,                                    // number of messages (or size/timestamp pairs)
        msgSizeTime,                                     // message level size/timestamp pair
        msgSize,                                         // message size
        msgTime,                                         // message timestamp
        msgHitCount,                                     // cumulative count of messages in date-range (folder level)
        msgHitSize,                                      // cumulative size  of messages in date-range (folder level)
        slotMsgHitCount,                                 // cumulative count of messages in date-range (mailslot level)
        slotMsgHitSize,                                  // cumulative size  of messages in date-range (mailslot level)
        i,                                               // mailslot counter
        j,                                               // folder counter
        k;                                               // message counter

    OUTPUT.WriteLine("===============================================" + "\r\n" +
                     RenderTimeStamp() + " Starting search...");

    // empty end date defaults to now()
    dateObjEnd = (!CFG.dateEnd) ? TIMESTART : new Date(CFG.dateEnd);

    // test date validity (not sanity)
    if ( !(dateObjStart instanceof Date) || isNaN(dateObjStart) ) {
        OUTPUT.WriteLine("! Aborting. Failed to parse start date: '" + CFG.dateStart + "'");
        return;
    }
    if ( !(dateObjEnd instanceof Date) || isNaN(dateObjEnd) ) {
        OUTPUT.WriteLine("! Aborting. Failed to parse end date: '" + CFG.dateEnd + "'");
        return;
    }

    OUTPUT.WriteLine("  from : " + dateObjStart.toLocaleString() + "\r\n" +
                     "  to   : " + dateObjEnd.toLocaleString() + " inclusive\r\n" +
                     "===============================================");

    for (i = 0; i < mailslotsCount; i += 1) {

        OUTPUT.WriteLine(mailslots[i] + ' (' + (i + 1) + ' of ' + mailslotsCount + ')\r\n-------------------------------------');

        // QueryFolders() gives folder-ids (+ messages counts & names) which we pass to
        // QueryMessages() which is run on each folder, giving the size & date of every message

        mailslot = Config.GetMailslot(mailslots[i]);
        folderInfo = mailslot.QueryFolders().split(",");
        folderInfoCount = folderInfo.length;
        slotMsgHitCount = 0;
        slotMsgHitSize = 0;

        for (j = 1; j < folderInfoCount; j += 6) {

            msgHitCount = 0;
            msgHitSize = 0;

            // QueryFolders() returns "[indent],[folder-id],[count],[new-count],[size],[name]" for each folder
            //                               0           1       2           3      4      5

            // flag empty folder name
            if (folderInfo[j + 4] === '') {
                OUTPUT.WriteLine("! Warning. Empty folder name found. 'Verify' under 'mailbox properties -> folders' may be worth a go.");
            }

            // if the folder is empty, maybe say so & move on
            if ((+folderInfo[j + 1]) < 1) {
                if (CFG.showEmptyFolders) {
                    OUTPUT.WriteLine("- '" + folderInfo[j + 4] + "': (empty)");
                }
                continue;
            }

            // array of all messages with timestamps & sizes; params: folder-id, first message, max-messages, query, filter, sorting, separator)
            msgData = mailslot.QueryMessages((+folderInfo[j]), 0, (+folderInfo[j + 1]), qmTemplate, null, null, "\r\n").split("\r\n");
            msgDataCount = msgData.length;

            // OUTPUT.WriteLine(' DEBUG: folder-id=' + (+folderInfo[j]) + ' max msg=' + (+folderInfo[j+1]) + ' msgData=' + msgData.toString());

            for (k = 0; k < msgDataCount; k += 1) {
                msgSizeTime = msgData[k].split("\t");      // tab separated list as defined by qmTemplate (size & time)
                msgSize = msgSizeTime[0];
                msgTime = GetDateFromTimeString(msgSizeTime[1]);
                // increment stats for any messages in date range
                if ((msgTime >= dateObjStart) && (msgTime <= dateObjEnd)) {
                    msgHitCount += 1;
                    msgHitSize += (+msgSize);
                }

            }
            // output folder name, count, size
            if (msgHitCount || CFG.showEmptyFolders) {
                OUTPUT.WriteLine("- '" + folderInfo[j + 4] + "': " + msgHitCount + ", " + msgHitSize + " bytes");
            }

            // cumulative counts (mailslot level)
            slotMsgHitCount += (+msgHitCount);
            slotMsgHitSize += (+msgHitSize);
        }

        // mailslot totals
        OUTPUT.WriteLine("Total: " + ((slotMsgHitCount) ? (slotMsgHitCount + ", " + slotMsgHitSize + " bytes") : ("(none)")) + "\r\n");

    }

    OUTPUT.WriteLine("Clean exit at " + (new Date() - TIMESTART) + " milliseconds\r\n");

    return;

}

// action

OUTPUT = InitialiseOutputFile(CFG.outfile, CFG.outfileAppend);
if (OUTPUT) {
    main();
    OUTPUT.Close();
}

%>


EDIT: corrected mailslot total counts
User avatar
Martin Clayton
Expert User
 
Posts: 529
Joined: Sat Jan 15th, 2005 8:20am
Location: London, UK


Return to Mailtraq Scripting

Who is online

Users browsing this forum: No registered users and 2 guests

cron