Tuesday, September 19, 2017

Parse and fill SharePoint IIS Logs in to SQL Server database for query

This is the second part of the article series, on monitoring SharePoint with Advanced IIS Logging

By using Advanced IIS Logging we can filter our IIS logs. However logs are scattered into multiple log files which makes it difficult to query.

Following are the steps to import log files into SQL Server table

1. Use Log Parser to parse log files

Download Log Parser

2. Select required fields for Advanced IIS logs.

Remember to put User Agent as the last field as it can contain spaces

image

3. Create a header separate header file and store it in a place accessible to SQL Server

image

4. Write a stored procedure to capture parsed log entries

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE SP_AdvancedLogs
    
AS
BEGIN
     DECLARE  @CMD nvarchar(512)
     ,@Result int = 0
     SET @CMD = '"G:\Tools\LogParser2.2\LogParser.exe -i:TSV -iSeparator:space -headerRow:OFF -nSkipLines:4 -iHeaderFile:"F:\LPS\IISADVHeader.txt" -createtable:ON -o:SQL -oConnString:"Driver=SQL Server;Server=Localhost;Database=Audit_Logs;Trusted_Connection=yes" "SELECT * INTO SP_Logs FROM ''\\SP_FARM\Logs\IIS\AdvancedLogs\HOME\*.log'' where RowNumber > 5 AND c-Ip <> ''10.10.10.11'' AND cs-Username is not null AND cs-Username NOT IN (''0#.w|DOMAIN\USER'')"'

    EXEC @Result = MASTER..xp_cmdshell   @CMD
END
GO

5. Configure the Stored Procedure to execute periodically

Table will be filled after the stored procedure is executed

image

(You might need to drop existing log table or retrieve the delta as per your requirement)

No comments: