Tech Notes

Tech Notes Resources StreamCatcher

How to use Firebird SQL to analyze Microsoft IIS log data (FBPrepLog)

Writing directly from IIS to Firebird SQL

We tried the following on Win2000, IIS 5, and it did NOT work. It seems like it should work, so we are leaving the details here for anyone interested in trying.

Some URLs to reference:

SQL to build the InternetLog table with Firebird

DROP TABLE INTERNETLOG;

/* ---------------------------------------------------------------------- */
/* Script generated with: DeZign for Databases v4.2.0                     */
/* Target DBMS:           Firebird 2                                      */
/* Project file:          Win2000_IISLog.dez                              */
/* Project name:                                                          */
/* Author:                                                                */
/* Script type:           Database creation script                        */
/* Created on:            2009-07-24 07:46                                */
/* ---------------------------------------------------------------------- */


/* ---------------------------------------------------------------------- */
/* Tables                                                                 */
/* ---------------------------------------------------------------------- */

/* ---------------------------------------------------------------------- */
/* Add table "INTERNETLOG"                                                */
/* ---------------------------------------------------------------------- */

CREATE TABLE INTERNETLOG (
    CLIENTHOST VARCHAR(255),
    USERNAME VARCHAR(255),
    LOGTIME TIMESTAMP,
    SERVICE VARCHAR(255),
    MACHINE VARCHAR(255),
    SERVERIP VARCHAR(50),
    PROCESSINGTIME INTEGER,
    BYTESRECVD INTEGER,
    BYTESSENT INTEGER,
    SERVICESTATUS INTEGER,
    WIN32STATUS INTEGER,
    OPERATION VARCHAR(255),
    TARGET VARCHAR(255),
    PARAMETERS VARCHAR(255)
);

Importing IIS Logs into Firebird SQL

While this has been made reasonably easy with Microsoft SQL Server, it does not seem that anyone has done it yet for Firebird SQL.

One might suspect that a good first step is to get rid of the comment lines in the log file. Microsoft offers a PrepWebLog.exe utility for this.

Another good step is to figure out the EXTERNAL file format definition, and then to do the equivalent of a bulk insert per these hints.

.... and all that has been DONE as of 01-Aug-2009, and the resulting source is available, free, with a Creative Commons license, in Code Central, Reference CodeCentral: ID: #27086, Utility for importing IIS logs into Firebird. The utility was tested in Delphi 7 and Delphi 2010 and probably works in all versions in between.

contributed by Ann Lynnworth on 18-Aug-2009

Ask a question or make a suggestion about this topic