Dinusha's Blog: September 2017

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

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)

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