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.  The 6 main tables in the database are

Logonsnap1 Table which contains all the raw Logon data that was added by the population scripts. 

Logonsnap Table This contains the raw Logon data from the previous time the script ran.

LogonsRaw Table This contain the filtered logons and Logoff added from the 2 action queries addlogon and addlogoffs.

OutlookVersions this contains all the version information and notes about Outlook Logons. This information was taken from CDOLive which is about the most up to date source of this information I could find. This information will constantly change as new versions of Outlook are released and new patches are released.

QdateFilter  this table was a solution to a problem I had with being able to change the time period  and client version 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 LogonRawFiltered. This LogonRawFiltered query retrieves data from the LogonsRaw 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 and also which client version to display. So when the report pages run the first thing they do is update the qdatefilter table with the date range and Client version 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 View and Aggregate logic

If you have a look inside the database you'll notice their are a lot a views and depending on how you look at it you may find that a lot of the time these views will return no records when trying to access them via Access. This is because to filter on Client versions I'm using wildcard characters stored in the Qdatefilter table and because of the differences between ADO wildcards and Access Wildcards a lot of the views I've used in the database don't show records when viewing them via the Access user interface.

Describing what all the views do would take too much time and pages mostly the logic is pretty straight forward, some of the code to work out logon durations is cumbersome its mainly that I couldn't work out a better way of calculating and displaying this type of thing. For records to be included in the audit views of the database there must be an associated logoff record for each logon record. These association are based on the logon time which once created does not change until the user has logged off and then logged back on.

Database size and archiving

Depending on how many users are on the server you are tracking and how long you are going to keep a history of logons in the 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).