Report Database                                Download Files

The Report database is a simple Access database that has a lot of queries that aggregate data based on "group by" SQL select queries, VBA functions and database unions. Depending on the logic that is used (which I'll explain below) the aggregates have been split between sent , received ,internal , external, time and date with a separate query for each. Total aggregate queries are based on the separate aggregates depending on how data is to be sorted and displayed . The 4 main tables in the database are

TrackinglogRaw table which contains all the raw tracking log data that was added by the population scripts.

Domains table 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).

Qdatfilter table this table was a solution to a problem I had with being able to change the time period the reports would look at. Because I have multiple queries based on other queries you can't use a "where" clause to set the time scope of the query, so the solution that I  used was all the queries in the database (bar a few) are all based on one central query called trackinglogs. This tackinglog query retrieves data from the trackinglograw table based on a "where" clause that does a sub-query of the qdatefilter table to work out the lower and upper date\time range to show. So when the report pages run the first thing they do is update the qdatfilter table with the date range they want to show in the page before they run the aggregate queries . Because these reports are usually only going to be used  by one person at a time this has worked out to be an okay way of solving the problem.

DateGraph table is a table that contains static setting for hours for the graph aggregate queries

Database aggregate logic

*Important note all the total aggregate queries are based on received email addresses if you have a mailbox that sends mail only it may not appear in the report unless it has at least received one email.

The logic I've used to break data into Internal and External data as well as sent and received email is as follows. (This logic is not right or wrong its just one way of looking at the data) A VBA function is used to separate the email domain from the email address by retrieving all the character after the @ sign.

Internally Received email is determined by first only looking at eventid 1028 (eventid 1020 is only logged when email is being transferred to another server) and checking to see if the domain of the sender and receiver where in the domains table.

Internally Sent email is determined by checking to see if the domain of the sender and receiver where in the domains table.

* Note with this internal logic one email sent internally to a user on the same server is counted twice once as a sent email and once as a received email. It all gets down to how you interprete email traffic. (if your going to store it twice you might as well count it twice). The other point to note is that if you are running this script on a connector server (such as a bridgehead or front-end server) nothing will be counted in the Internal Received email because only 1020 events will be stored in the logs

Externally Received email is determined by checking if the recipient email domain is in the domain table and the sender address domain is not in the domain table.

Externally Sent email is determined by checking if the recipient email domain is not in the domain table and the sender address domain is in the domain table.

Total Size aggregates are show in megabytes if you have a lot of mail volume you may want to change the divider in some of the queries from 1048576  to 1073741824 which will then show the total size aggregate in Gigabytes.

There are five main aggregate queries in the database which are display-by-date, display-time-use, display-total, graph-all-date and graph-all-time.

Database size and archiving

If you have a high volume of mail traffic or you are going to keep a long log history of data in the tracking log database you may have issues with the size of the database affecting the speed at which the aggregate queries run. In this case you are better looking at a proper RDBMS like SQL server where you can store data more efficiently and use proper database indexes to optimize all the queries.  Normally you should look at archiving data out of the database every couple of months depending on the size growth its also best to do a compact of the database at the same time.

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).

Download Files