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:\logparser.exe "select top 1 path from \\Frontend1\e$\wwwlog\*.log order by path desc" -i:fs -q:on'''
select @path2 =  'xp_cmdshell ''c:\logparser.exe "select top 1 path from \\Frontend2\e$\wwwlog\*.log order by path desc" -i:fs -q:on'''
select @path3 =  'xp_cmdshell ''c:\logparser.exe "select top 1 path from \\Frontend3\e$\wwwlog\*.log order by path desc" -i:fs -q:on'''
select @path4 =  'xp_cmdshell ''c:\logparser.exe "select top 1 path from \\Frontend4\e$\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:\temp\logparser.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';