SharePoint 2007 IIS Traffic Analysis

I recently needed to run down some performance/usage statistics on a SP 2007 farm, and found the following Feb. 2008 blog post. Unfortunately, the SQL scripts for building the tables and procedures were missing. Archive.org to the rescue and I revived the SQL from the dead. Below is the full blog post including the necessary SQL scripts:

**Note: I also corrected a typo, the column names IISReports.IISLOGSTATS and IISReports.loggingdir were incorrectly referenced as IISReports..IISLOGSTATS and IISReports.loggingdir in the scripts.

Original Post: http://blogs.technet.com/b/corybu/archive/2008/02/26/sharepoint-iis-traffic-analysis.aspx

Internally we have experienced situations where internal users have built tools or solutions which have negatively affected performance for our SharePoint users. This can be done in different ways such as Setting up multiple MOSS Farms to crawl our customer facing front ends at high performance setting or creating a custom application to execute against various MOSS web services without throttling the application in any way. From that need spawned a little tool we created with the help of log parser J The following is an example of how you could setup a scheduled task to notify you on number of Hits against your moss environments and how much bandwidth each user is consuming.

Please remember to never perform any of the following on production without first trying it out in a test environment, all items here are merely examples of how you ‘could’ perform such an action.

Skills and Items required / assumed to perform this task.

· A SQL 2005 server with XP_CMDSHELL enabled
· Read Permissions for SQL Server Account Granted to the IIS Log directory of your SharePoint Servers
· Access to create and modify databases on your SQL Server
· Logparser 2.2 Installed on your SQL Server
· Good Understanding of SQL Server and TSQL
· Good Understanding of IIS
· Access to all customer Facing SharePoint Front Ends
· Good Understanding of SharePoint

Step 1: Create a reporting database on your SQL Server, for the benefit of this blog entry we will call this database IISReports. Set that reporting database to autogrow for the data file and log file.

Step 2: Grant read permissions to all IIS log directories where your Web application Logs are stored on all servers you wish to report on.

Example: %WINDIR%System32Logfilesex080218.log For better performance internally we place our IIS Log files on a serperate drive

Step 3: Install Logparser 2.2 to a directory on your sql server(make note of this directory) For the purpose of this entry we will call this c:templogparser.exe

Logparser 2.2 can be downloaded @ http://www.microsoft.com/technet/scriptcenter/tools/logparser/default.mspx

Step 4: Create Tables that we will use to pull in and manipulate the data

Click Here to open example Script

