知方可补不足~利用LogParser将IIS日志插入到数据库

[ 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路径，大功告成！

运行截图

数据库生成日志

查看更多关于知方可补不足~利用LogParser将IIS日志插入到数据库的详细内容...