Installation

Since 04/10/2010 there is a possibility to run the code in order to do some analysis stuff and store it to a database.

Requirements
  • Microsoft SQL Server Express 2005 or higher
  • Microsoft IIS 6.0 with ASP.NET

Things that work :-)
  • Parsing IIS log files
  • Storing the details to a database via WCF Service

Step-by-Step

  1. Set up a database and run the script down the text
  2. Change the configuration file web.config in the WCF Service project. It looks like this (for me ;-) )
    <connectionStrings>
        <add name="statisticsEntities" connectionString="metadata=res://*/DatabaseModel.csdl|res://*/DatabaseModel.ssdl|res://*/DatabaseModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=ALEXANDER-PC\SQLEXPRESS;Initial Catalog=statistics;Integrated Security=True;MultipleActiveResultSets=True&quot;"
            providerName="System.Data.EntityClient" />
        <add name="statisticsConnectionString" connectionString="Data Source=ALEXANDER-PC\SQLEXPRESS;Initial Catalog=statistics;Integrated Security=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
  1. Add a database entry to the sites (for example: 1, Alexander Seeliger Software, W3SVC155319398, E:\LogPath)
  2. Change the siteid in the LogProcessor.cs:public void Process() file if the site number is not 1 (i marked the line)
  3. Run the Statistics project and give it a try.

Known Issues

  • Currently there is no error handling included so if something goes wrong it alerts
  • It's slow because of the wcf service. If someone has an idea why, make a post into the discussion forum.
  • The IIS log file needs all columns (maybe not all, but I don't have a list with the needed colums)

Database structure script

Here is the current database structure to get the service running.
USE [statistics]

GO
/****** Objekt:  ForeignKey [FK_LogFiles_Sites]    Skriptdatum: 04/10/2010 17:04:04 ******/
ALTER TABLE [LogFiles] DROP CONSTRAINT [FK_LogFiles_Sites]
GO
/****** Objekt:  Table [dbo].[LogFiles]    Skriptdatum: 04/10/2010 17:04:03 ******/
DROP TABLE [LogFiles]
GO
/****** Objekt:  Table [dbo].[Visits]    Skriptdatum: 04/10/2010 17:04:31 ******/
DROP TABLE [Visits]
GO
/****** Objekt:  Table [dbo].[Pages]    Skriptdatum: 04/10/2010 17:04:07 ******/
DROP TABLE [Pages]
GO
/****** Objekt:  Table [dbo].[Sites]    Skriptdatum: 04/10/2010 17:04:12 ******/
DROP TABLE [Sites]
GO

/****** Objekt:  Table [dbo].[Sites]    Skriptdatum: 04/10/2010 17:04:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Sites](
	[SiteId] [int] IDENTITY(1,1) NOT NULL,
	[Name] [text] NOT NULL,
	[SiteName] [text] NOT NULL,
	[LogPath] [text] NOT NULL,
 CONSTRAINT [PK_Sites] PRIMARY KEY CLUSTERED 
(
	[SiteId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Objekt:  Table [dbo].[Visits]    Skriptdatum: 04/10/2010 17:04:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Visits](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[SiteId] [int] NULL,
	[VisitorIp] [ntext] NULL,
	[VisitorFirstTime] [datetime] NULL,
	[VisitorLastTime] [datetime] NULL,
	[EntrySiteId] [bigint] NULL,
	[VisitorTotalActions] [int] NULL,
	[Referer] [ntext] NULL,
	[OS] [ntext] NULL,
	[WebBrowser] [ntext] NULL,
	[WebBrowserVersion] [ntext] NULL,
	[Resolution] [ntext] NULL,
	[TrafficSent] [bigint] NULL,
	[TrafficGet] [bigint] NULL,
	[Visits] [int] NULL,
	[VisitorType] [int] NULL,
 CONSTRAINT [PK_Visits] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Objekt:  Table [dbo].[Pages]    Skriptdatum: 04/10/2010 17:04:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [Pages](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[SiteId] [bigint] NULL,
	[Url] [varchar](max) NULL,
 CONSTRAINT [PK_WebSites] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

/****** Objekt:  Table [dbo].[LogFiles]    Skriptdatum: 04/10/2010 17:04:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [LogFiles](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[SiteId] [int] NOT NULL,
	[LogFile] [text] NULL,
	[CurrentLine] [bigint] NULL,
	[Processed] [int] NULL,
 CONSTRAINT [PK_LogFiles] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Objekt:  ForeignKey [FK_LogFiles_Sites]    Skriptdatum: 04/10/2010 17:04:04 ******/
ALTER TABLE [LogFiles]  WITH CHECK ADD  CONSTRAINT [FK_LogFiles_Sites] FOREIGN KEY([SiteId])
REFERENCES [Sites] ([SiteId])
GO
ALTER TABLE [LogFiles] CHECK CONSTRAINT [FK_LogFiles_Sites]
GO

Last edited Apr 10, 2010 at 3:24 PM by alexosoft, version 3

Comments

No comments yet.