USE [IISReports]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[IISLOGSTATS](
	[TotalHits] [bigint] NULL,
	[LogFilename] [varchar](255) NULL,
	[csHost] [varchar](255) NULL,
	[csUsername] [varchar](255) NULL,
	[cIp] [varchar](255) NULL,
	[ServerToClientBytes] [bigint] NULL,
	[ClientToServerBytes] [bigint] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF



USE [IISReports]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[loggingdir](
	[IISDir] [nvarchar](100) NULL
) ON [PRIMARY]

Step 5: execute the Create script for the stored procedure that will be used to pull the data.

Click Here to Open example Script – Make sure to read the /**notes**/ as they direct you to make changes to the script to cater to your environment.

use [IISReports]
go
create procedure [dbo].[IISLOGTracking]
as
truncate table IISReports.loggingdir

/** standard query that uses log parser to grab the current IISLOG file off of each web front end
Make sure to change frontend#e$wwwlog*.log  to the actual UNC path of each of your IIS log directories for each front end **/
declare 
@path nvarchar(255),
@path2 nvarchar(255),
@path3 nvarchar(255),
@path4 nvarchar(255),

select @path =  'xp_cmdshell ''C:Program Files (x86)Log Parser 2.2logparser.exe "select top 1 path from Frontend1e$wwwlog*.log order by path desc" -i:fs -q:on'''
select @path2 =  'xp_cmdshell ''C:Program Files (x86)Log Parser 2.2logparser.exe "select top 1 path from Frontend2e$wwwlog*.log order by path desc" -i:fs -q:on'''
select @path3 =  'xp_cmdshell ''C:Program Files (x86)Log Parser 2.2logparser.exe "select top 1 path from Frontend3e$wwwlog*.log order by path desc" -i:fs -q:on'''
select @path4 =  'xp_cmdshell ''C:Program Files (x86)Log Parser 2.2logparser.exe "select top 1 path from Frontend4e$wwwlog*.log order by path desc" -i:fs -q:on'''

insert into IISReports.loggingdir exec(@path)
insert into IISReports.loggingdir exec(@path2)
insert into IISReports.loggingdir exec(@path3)
insert into IISReports.loggingdir exec(@path4)

delete IISReports.loggingdir where IISDir is null
truncate table IISReports.iislogstats

/** SQL Cursor that takes the directory path / current days IIS Log and passes it through a cmdshell string 
to execute the logparser query, it then pulls it back to your Sql server.  Make sure to change
-server:ReportingServer to your reporting server.**/ 

declare   
@dirpath as nvarchar(250)  

DECLARE DBCursor CURSOR For  
  Select IISDir 
  From IISReports.loggingdir  
  
OPEN DBCursor  
FETCH NEXT FROM DBCursor into @dirpath
  
WHILE @@FETCH_STATUS =0  
BEGIN  
EXEC  
  ('  
 xp_cmdshell ''c:templogparser.exe "select count(*) as TotalHits, logfilename, cs-host, cs-username, c-ip, sum(sc-bytes) as ServerToClientBytes, sum(cs-bytes) as ClientToServerBytes from ' + @dirpath + ' to IISLOGSTATS Group by cs-host, cs-username, logfilename, c-ip order by TotalHits desc" -i:IISw3c -o:sql -server:ReportingServer -database:IISReports''
')  
 FETCH NEXT FROM DBCursor into @dirpath
END  
CLOSE DBCursor  
DEALLOCATE DBCursor  

/** Code to Email top 20 for Hits/Visits Change <change to your email> to your email at @reciepients line :) **/
declare @tableHTML nvarchar(max);
SET @tableHTML =
    N'<H1>Top 20 for Total Hits</H1>' +
    N'<table border="1">' +
    N'<tr><th>Total Hits</th><th>Logfilename</th>' +
    N'<th>Url Host</th><th>UserName</th><th>IP</th>' +
    N'<th>Bandwidth Used (MB)</th></tr>' +
    CAST ( ( SELECT top 20 td = totalhits,       '',
                    td = logfilename, '',
                    td = cshost, '',
                    td = csusername, '',
                    td = cip, '',
                    td = sum(ServerToClientBytes + ClientToServerBytes)/1024/1024 
				from IISReports.IISLOGSTATS 
					where csusername is not null 
					group by totalhits, logfilename, cshost, cip, csusername order by totalhits desc
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail    
    @recipients = '<change to your email>',    
    @subject = 'Top 20 Hits',   
    @body = @tableHTML , 
	@body_format = 'HTML';

/** Code to Email top 20 for bandwidth used Change <change to your email> to your email at @reciepients line :)**/
declare @tableHTML2 nvarchar(max);
SET @tableHTML2 =
    N'<H1>Top 20 for BW used</H1>' +
    N'<table border="1">' +
    N'<tr><th>BW Used in MB</th><th>Total Hits</th>' +
    N'<th>Logfile</th><th>Url Host</th><th>Username</th>' +
    N'<th>IP</th></tr>' +
    CAST ( ( SELECT top 20 td = sum(ServerToClientBytes + ClientToServerBytes)/1024/1024,    '',
					td = totalhits,       '',
                    td = logfilename, '',
                    td = cshost, '',
                    td = csusername, '',
                    td = cip
                    
				from IISReports.IISLOGSTATS 
					where csusername is not null 
					group by totalhits, logfilename, cshost, cip, csusername order by sum(ServerToClientBytes + ClientToServerBytes)/1024/1024 desc
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail    
    @recipients = '<change to your email>',    
    @subject = 'Top 20 Users for Bandwidth',   
    @body = @tableHTML2 , 
	@body_format = 'HTML';

Step 6: If everything has been followed and updated accordingly you can now run the following in SQL.. shortly there after you will receive an email similar to the following