知方可補不足~利用LogParser將IIS日志插入到數據庫 - lori－IT工程師數位筆記本

文章出處

LogParser是微軟開發的一個日志分析工具，它是命令行格式的，我們通過這個工具，可以對日志文件進行操作，對于一個幾百兆的log文件，使用記事本打開是件很殘酷的事，所以，很多情況下，我們都會將大日志文件的內容插入到數據庫中，這樣有利于我們更好的去分析系統的日志。

腳本中心給它的定義

Log Parser 2.2 是一個功能強大的通用工具，它可對基于文本的數據（如日志文件、XML 文件和 CSV 文件）以及 Windows 操作系統上的重要數據源（如事件日志、注冊表、文件系統和 Active Directory）進行通用查詢。只要告訴 Log Parser 您所需的信息以及您希望如何處理這些信息，它就能很好地完成任務。查詢結果可以是基于文本的自定義格式輸出，也可以針對更特定的目標（如 SQL、SYSLOG 或圖表）進行保存。大多數軟件都是為完成有限幾個特定任務而設計的。Log Parser 卻不一樣。只要用戶需要，只要用戶能想到，它都可以實現。只要使用 Log Parser，世界就是您的數據庫。

建立日志數據庫和數據表

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