知(zhi)方可(ke)補(bu)不足~利用LogParser將IIS日志插入到數據庫

回到目錄

LogParser是(shi)微軟開(kai)發的(de)一(yi)個日(ri)(ri)志分析(xi)工具，它是(shi)命令行格式的(de)，我(wo)們(men)(men)通過這個工具，可以(yi)對(dui)日(ri)(ri)志文(wen)(wen)件(jian)進行操(cao)作，對(dui)于一(yi)個幾(ji)百兆的(de)log文(wen)(wen)件(jian)，使(shi)用記事本打開(kai)是(shi)件(jian)很(hen)殘酷(ku)的(de)事，所以(yi)，很(hen)多情況(kuang)下，我(wo)們(men)(men)都會(hui)將(jiang)大日(ri)(ri)志文(wen)(wen)件(jian)的(de)內容插入(ru)到數據庫(ku)中，這樣有(you)利于我(wo)們(men)(men)更好的(de)去分析(xi)系統(tong)的(de)日(ri)(ri)志。

腳本中心給它的定義

Log Parser 2.2 是(shi)一(yi)個功(gong)能強大(da)的(de)(de)通用(yong)工具，它(ta)可(ke)(ke)對基于(yu)文本(ben)的(de)(de)數據(ju)（如日(ri)志文件(jian)、XML 文件(jian)和(he) CSV 文件(jian)）以(yi)及(ji) Windows 操作系(xi)統上的(de)(de)重要(yao)(yao)(yao)數據(ju)源（如事件(jian)日(ri)志、注冊表、文件(jian)系(xi)統和(he) Active Directory）進(jin)行(xing)通用(yong)查詢(xun)。只要(yao)(yao)(yao)告訴 Log Parser 您所(suo)需(xu)的(de)(de)信(xin)息(xi)(xi)以(yi)及(ji)您希望如何處理這些信(xin)息(xi)(xi)，它(ta)就能很(hen)好地完(wan)成任務。查詢(xun)結果可(ke)(ke)以(yi)是(shi)基于(yu)文本(ben)的(de)(de)自定義格式輸(shu)出，也可(ke)(ke)以(yi)針對更(geng)特定的(de)(de)目標（如 SQL、SYSLOG 或圖表）進(jin)行(xing)保存。大(da)多數軟件(jian)都是(shi)為完(wan)成有(you)限(xian)幾(ji)個特定任務而設計的(de)(de)。Log Parser 卻不一(yi)樣。只要(yao)(yao)(yao)用(yong)戶需(xu)要(yao)(yao)(yao)，只要(yao)(yao)(yao)用(yong)戶能想到，它(ta)都可(ke)(ke)以(yi)實現。只要(yao)(yao)(yao)使用(yong) Log Parser，世界就是(shi)您的(de)(de)數據(ju)庫。

建立日志數據庫和數據表

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

添加SQL腳本

/*

    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')

添加VBS自動導入數據腳本

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 = "Cmd /k 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

配置path路徑，大功告成！

運行截圖

數據庫生成日志