Use logparser to import IIS logs to the database

I. Overview

At present, many websites use IIS logs for statistics. Recently, when the company upgraded the statistics system from one physical machine to three virtual machines and set up scheduled tasks, it encountered some problems and recorded the process, for later reference.

Implementation principle:

Create a new site tj.elong.com, this site only has a 1 picture tj.gif, when the need for statistical data, you can access this picture, the need for statistical data as a parameter (such as: http://tj.elong.com/tj.gif? Tablename = tjtest & orderid = 123456). Each access is recorded in the IIS log. Write a vbs tool to import IIS logs to the database using the logparser tool, then analyze and process the data as needed. The following describes how to set up a local mechanism in win7.

Ii. IIS settings

1. IIS settings

A. The newly created tj.elong.comsite includes a 1-character image tj.gif.

B. Double-click "log" in "function view" to set the Log Path.

　　　　　　

2. view the log information and confirm that the log record is normal.

A. Set hosts to point tj.elong.com to 127.0.0.1.

B. Visit http://tj.elong.com/tj.gifand submit iislog information to confirm the site is OK.

3. Install logparser

You can download the package from http://download.csdn.net/detail/fuhongxue2011/3729508and install it in double-click mode.

4. Create database tables and write SQL statements

1. Create a database table

Create a local database log_iis and the online_tj table in the database. The SQL statement for creating a table is as follows:

View code

USE [Log_IIS]
GO

/****** Object:  Table [dbo].[Online_tj]    Script Date: 10/28/2011 17:08:28 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Online_tj]') AND type in (N'U'))
DROP TABLE [dbo].[Online_tj]
GO

USE [Log_IIS]
GO

/****** Object:  Table [dbo].[Online_tj]    Script Date: 10/28/2011 17:08:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Online_tj](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [logtime] [datetime] NULL,
    [s_ip] [varchar](255) NULL,
    [cs_method] [varchar](255) NULL,
    [cs_uri_stem] [varchar](255) NULL,
    [cs_uri_query] [varchar](1024) NULL,
    [s_port] [int] NULL,
    [cs_username] [varchar](255) NULL,
    [c_ip] [varchar](255) NULL,
    [cs_User_Agent] [varchar](255) NULL,
    [sc_status] [int] NULL,
    [sc_substatus] [int] NULL,
    [sc_win32_status] [int] NULL,
    [time_taken] [int] NULL,
 CONSTRAINT [PK__Online_tj__164452B1] 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


USE [Log_IIS]
/****** Object:  Index [IX_Online_tj_CI_LCCC]    Script Date: 10/28/2011 17:08:29 ******/
CREATE NONCLUSTERED INDEX [IX_Online_tj_CI_LCCC] ON [dbo].[Online_tj] 
(
    [cs_uri_stem] ASC,
    [ID] ASC
)
INCLUDE ( [logtime],
[c_ip],
[cs_uri_query],
[cs_User_Agent]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO


USE [Log_IIS]
/****** Object:  Index [ix_Online_tj_logtime]    Script Date: 10/28/2011 17:08:29 ******/
CREATE NONCLUSTERED INDEX [ix_Online_tj_logtime] ON [dbo].[Online_tj] 
(
    [logtime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

2. Compile the SQL statement used by logparser to insert data

This SQL function: selects data from logs and inserts data into database tables.

View code

/*

    logparser file:tj_insert.sql?start=starttime+end=endtime+log=logfilename
    input parameter:
            start    -    starttime    example:1:00:00 or 18:00:00
            end    -    endtime        example:1:09:59    or 18:59:59
            log    -    logfilename    example:ex10111601 or ex10111618

*/

Select TO_TIMESTAMP(date,time), TO_TIMESTAMP(date,time), s-ip, cs-method, cs-uri-stem, cs-uri-query, s-port, cs-username, c-ip,
    cs(User-Agent), sc-status, sc-substatus, sc-win32-status, time-taken
INTO
Log_IIS.dbo.Online_tj
FROM
E:\tj\IISLog\W3SVC10\%log%.log
WHERE TO_LOCALTIME(Time) BETWEEN TO_TIMESTAMP('%start%','h:mm:ss') AND TO_TIMESTAMP('%end%','h:mm:ss')

V. Compile vbs

1. Write TJ. vbs

Function: Call and execute the preceding SQL statement.

View code

d = DateAdd("n", -6, Now())
strDate = Right(""&(100+Year(d)),2) & Right(""&(100+Month(d)),2) & Right(""&(100+Day(d)),2)

strHr = Hour(time())
strMin = Minute(time())
starttime = timeserial(strHr, strMin - 6, 0)
endtime = timeserial(strHr, strMin - 2, 59)

strHr = Right(""&(100+Hour(starttime)),2)
logfilename = "u_ex" & strDate 
Set WshShell = Wscript.CreateObject("Wscript.Shell")

Wscript.Echo starttime &":"& endtime &":"&logfilename

strCMD = "LogParser  file:E:\tj\tj_insert.sql?start=" & starttime &_
"+end=" & endtime & "+log=" & logfilename &_
" -iw:ON -i:iisw3c -o:sql -oConnString:""Driver={SQL Server};Server=(local);db=Log_IIS;uid=sa;pwd=123"""
Wscript.Echo strCMD
WshShell.run strCMD, 1, false

2. Test vbs

When testing this vbs, we encountered a small problem. If you use logparser 2.2 to run vbs in the beginning, the data can be written to the database. If you run it directly using cmd, the data cannot be written to the database.

There are two solutions:

A. Point the CMD path to c: \ Program Files \ log parser 2.2 and then run vbs,

　　　　　　

B. Add c: \ Program Files \ log parser 2.2 to the environment variable.

Right-click my computer-properties-change settings-advanced-environment variables-path in system variables-edit-add C at the end: \ Program Files \ log parser 2.2, for example

　　　　　　

6. Create a scheduled task

Control Panel-system and security--management tool's last "scheduled task"--create basic task--created, double-click the scheduled task you just created, and select the task on the trigger tab, click Edit below to set it to run every 5 minutes. On the operations tab, select a task and click Edit. If the c: \ Program Files \ log parser 2.2 path is set in the environment variable of the computer, you do not need to fill in the "starting from" in the startup program. If not set, enter c: \ Program Files \ log parser 2.2 in the starting program. For example:

　　

Windows 2003 and windows are similar, and some features are not described in detail. Please refer to the following for any questions.