Auto IIS log reading with Log Parser

Auto IIS log reading with Log Parser

We have already seen few topics of Log Parser and found it an interesting and very useful tool. If we want to capture data for few weeks or months for analysis purpose, it is little hectic to read data manually everyday and keep track of it. I have already written some articles of Log Reader which explained how to take output of Log Reader in CSV file or Grid but today we will see how to export Log Parser data to SQL Server and that too, automatically.

We will create one table in SQL Server and will also create one .bat file which will have command to read Log Parser data and send it to SQL Server we will create. Once we have proper .bat file, we can schedule it in Windows Schedule Task to run it automatically everyday as per schedule and insert the data into SQL Server so that we have properly managed IIS Log in relational format to query and analyze in future.

If you are new to IIS, I would like you to go through following articles first to get an idea, what actually Log Parser is???

  • LOG Parser – Wonderful yet under-appreciated tool to read log of windows, IIS (Click Here)
  • Read IIS log with LOG Parser in SQL-Like language (Click Here)
  • Select data fields for your IIS Log file (Click Here)

Well after flash back of some earlier articles, let us move ahead. Create one table in SQL Server.  I am using my SQL Server 2008 R2 Developer edition and ExtremeAdvice database to create following table.

[sourcecode]
CREATE TABLE IISLogDetails
(
LogFileName VARCHAR(150)
,RowNumber INT
,LogFolder Varchar(25)
,IP VARCHAR(25)
,HTTP_Method VARCHAR(5)
,WebURL VARCHAR(100)
,QueryString VARCHAR(250)
,Port INT
,UserName VARCHAR(25)
,ClientIP VARCHAR(25)
,StatusNumber INT
,SubStatus INT
,Win32Status INT
,SCBytes INT
,CSBytes INT
,TimeTaken INT)[/sourcecode]

After having Proper table in SQL Server we will create one batch file which will read IIS log file and send the data to SQL Server. If you want, you can set that batch file in windows schedule task to read data every day from new log file and send it to database table.

Here is the code snippet of batch file.

[sourcecode]
ECHO Log Import Started….
@For /F "tokens=2,3,4 delims=/ " %%A in (‘Date /t’) do @(
Set mm=%%A
Set dd=%%B
Set yy=%%C
)
set dt=%yy:~2,2%%mm%%dd%
"c:\program files (x86)\log parser 2.2\LogParser.exe" "SELECT LogFileName ,rOWnUMBER,S-SITENAME,S-IP,CS-METHOD,CS-URI-STEM,CS-URI-QUERY,S-PORT,CS-USERNAME,C-IP, SC-STATUS,SC-SUBSTATUS, SC-WIN32-STATUS,SC-BYTES,CS-BYTES,TIME-TAKEN INTO ExtremeAdvice.dbo.IISLogDetails FROM D:\ex%dt%.log" -i:W3C -o:SQL -server:RITESH-LAPTOP\SQL2K8R2  -database:ExtremeAdvice -username:YourUserName -password:YourPassword

ECHO Log Import Completed[/sourcecode]

I saved batch file in my “D” drive with name “Log.bat”. you can save it at your convenient path and run it.

Log file in my server has prefix “ex” followed by yymmdd format of date so we are going to extract mm, dd, yy from date in batch file. Date part extraction code I wrote in batch file, would work with few datetime format you should confirm that part with your server/computer and if needed make appropriate change.

Also confirm the path of “LogParser.exe” and give it properly in batch file.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

4 thoughts on “Auto IIS log reading with Log Parser”

  1. I have similar requirement that to collect logs from different servers every hour to see the errors , does your script can track the timestamp based on each server and start again from the last time stamp rather pulling the records all over again based on server?

Comments are closed.