Report database and ASP pages for IMF Tracking logs data   Download Files

The report database was based on my other message tracking logs article, instead of reporting on message flow this version is specifically targeted at SPAM the IMF has detected and archived (or deleted). This script, database and reporting pages are useful for identifying the pattern of SPAM in your organization and can help you answer some of the following questions.

How many Spam Emails has the IMF archived over the past 1,7,14,30 days

What do the SPAM trends look like

How many SPAM emails are deleted by the IMF at different times of the day what is the busy day/time of day for spam.

IP and email address's that send you the most spam

How many SPAM email does each user receive

How it works

The collection script and database work exactly the same as I described in my previous article except that instead of the collection script retrieving events with message tracking logs id 1020 and 1028 it only retrieves events with Message Tracking ID 1039.

Installing and Running the population scripts

There are two hard coded references in these scripts you need to be aware of the first reference is to the database being located on the d:\ of the server. (Erstwhile the database needs to be located there or the script won't function)

strCnxn1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\spamtrack.mdb;"

The Second is the name of the server which you must change to your own server name

strComputerName = "Your Server"

Once these two references have been verified your ready to run the script, There are two versions of the script one is a run-once population script (fullsppopt.vbs) which will capture all the data (regarding SPAM archiving) that is stored in the message tracking logs and insert it into the database, (if you run this any more then once you end up with duplicates in your database). The second version (incsppopt.vbs) is designed to capture data during the day and can be run via a scheduled task (eg schedule for every 15 minutes which corresponds to the way the query is designed to work getting the last 15 minutes form the message tracking logs).

Report Database

Read the report database page from my previous article  for the database to work you must add the email domain you wish to report on to this table in the format "yourdomain.com" (essentially everything after the @ sign of the email address's you are tracking).

Important: For the report pages to work properly the permissions on the database file must allow write access for the anonymous II_USR web server account. (or allow write access for everyone).

Report ASP pages                                                                   

The report pages consist of three different types of pages the first is the aggregate pages which display one of the total aggregate queries in a table. The second type is the drill down page which displays a segment of the database based on input parameters passed in from the aggregate pages via html query-stings. The third page type is a graph page which displays a visual representation of achieved Spam based on a aggregate query. All these pages use ADO  to connect to and query the spamtrack database. There is one hard-coded reference to the report database being located on the D:\ in all pages.

dataConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\spamtrack.mdb" 'make connection

NavBar

All aggregate and graph pages include a NavBar across the top of the page which allows you to change the time scope of the data displayed by the page and also move between different reports. It achieves this using a case statement and some query strings if no query string is passed into the page the default will be set to a 14 days date range . The front end of the page includes some code that inserts the date range  into the qdatfilter table which is how the date range of the queries that run after this are controlled (see previous article for a description of this).

Aggregate Pages

The aggregate page shows one of the total aggregate queries from the database and includes some code to create hyperlinks to the drilldown pages for all the total values.

Drill-Down pages

The drill-down pages query one of the Detail Access queries with input parameters passed in from HTML query-stings (which are passed in from the hyperlink)  to filter down the detail query to the section of the log file that the drill down refers to.

Graph pages

The Graph pages shows a visual representation of Spam Archival across a period of time, to generate the graph itself I've used a 3rd party Java applet which you can download for free from here. The hour graph page shows the number of logons and logoffs grouped by hour, the day graph show this same representation based on days.

Installing and Using the pages

The database file needs to be located on the d:\ of your web server unless you have changed the location in all the asp pages. Also the permissions on the database file must allow write access for the anonymous II_USR web server account. (or allow write access for everyone).  To install the pages you can create a directory under the default website call it something like spamtrack put the asp pages into that directory and you should be ready to go. If you are using Windows 2003 you need to make sure you have enabled ASP pages under the IIS web service extensions.

The other thing that's necessary to use the graph pages is you must download the java chart applet from here .I have no association with the people that wrote this Java applet they currently provide a free download of a branded applet so please respect the usage rights of the author. Once you have downloaded the applet you will have one java class file called barchart.class all you need to do is place this in the same direction as the asp files and it should work okay. To use the graphs all you need then is a browser with at least the Microsoft Java virtual machine (or equivalent)  installed and enabled.

Download Files