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.

Select data fields for your IIS Log file

Select data fields for your IIS Log file

After reading my earlier articles “LOG Parser – Wonderful yet under-appreciated tool to read log of windows, IIS” and “Read IIS log with LOG Parser in SQL-Like language” one of the blog reader sent me an email and asked me that if he runs “Command 2” and “Command 3” given in “Read IIS log with LOG Parser in SQL-Like language” article, he face the error something like this:

Error: SELECT clause: Syntax Error: unknown field ‘time-taken’.

The closest match for input format ‘W3C’ is ‘time’.

To see valid fields for the W3C input format type:

LogParser -h -i:W3C

He has provided me the screen shot which looks something like this:

He asked me whether I have made mistake or typo in providing the queries in  “Read IIS log with LOG Parser in SQL-Like language”.

Well, I would like to say that there is no mistake/type/error in the commands provided. If your log file doesn’t have “time-taken” or “cs-bytes” or any other fields, please select the setting of your log file from IIS. You can select/de-select fields from IIS, follow the steps given below to do the same:

1.)    Double click on “IIS Logging” from the property of website in IIS.

2.)    Click on “Select Fields” button

3.)    Select the fields you wanted to include in log file of IIS and click on “OK” button.

Once you have proper field selected, new log file will be generated from the next schedule with the latest fields and the query given in “Read IIS log with LOG Parser in SQL-Like language” will work fine.

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.