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

• Introduction to Advanced IIS Logging for SharePoint
• Parse and Fill SharePoint IIS Logs in to SQL Server database for query

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

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

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

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