WebStats.mdb - Microsoft Access 2003 database to import CLF website log files and report statistics

WebStats.mdb is a Microsoft Access 2003 database for analyzing raw web server access logs that are in Combined Log Format (CLF). It is not designed to be an all-in-one solution to replace Webalizer or other statistics programs, but it does:

• Generate a few specific reports and statistics that appear to be difficult or impossible to generate with Webalizer.
• Provide the flexibility of having the data stored in a general-purpose database. You can easily create new custom filters and reports as you need them.

This is the database I use for web statistics reports. I republish it once in a while with whatever new features I've added.

Most of the discussion and instructions below are to help you if you want to see the same type of information that I look at, by using the same pre-designed queries that I use. However, the database's main feature is that it can import CLF log files. Once you've done that, you can, for example, delete all my queries and design whatever new ones you want. 

It's not a "turnkey" system. Familiarity with Access and knowledge how to use it is needed.

Click here to download webstats.zip (59 KB). It contains:

• WebStats.mdb -- the database in Access 2003 format.
• WebStats.doc -- this page of instructions in Word 2003 format.
• Copying.txt -- the GNU GPL license.

Changes in Version 2.0 (3/12/2007):

1. CDate is used to interpret the CLF date/time string. CVDate used in Version 1.0 didn't properly handle date calculations when the log contained entries spanning multiple years.
2. Two fundamental queries now request start and end dates so you can obtain a report for a specific time period. All queries based on those base queries now also request start and end dates.
3. There are several new queries and 1 new form.
4. You should be able to import your Log table from a Version 1 database into Version 2 with no difficulties.

The questions the database is supposed to answer are:

1. How many real, human, people are looking at my site?
2. What real, HTML, pages are they viewing?

Real People

A server log file shows all the requests for files that were made to the server. It includes all the requests by your site visitors, intermingled with all the requests by automated crawlers and by you, the site owner. If your site is new or has a low amount of traffic, the large number of accesses by crawlers and bots and by yourself during building and testing may make it difficult to answer the question, "How many real, human, people are actually looking at my site?" So the first purpose of this database was to allow filtering out web crawlers and yourself so only real human visitors are reported.

Real Pages

The server also logs every request for every file. If an HTML page contains references to 50 small .gif files used on its navigation bars, then every request for that page will generate 51 log file entries. The second purpose of this database was to allow defining what is a Real Page, and filter out the rest.

Both of these things can be done to some degree with Webalizer when you run it locally on your own computer and customize its .conf file. It generates more useful reports locally than it does when run on a remote server where customization of the .conf file is not allowed.

However, even run locally, some of the reports I wanted to see would:

1. require modifying Webalizer filters to get the desired result for one report but with unwanted side effects for other reports generated during the same run.
2. require creating multiple Webalizer .conf files for different purposes, with the difficulties of maintaining multiple .conf files when modifying settings common to all of them.

So, to get more flexibility, I created this database.

Instructions for use:

1. Download your raw access log from your server and unzip it, if necessary.
2. Rename the log file so its extension is .txt (.csv, .tab, or .asc would also be ok)
3. In WebStats.mdb, click File > Get External Data > Import. 
4. Select Files Of Type: Text Files, navigate to the file, select it, and click Import.
5. At the Security Warning about the Microsoft Office ACWZLIB.MDE wizard (which is a built-in module that Access should probably know enough to trust), click Open.
6. In the Import Text Wizard dialog, click Delimited.  Next...
7. Select Delimiter = space, and Text Qualifier = ".  First row does not contain field names.  Next...
8. Store your data in an existing table: Log.
9. At the next page, click Finish. You can display Help if you want to, but you don't want the table analyzed.

There will frequently be import errors, saved to a separate table, which you will discover are truncation of Referrer fields that were longer than 255 characters. This can be resolved by making Referrer a Memo field, but that would make it a non-indexable field, so I didn't do it.

Customization is required before use

Examine the following queries in Design View before using them:

1. The RealPagesByRealPeople query requires customization:

2. The Server Error Report query also has a couple of criteria where you might want to make modifications regarding which files you want to exclude and which server result codes you consider errors or not.
3. You should look at all the other queries, too. Since this is the database I actually use, it is likely I've set a number of criteria that won't suit your purposes.

Database contents:

Tables:

Log Table Fields:

Queries

The most useful ones are highlighted in yellow.

The queries that have {} around their descriptions are data sources for other queries, and are otherwise not interesting.

Form