Pivot Table Log File Analyzer (freeware) - Version 1.01
This is an Excel program (visual basic for applications) that creates a pivot table from a log file. If you have Excel 2002 or later, you can click on the IP address in the Pivot table to resolve it. The download file contains the program, a short manual, an Access database, and the smart tag.
Example of a section of the program output
|
IP Address |
Date |
Item Viewed |
Referrer |
Code |
|
62.161.171.49 |
[03/Jan/2006:06:09:53 |
GET /Essays/FMEAFracas.htm HTTP/1.0 |
http://www.google.com/search?hl=en&lr=&q=surgery+fault+tree&btnG=Search |
200 |
|
|
[03/Jan/2006:06:09:55 |
GET /Essays/FMEAFr1.gif HTTP/1.0 |
http://krouwerconsulting.com/Essays/FMEAFracas.htm |
200 |
|
|
|
GET /favicon.ico HTTP/1.0 |
- |
404 |
|
|
[03/Jan/2006:06:18:22 |
GET /Essays/Preven2.gif HTTP/1.0 |
http://krouwerconsulting.com/Essays/Preventability.htm |
200 |
|
|
|
GET /Essays/Preventability.htm HTTP/1.0 |
http://www.google.com/search?q=surgery+fault+tree&hl=en&lr=&start=20&sa=N |
200 |
From this pivot table, one can see that this user looked up the terms "surgery fault tree" in Google and got to an essay on my site. He bookmarked the essay and went back to Google and found (9 minutes later) another essay on my site. He then left my site.
If I click on the IP address, I get the following, which says that this was a hospital in France. Clicking on the IP address works because I have a smart tag.
inetnum: 62.161.171.48
- 62.161.171.55
netname: FR-CENTRE-HOSPITALIER-POISSY-SAINT-GERMAIN
descr: CENTRE HOSPITALIER POISSY SAINT GERMAIN
descr: 20, Rue Armagis
descr: 78100 Saint Germain En Laye
country France
Thus, the program shows for each visitor, what they viewed, how long they stayed, how they got to the site, if they bookmarked anything, and the return code for the operation.
The program also allows one to delete unwanted entries such as search engine visits, navigation buttons*, and selected IP addresses (see screen shot below). After a log file is analyzed, the program allows the data to be added to an Access database, so that analyses over time can be performed. The program has some simple built in queries. If you have Access, you can perform your own queries.
*Remember that for a web page with text and 7 navigation buttons, there will be 8 entries in the log file; one corresponding to the html text and seven others for the navigation gifs.
Download the program. It is a zip file that is configured to unzip all files when double clicked. Installation instructions are provided in the manual.
Options Screen shot.
Programming note - I looked into replacing the Smart Tag with the Dns.GetHostEntry function available in the 2.0 net framework. This could be called from Excel by making a COM callable wrapper using Visual Studio 2005 and would automatically populate the organization name corresponding to the IP address obtained from the log file. The problem is that the results from the Dns.GetHostEntry function are not as informative as results from the Smart Tag and in some cases even fail to resolve the IP address.