Cool Solutions

GWAttachmentStats



By:

July 17, 2010 10:01 am

Reads: 13439

Comments:10

License:
Free

Download gwattachmentstats

By Bryan Keadle

We have determined that much of our total GroupWise storage is due to attachments that are left in users’ mail boxes. Many of these attachments are dated and can be buried in a user’s Cabinet folder for reference, or still exist as Sent or Trashed item. We can significantly reduce our GroupWise storage space without resorting to global policies to delete or archive messages if we could identify these messages with attachments for the users, and provide them with a report of the messages with attachments and request that they “clean house”.

Though the GWCheck program provided by GroupWise does give the ability to report on mailbox statistics, and find this information, the resulting log does not lend itself to be imported into a spreadsheet or database for reporting purposes.

This utility program, GWAttachmentStats.exe, parses out a GWCheck report generated to report attachment statistics. With an options file defined like this:

Where we are reporting on all messages with attachments larger than 3 MB, it will process the resulting log file into 2 tab-delimited files, SUMMARY.TXT and DETAIL.TXT, which is then easily imported into Excel for reporting purposes. The SUMMARY.TXT gives mailbox statistics for each user, and the DETAIL.TXT provides the information about the messages containing attachments that meet the GWCheck report criteria. We take this:

And turn it into this:

UPDATE: 07-2007

Version 3.0 update:

This newest version is exponentially faster than the original, and can now handle very large gwcheck log files. Features of this version include:

- Handles large log files (tested against 200+ MB gwcheck logs)

- You now have a dialog box on launch to select Summary and/or Detail

- Window title shows start time, and when complete, elapsed time

- Process multiple log files at the same time

- The resulting summary and detail reports now include the PO name processed:

 (ReportType)_(PO)_yyyy-mm-dd-hh-mm.txt
    e.g.:
        Summary_PO-name_2007-06-15-14-07.txt  (started at 2:07pm)
        Details_PO-name_2007-06-15-14-07.txt (started at 2:07pm)

- Excel import now creates a single workbook, with a separate Summary and Detail worksheet tab, formatted, and column totals generated. Subsequent processed files will be added as additional worksheets (gwattachmentstats.xls)

This version of GWAttachmentStats does require Microsoft’s LogParser program/utility. Logparser.exe needs to be located in your search path. Thanks to this excellent, *FREE* utility, what originally took over 40 hours to process a 200MB gwcheck log file now takes a mere 10 minutes (3 minutes for just the summary report)!

Additionally, if you use my companion program, GWPUDBInfo.exe, you can generate a PUDBIndex log file which will translate the pu*.db UserIDs to the originating owner of that “mailbox”. Here’s what you do:

  1. Run GWPUDBInfo.exe against the same PO that you are reporting on with GWAttachmentStats
  2. Make sure the resulting log file, PUDBIndex_(PO name).txt , exists in the same directory as the GWCheck log file you created for GWAttachmentStats to process
  3. Run GWAttachmentStats, and select the PO GWCheck log file. If it finds this index file that matches the PO name you’re processing, it will use it to translate the pu*.db references in the SUMMARY report, like this:

Thanks to Laurie Baker for her patience, feedback, and willingness to test this update against her quite large gwcheck log files (200+MB).

This feedback is what this tool is all about! -

“… we have no size limits in place. We are trying to find out exactly how big people’s mailboxes are since we are moving to an environment where they will not only have limits, but be charged for the size of their mailbox. So your tool is key for us to come up with as accurate as possible projections of cost in order to encourage them to delete.”

Now we can easily report, sort, and trend on user’s email statistics, mailbox sizes, and space taken by attachments!

As a matter of policy, we would request that users save out email attachments that are important, and delete them from their mailbox. Alternatively, there is a handy Formativ Applet by Advansys that allows you to delete the attachment from a message, without deleting the message.

Click here for GWCheck and supporting files.

For you convenience, included in the download package is a GWCheck options file template to get you started. Simply:

  1. Run GWCheck.exe
  2. Selected the provided options file template
  3. change the path to the post office and the po name (and optionally the log file name and attachment sizes parameter) and select Run

UPDATE: 02-2008

Version 3.2 update:

By popular demand, this tool (as of v3.2) will also process ConsoleOne-generated GWCheck logs in addition to the standalone version of GWCheck. English versions, however, is still required.

