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

9/1/2015来源:SQL技巧人气:2685

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

回到目录

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]GOUSE [Log_IIS]GO/****** Object:  Table [dbo].[Online_tj]    Script Date: 10/28/2011 17:08:28 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGOUSE [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]GOUSE [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-takenINTOLog_IIS.dbo.Online_tjFROME:\tj\IISLog\W3SVC10\%log%.logWHERE 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 &":"&logfilenamestrCMD = "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 strCMDWshShell.run strCMD, 1, false
配置path路径,大功告成!

QQWdpaWnsqRUAIHWydSduff77TqoUcOz2AAB0Ynl5GQUEAGgkKz/5d8zmT+95xOmlAo7+P3hCCGkdFBAAQEO2vUa+UwGXXRxgcaDFQRarLQ62OMTiUIvDLA63OMLiSIs1FkdZHG2x1uJjFuss1lscY3GsxQaL4yyOt/i4xUaLEyxOtDjJYpPFyRanWGy22GJxqsVpFlsttlmcbnGGxScsPmlxpsWnLD5tsd3iMxZnWZxtcY7FuRbnWZxvscPisxafs/i8xQUWX7D4osWXLC60+LLFRSu5uAMoIACAkuzCCy+sLhUoIAqIAqKAKCAAwCKQXbOTyy67DAVEAVFAFDARBQyayJaWllBAAIAgsht3cv311zcqoPGZm7r8lVsq86vu1uWv8UM8lQKWdyv/K++igCggCrhQChj04T8UEAAgiOz2ndxy6222AtqzrW8V0FDAygINBfzG1y51rgKigCggCogCooAAAIOR/baGsApo+19dAatFwcr8jLReBcyvvfKSiy9AAVFAFHCRFTDLsizL6nNFfQsKCADQgqx+x6eAhv9Vdw3/qy8E+t4Irq8CVnfrnwLMr73y6isuLnf/2s4b9aCAKCAKuGgKaDifYYQoIABAC5oV0LlcV18FrG83/M94L7h0PnkJsK6A1VvArAKigCjggitgZX72iiAKCADQAtUqoOGC8mcB6y5oWGBdAeurgD4FNEQQBUQBUcBFVkDnO8IoIABAOwLeCLbfC27xWcDQVUC+DoICooAoIKuAAADRaVBAn65VMd4IrhYCr7j0wvzaK79+1VdtBZQ/C2h/HcQICogCooALqIB8FhAAIC7ZHTU0bwT7FgLrPw1dV0B+FxAFRAFRQL4RDACQGs0KWE2ylQJWN+zPAtqrgLYCfvPrl9dXAfNrr/zqRReggCggCogC8ruAAACD0aCA1QxrrALWlwDrbwobXwe23wguFdCYwVFAFBAFRAFRQACAIZEU0Pa/xjeCK/+rfx24VMBK9apVwPqHAp0K6AsKiAKigIuggM5PpDhBAQEAQgn4LOABFgdaHGSx2sL5B+IMDrU4zAIFRAFRwBkr4FIgKCAAQBAoIAqIAqKAKSpgO1BAAAAlKCAKiAKigCggAMDC4f5dQEIImWhQQAAADSggIWRWQQEBADSggISQWQUFBADQgAISQmYVFBAAQAMKSAiZVVBAAAANKCAhZFZBAQEANKCAhJBZBQUEANCAAg6dkU60l9EHhJC4QQEBADSggIMmweFdWloavQ2ERAwKCACgoY0CjtTUYBJs8+hXR+f1cuxRCSC1czr66SN2UEAAAA3BCthYIJ1U61uJtDmRZhgpr5ejN0OZ1M7phIZucYICAgBoCFNA+dHUUq1vjd6SoNHLsqzFQ11GaUIek9o5LdszejNIPSggAICG7N377Fkln50ClleC0Vsij17WRL2ks3z3URp9cIJam845ndboLUjKk3LsxnXHblxXTnPl7cQzuaYCwNTJPnvVT6vkrRTQVpB2W3wbndGU7K4LxlE6ylaXlhhH991uEY3E+HRTOT6NLdSPs++cBg2C06T7Gz0ycMqT8o0f/q7uVd/44e9SzhSbCgBTJ4IC5opLuH2tFVa5fI/Kh/BdCew2G5VrVt30B20xenar5JHJ+1dApR45T7SvmFCnsh7hnOaeJ4/zKWcfovUYooAJBgVEAQFAQxwFNOK7wAs3lAWqjYItGVcCX5v1FjKYAgpbfPrssUl5k8Qo/U8vr/LgN57WiCu7KOBcgwKigACgYQgFrDbKolDfKCiOr3LflcDXZp92NBbwGWdj5NHTHMUeli7tqY+S5jyGip2mpO+8+yqMq4AdT6g8emSsoIAoIABo6KqAziuo0tKUimMX0Bwu171paNRp+IGzZOvIY9sYoSV9KKDso/ZGn+pplEs+3cpz6mt2/VR2FD796JERgwKigACgIeZnAX2X3qqM3ip8TuA8onwlcLbZZwZOpxG2BEUYPZnc0pfGpoZeL4UCwiD7TlP9JDofCqpHc059AyKcROGpGHH0yPBBAVFAANAw0NdBnBudCpivvJwL8tF42W7UBd+/ygaHRjl6mkcFxwqN/EawcGaN2z6jshXWWcZXTHlOfW1zPsfksxxr9MhYiaKAWZbVb1cYZXwMqYD24YQGdG8tCggwG7IsywZTQFkCcteV265KmHadVwJnm7OVCiU3SXMsTRpHz3mIzGVa8lC3uF7qB9keFueQKp8bQmedvYuigPITrPvokXETaxUw2+lG9g3ndl+xvhVQaImhfUpNRAEBFoTIbwTX7zrLKLcIAqSvX6OAQps1W4Iij57d2camCqOhj+aNYM0g+BojK6wwAkJrhXPqHEanntpe28fokeET/Y1gQQF9DKyAvib5hK+1/6GAAHMimgI69aX+qIC9o2w5zgu2vVG/CujcXbMlKMLoOQfKHljnEHVsmPB1EOGI9nlvHEZhtIVDtDunvkYKh447emTEDKaAsaQqVlOFdtYfskEBARaT4T4LmCusxb6K+4xBVsn6lcDZZmMX5+6DKeC48b0RLN/2eVXjGZQVtrsCNgYFnH26K2DdjTLXyp/Ppbp7VZemCj6aKewQBQRYNAb9XUBNGad4Kfd1Xgmcba57iaE1go4siAIactY4+HYBn0H6drfFUW6tPZJVDT40JaOMHhk90T8LGL1wRAWsH10wUc0NFBBg0ehFARNJ958Rjpt0WmKP0ujNCGptOiM5rdFbkMRVQJ9UVWXkAkMqoNFsZwEUEAAqUMDhkk5L7FEavRlBrU1nJKc1eguS/r4RLKiVsAUFBIA00Srg2O2cCaNfHX3Xy7EHBiAYn3z38Uaw06sSVEDjRv2uvQUFBFhwAhRwdFkhhCxy6s4nrL9GVEBh/U92r1EU0PkmtbNJvu0oIMBCkWW6n4aubx+7zQCwcBjON4wCNjpTCgoY6nONXosCAiwILRVw9PUAQshCZXgFrCTJcCbf10S6fCmky4/CGG0TmuFsGG8EAywsbd4ILm8vWxxgceBKDrJYvZKDLQ6xOHQlh1kcvpIjLI60WLOSoyyOXslai49ZrFvJeotjVnKsxQaL41ZyvMXHV7LR4gSLE1dyksWmlZxscYrF5pVssTh1JadZbF3JNovTLc5YyScsPrmSMy0+ZfHplWy3+MxKzrI42+KclZxrcd5KzrfYYfHZlXzO4vMrucDiCxZfXMmXLC5cyZctLlrJxR0YUQGHyRSbCgBTBwVEAVFAFBAFTMWrJtRUAJg6KCAKiAKigMMpYND0tLS0hAKmFhQQYDYMp4DlvpX8lXdlBSzLOBVQ/tgQCogCooD