使用LogParser将SharePoint使用情况日志文件导出到数据库中 [英] Exporting SharePoint usage log files into a database using LogParser

问题描述

因此，基本上，我们有许多由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:\projects\STSLogParser\STSLogParser.exe" 2005-01-01 "c:\projects\STSlog\2005-01-01\00.log"  c:\projects\logparsertmp\stslog.csv
"C:\Program Files\Log Parser 2.2\logparser.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:\projects\logparsertmp\stslog.csv WHERE (username IS NOT NULL) AND (TO_LOWERCASE(username) NOT IN (domain\serviceaccount))" -i:CSV -headerRow:ON -o:SQL -server:localhost -database:SharePoint_SA_IN -clearTable:ON

这篇关于使用LogParser将SharePoint使用情况日志文件导出到数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！