知方可补不足~利用LogParser将IIS日志插入到数据库
时间：2021-07-01 10:21:17
帮助过：7人阅读
[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路径，大功告成！
运行截图
数据库生成日志
