Home
Fen
RSS Feeds
Learning Resources
The Information Age
Projects
Network Essentials
Homezones
Bert Lutman
A.W.Jackson

 

more pages

 

Strip log files
DVD to CD
Streaming webcam
Wireless networking
Web matters
Gardening
Woodworking

 

 

 

   

 

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:

 

  Logfields

 

 

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:

 

 

Strip 1

 

 

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:

 

Strip 2

 

 

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

 

 

Strip 3

 

 

Create a macro to be used for importing future logfiles

 

 

 

Strip 4

 

                  

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:

 

 

 

Strip 5

 

 

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:

 

 

 

Strip 6

 

 

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

 

 
 
   

Copyright 2008 [Fen Tyler]