ISA Server 2004 Supports 3 Types of Logging. File based, Integrated MSDE or ODBC/SQL Server. The downsite of the Filebased and MSDE based solution is its limited capacity, the additional load to the ISA Server and the possible security risk. (You should store the logs of your ISA on a different and additionally secured machine…)

So, i decided to use the ODBC / SQL Server based logging. But how to set it up? ISA Server does create its logging tables in the target ODBC or SQL Server. And i cant find any SQL Scripts for easy table creation. Not even on g00gle. :-/

I ve tried to copy the table Layout from the internal ISA MSDE instance, but the table layout is not the same. ISA uses a different table schema. One for the MSDE one for ODBC / SQL Server based logging. (btw: the internal MSDE instance is named msfw. Connect with Windows Auth as Admin to “(local)\msfw” on the ISA Box.)

The only way i saw, was to do a little bit of SQL Server auditing to catch the hole communication between ISA and SQL Server. To do this, i ve used the MS SQL Profiler to log every SQL statement ISA server sends to the SQL server. Based on the results i ve build up two tables which can handle the isa server logs:

CREATE TABLE [FW] (
[servername] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[logTime] [datetime] NULL ,
[logDate] [smalldatetime] null, –Added by GS / 27.08.2006
[protocol] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[Source] varchar(200) NULL , — Added by GS / 27.08.2006
[SourceIP] [bigint] NULL ,
[SourcePort] [int] NULL ,
[DESTINATION] varchar(200) NULL , — Added by GS / 27.08.2006
[DestinationIP] [bigint] NULL ,
[DestinationPort] [int] NULL ,
[OriginalClientIP] varchar(32) null, –changed from BIGINT to varchar by GS / 27.08.2006
[SourceNetwork] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[DestinationNetwork] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[Action] varchar(100) NULL , — Changed by GS. was smallint, now: varchar! / 27.08.2006
[resultcode] [int] NULL ,
[rule] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[ApplicationProtocol] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[Bidirectional] varchar(100) NULL , –Changed by GS. was smallint, now varchar / 27.08.2006
[bytessent] [bigint] NULL ,
[bytessentDelta] [bigint] NULL ,
[bytesrecvd] [bigint] NULL ,
[bytesrecvdDelta] [bigint] NULL ,
[connectiontime] [int] NULL ,
[connectiontimeDelta] [int] NULL ,
[SourceProxy] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[DestinationProxy] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[SourceName] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[DestinationName] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[ClientUserName] [varchar] (514) COLLATE Latin1_General_CI_AS NULL ,
[ClientAgent] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[sessionid] [int] NULL ,
[connectionid] [int] NULL ,
[Interface] [varchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[IPHeader] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[Payload] [varchar] (255) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [Web] (
[ClientIP] [bigint] NULL ,
[ClientUserName] [nvarchar] (514) COLLATE Latin1_General_CI_AS NULL ,
[ClientAgent] [varchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[ClientAuthenticate] [smallint] NULL ,
[logTime] [datetime] NULL ,
[service] [smallint] NULL ,
[servername] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[referredserver] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[DestHost] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[DestHostIP] [bigint] NULL ,
[DestHostPort] [int] NULL ,
[processingtime] [int] NULL ,
[bytesrecvd] [bigint] NULL ,
[bytessent] [bigint] NULL ,
[protocol] [varchar] (12) COLLATE Latin1_General_CI_AS NULL ,
[transport] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[operation] [varchar] (24) COLLATE Latin1_General_CI_AS NULL ,
[uri] [varchar] (2048) COLLATE Latin1_General_CI_AS NULL ,
[mimetype] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[objectsource] [smallint] NULL ,
[resultcode] [int] NULL ,
[CacheInfo] [int] NULL ,
[rule] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[FilterInfo] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[SrcNetwork] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[DstNetwork] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[ErrorInfo] [int] NULL ,
[Action] [smallint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [Web] (
[ClientIP] varchar(32) NULL , –was BIGINT
[ClientUserName] [nvarchar] (514) COLLATE Latin1_General_CI_AS NULL ,
[ClientAgent] [varchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[ClientAuthenticate] varchar(100) NULL , –was smallint
[logTime] [datetime] NULL ,
[logDate] [smalldatetime] null, –Added by GS / 27.08.2006
[service] varchar(100) NULL , — changed from smallint to vharchar GS/27.08.2006
[servername] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[referredserver] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[DestHost] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[DestHostIP] varchar(32) NULL , — was BIGINT
[DestHostPort] [int] NULL ,
[processingtime] [int] NULL ,
[bytesrecvd] [bigint] NULL ,
[bytessent] [bigint] NULL ,
[protocol] [varchar] (12) COLLATE Latin1_General_CI_AS NULL ,
[transport] [varchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[operation] [varchar] (24) COLLATE Latin1_General_CI_AS NULL ,
[uri] [varchar] (2048) COLLATE Latin1_General_CI_AS NULL ,
[mimetype] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[objectsource] varchar(100) NULL , –was SMALLINT
[resultcode] [int] NULL ,
[CacheInfo] [int] NULL ,
[rule] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[FilterInfo] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[SrcNetwork] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[DstNetwork] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[ErrorInfo] [int] NULL ,
[Action] varchar(100) NULL — changed from smallint to varchar by GS / 27.06.2006
) ON [PRIMARY]
GO