使用 LogParser 将 SharePoint 使用日志文件导出到数据库中
所以基本上我们有很多由我们的 SharePoint 2007 网站生成的 SharePoint 使用日志文件，我们希望了解它们.为此，我们正在考虑读取日志文件并将其转储到具有适当列和所有内容的数据库中.现在，当我遇到 LogParser 时，我打算制作一个 SSIS 包来读取所有文本文件并提取数据.有没有办法使用 LogParser 将数据转储到 Sql Server 数据库中，或者 SSIS 方式更好?或者还有其他更好的方式来使用 SharePoint 使用日志吗?
So basically we have lots of SharePoint usage log files generated by our SharePoint 2007 site and we would like to make sense of them. For that we're thinking of reading the log files and dumping into a database with the appropriate columns and all. Now I was going to make an SSIS package to read all the text files and extract the data when I came across LogParser. Is there a way to use LogParser to dump data into an Sql Server database or the SSIS way is better? Or is there any other better way to use the SharePoint usage logs?
推荐答案
这是 博客文章 我过去常常获得所需的所有信息.没有必要去自定义代码的长度.
This is the blog post I used to get all the info needed. It is not necessary to go to the length of custom code.
简而言之，创建表脚本:
In brief, create table script:
CREATE TABLE [dbo].[STSlog](
[application] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[date] [datetime] NULL,
[time] [datetime] NULL,
[username] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[computername] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[method] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[siteURL] [varchar](2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[webURL] [varchar](2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[docName] [varchar](2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[bytes] [int] NULL,
[queryString] [varchar](2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[userAgent] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[referer] [varchar](2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[bitFlags] [smallint] NULL,
[status] [smallint] NULL,
[siteGuid] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
调用使日志解析器加载文件数据
Call to make log parser load in the data for a file
"C:projectsSTSLogParserSTSLogParser.exe" 2005-01-01 "c:projectsSTSlog2005-01-010.log" c:projectslogparsertmpstslog.csv
"C:Program FilesLog Parser 2.2logparser.exe" "SELECT 'SharePointPortal' as application, TO_DATE(TO_UTCTIME(TO_TIMESTAMP(TO_TIMESTAMP(date, 'yyyy-MM-dd'), TO_TIMESTAMP(time, 'hh:mm:ss')))) AS date, TO_TIME( TO_UTCTIME( TO_TIMESTAMP(TO_TIMESTAMP(date, 'yyyy-MM-dd'), TO_TIMESTAMP(time, 'hh:mm:ss')))), UserName as username, 'SERVERNAME' as computername, 'GET' as method, SiteURL as siteURL, WebURL as webURL, DocName as docName, cBytes as bytes, QueryString as queryString, UserAgent as userAgent, RefURL as referer, TO_INT(bitFlags) as bitFlags, TO_INT(HttpStatus) as status, TO_STRING(SiteGuid) as siteGuid INTO STSlog FROM c:projectslogparsertmpstslog.csv WHERE (username IS NOT NULL) AND (TO_LOWERCASE(username) NOT IN (domainserviceaccount))" -i:CSV -headerRow:ON -o:SQL -server:localhost -database:SharePoint_SA_IN -clearTable:ON
相关文章