Sql-server – Exporting SharePoint usage log files into a database using LogParser

logparsermosssharepointsql-server

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?

Best Solution

This is the script we use to load IIS log files in a SQL Server database:

LogParser "SELECT * INTO <TABLENAME> FROM <LogFileName>" -o:SQL -server:<servername> -database:<databasename> -driver:"SQL Server" -username:sa -password:xxxxx -createTable:ON

The <tablename>, <logfilename>, <servername>, <databasename> and sa password need to be changed according to your specs.

From my experience LogParser works really well to load data from IIS logs to SQL Server, so a mixed approach is the best:

  • Load raw data from IIS log to SQL Server using LogParser
  • Use SSIS to extract and manipulate data from the temporary table containing the raw data in the final table you'll use for reporting.
Related Question