There are many free offerings on the web for managing and viewing log files, e.g.  Analog

Whilst these are very useful tools which produce highly effective output, there are times when it's good to study the raw log files.

Many ISP's make raw log files available for a user's web-space. These are most commonly CLF (Common Logfile Format) - Apache or WC3 Extended - IIS.

However,  in order to make sense of the log files it's necessary to strip out unwanted fields and later all unwanted records - for example, your own visits, images etc etc.

Here is a 'belt & braces' approach for both IIS and Apache log files.

Which fields you choose to include is down to personal preference but here's one possibility:

IIS

Name the log file:       logfile.txt

Remove the characters #Fields:  in row 4 (including the space after the : character). The file-header should now be as follows:

Create a new blank  Access database to be used for the imported data:

                  On the File menu select  Get External Data - Import - logfile.txt

                  Select delimited - next - delimiter space - no text qualifier - next

                  Select in a new table and click advanced button

                  Name the 6 fields in order Time; IP; Method; Page; Status; Referrer

                  Save the import specification with the default name, as shown below:

 

Complete the import process naming the table logfile. The table should look like this:

Create a macro to be used for importing future logfiles

Running the import_logfile macro will import logfile.txt from the designated directory and create the logfile table. If you fail to delete the table after each use, records will be appended to it.

You will probably now want to strip extraneous records from the logfile table. Here's a sample design:

When the query is run it will delete the unwanted records.  You now have various options for saving the logfile records. Here are a couple of possibilities:

               1. Retain the data as an Access table - right-click on the logfile table and select 'copy'
                   followed by 'paste'. Choose an appropriate name for the new table.

               2. Export the data from the logfile table in your preferred format - e.g. 010106.rtf

Don't forget to delete the logfile table !

Apache

Preparing the log files for import to Access is a little more complex

 

Using 'find and replace' in a text editor do the following:.

                    find  - - [    replace with no characters

                    find yyyy: replace with yyyy followed by space (where yyyy = current year)

                   find ] "  replace with ** followed by space

Scan the file and, if present, remove any anomalies, e.g. data between IP and date fields

Now import the file into Access (as described above for IIS) with space delimiter and no text qualifier - select new table and click on Advanced

Rename the following fields:   Field1 as IP; Field3 as Time; Field5 as Method; Field6 as Page; Field8 as Status; Field10 as Referrer, and check skip for all other fields.

Click on 'Save As' and save the Logfile Import Specification with the default name. Click OK. Complete the import process giving your table an appropriate name

Check the table - it should look like this:

Create an import macro following the same procedure as described above for IIS

Finally, create a query to be used for stripping the extraneous records (see IIS above). Note that the fields are in a different order.

Return to Projects