Also, issue resolved with missing first attachment record for each user.

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)


Categories: Uncategorized

Disclaimer: This content is not supported by Novell. It was contributed by a community member and is published "as is." It seems to have worked for at least one person, and might work for you. But please be sure to test it thoroughly before using it in a production environment.

10 Comments

  1. By:Anonymous

    After todays microsoft updates it no longer asks if I want to import in to excel, only if I want to open with Notebook :(

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    • By:bkeadle

      Sorry for the delay, I only ow discovered these comment areas.

      The tool looks for HKCR\Applications\EXCEL.EXE\shell\Edit\command\[] to know that Excel is installed and prompt you. You *could* manually populate that registry value to restore the feature.

      VN:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
  2. By:augusta

    Really great tool !!! Thanx !!!

    Milan

    VN:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
  3. By:ndbcllc

    I have not been able to successfully use the DRN to find a specific email reported in the detail list. This is necessary when a user has a very complex folder structure and the email is difficult to find, especially an older communication the user has no idea where it resides. Various searches are not successful. So, can the DRN be used to locate a specific email? It is not the same as the Document Number in the advanced find filter fields. I don’t know which version GWattachmentstats I am using but it was installed in Feb or Mar of 2009.
    Thank you

    VN:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    • By:konecnya

      Since you do know the date, and even who it is from;
      Create a Find Results Folder, select the created or delivered date range for the date specified (or from the day before to the day after the message in question). Then in this folder; right click on the column header, select size. then right click on the Size header and Sort Descending. The troublesomely large message(s) will appear at the top.

      This tool for finding the large messages works in any folder and is a great thing to educate the users to using on a regular basis.

      Andy Konecny

      VN:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
  4. By:konecnya

    a sorely needed tool, thank you soo much for creating this
    With it I’m finding many instances of users with over 100MB attachments, so far as high as 860MB, no wonder we’ve been having to expand space constantly.
    Time to train the users of the wonders of the size column.

    a few issues I’ve found
    A) txt is more universally for unformatted Text which will naturally want to open Notepad, MS Word, OO Write, or such unless changes have been made. CSV is more the standard for this sort of thing and is mostly a standard for pointing to MS Excel, OO Calc, or an actual database application. So I’ve just been renaming to .csv to get OO Calc to open up, then I tell it that it is tab and not comma delimited.
    B) as a number of the user names are coming through with quotation marks, but only the starting one makes it through, so the import (at least into OO Calc) gets all messed up. My work around is the open in Notepad2 to search and replace all the quotation marks to nothing. would be nice if gwattachmentstats would do this for us.

    now to find an install of MS Office to see how our output shows in Excel to see if these challenges were just our data or the tiny differences between the Excel and Calc.

    Andy Konecny
    KonecnyConsulting.ca

    MS – Microsoft, as in Microsoft Office
    OO- Open Office, whether form openoffice.org, Novell’s or Oracle’s

    VN:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    • By:bkeadle

      Thanks for your feedback. As for A) Instead of renaming to .csv, what if you rename to .tab? That would imply tab-delimited, and I think Excel/Calc understands that? And as for B) I could probably do that. Let me take a look and if so, I’ll re-post an update.

      VN:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
      • By:konecnya

        A) by default, .tab open in Writer, not Calc but then just tried on a MS Office 2003 system, and it didn’t know what to do with the a .tab file, had to manually point it towards Excel
        B) Excel 2003 has the same problem with those quotation marks.
        C) I see that Excel handles the date info properly while Calc doesn’t, will have to bug the OpenOffice guys about that. I wonder if it would work better if the Date and Time were separate columns?

        VN:F [1.9.22_1171]
        Rating: 0.0/5 (0 votes cast)
    • By:bkeadle

      v3.5 now strips quotes from the User field. Also, Excel importing is handled better.
      Thanks for your suggestion.

      VN:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
  5. By:SAMSoban

    Reports error on running XP or W7

    1031: DirChange: Dir not found/changed

    On line 1355 of O:\wbf\gwattachmentstats.wbt
    In Routine “recentfilesudf”

    Interestingly someone has report an almost identical error in another of this author’s apps “GWAuditReport”, however there has been no comeback on that one.

    Steve

    VN:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)

Comment

RSS