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)

Tuesday, September 12, 2017

Monitor SharePoint farm with Advanced IIS Logging

There are so many options when it comes to SharePoint monitoring. ULS logs and IIS logs are some examples.

ULS logs are generated from different components of SharePoint. These logs are very helpful when troubleshooting. We can use tools like ULSViewer to make our lives easier.

On the other hand IIS logs capture all information relevant requests made to our SharePoint server. But it is very difficult to mine relevant information.

What if I want do some filtering before the logging, rather than capturing all requests. Otherwise my logs will eventually fill my storage space. Unfortunately this is not possible directly with IIS logging.

That’s where Advanced IIS Logging comes in to the picture.

Let me summarize my requirement

  • I don’t need to log requests coming from 10.10.10.10 server
  • I want to apply the filter only to my SharePoint web application
  • Other IIS web applications should use normal IIS Logging

Following are the steps to fulfill my requirement

1. Download Advanced IIS Logging and install

Download from this location : https://www.iis.net/downloads/microsoft/advanced-logging

image

2. Disable Advanced IIS Logging at root level

image

3. Navigate to SharePoint application and enable Advanced IIS Logging

image

4. Disable IIS Logging for SharePoint web application

image

image

5. Apply the filter at Advanced IIS Logging

image

image

image

image

6. Verify whether the filter is applied

image