Category Archives: Log Parser

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.

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)

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.

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

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.

Read IIS log with LOG Parser in SQL-Like language

Read IIS log with LOG Parser in SQL-Like language

Before we start discussion regarding reading of IIS log, I would like to request you to please read my previous article which provides introduction of Log Parser along with some sample query. Basic knowledge of log parser required for this article, if you don’t have knowledge of Log Parser, kindly click here.

After having understanding of Log Parser, you have to find IIS log file location for your server. It may be:

C:\inetpub\logs\LogFiles\W3SVC1

or

C:\WINDOWS\system32\LogFiles\W3SVC1

Administrator can change the location of log file from IIS so it is better to confirm the location of log file first before executing the commands given below.

Once you are ready with Log Parse in your server along with path of IIS log file, you can execute following commands to get various information.

Command 1: This command will give you total number of execution of .ASPX page in your IIS based on given file path and date. I wanted to read only one log file “ex121025.log”, you can give “*” there to read all log files available under “W3SVC1” folder.

 logparser.exe -i:W3C -o:DATAGRID "SELECT COUNT(*) FROM C:\WINDOWS\system32\LogFiles\W3SVC1\ex121025.log where date>'2012-10-19' AND date<'2012-10-21' and cs-uri-stem LIKE '%.aspx'"
  

Command 2: Following command will return total number of process/pages which takes more than 1 second to load, if we know long running pages, we can troubleshoot it and optimize it as well. Please not that “Time-Taken” field gives value in millisecond

logparser.exe -i:W3C -o:DATAGRID "SELECT COUNT(*) FROM C:\WINDOWS\system32\LogFiles\W3SVC1\ex121025.log where time-taken>1000" 

Command 3: following command will return total number of pages whose size is greater than 300KB. We might want to make page little lower in size so that it can be loaded in browser fast.

 logparser.exe -i:W3C -o:DATAGRID "SELECT COUNT(*) FROM C:\WINDOWS\system32\LogFiles\W3SVC1\ex121025.log where cs-bytes>307200"
 

Command 4: Following command will give you top 10 heaviest pages which has taken long time to load in browser.

logparser.exe -i:W3C -o:DATAGRID "SELECT TOP 10 cs-uri-stem as URL, MAX(time-taken) As Max, MIN(time-taken) As Min, Avg(time-taken) As Average FROM C:\WINDOWS\system32\LogFiles\W3SVC1\ex121025.log GROUP BY URL ORDER By MAX DESC" 

Command 5: following command will list out IP with reverse DNS and count how many request came from the IP.

logparser.exe -i:W3C -o:DATAGRID "SELECT c-ip As IP, REVERSEDNS(c-ip) As DNS, COUNT(*) As Requests FROM C:\WINDOWS\system32\LogFiles\W3SVC1\ex121025.log GROUP BY IP ORDER BY Requests DESC" 

BTW, “W3C” parameter value provided when you want to read log of IIS for further details about input and output parameter, please refer my previous article Log Parser.

Hope you find it useful.

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.

LOG Parser – Wonderful yet under-appreciated tool to read log of windows, IIS

LOG Parser – Wonderful yet under-appreciated tool to read log of windows, IIS

LOG Parser it one of the wonderful tool which helps you to read the log Widows, IIS etc. Log Parser is command line utility which helps you to read text based log file files such as XML, CSV and some key data source of Windows operating system such as event log and registry.  As an administrator, you face situation, many times, when you have to read various log files of windows and/or IIS and generally we used to open log file in notepad/XML viewer or some other 3rd party tool but if you think of LOG Parser in that situation, you will have familiar SQL based syntax to read the log and it returns data very fast.

You can download LOG Parser from here.

I am sure you must have seen EventViewer of Windows. It is very good tool to use for getting information about various system error, notification, warnings etc. we can access the same with help of LOG Parser with simple SQL based query. We have few different ways to access that information, out of those different ways, I generally check information in command prompt itself small amount of information is expected and I would go for DATAGRID if I am expecting big result set.

If you have download and installed LOG Parser, open it from Start->All Programs->Log Parser 2.2->Log Parser 2.2

You will get command prompt of Log Parser where you have to execute following command.

 logparser.exe -i:EVT "SELECT TOP 5 * FROM System WHERE EventID>10"
 

Detail about the command we have executed above:

“Logparser.exe” is the command name which supposed to use to read any log information

“-i:EVT”: “-I” (input engine”) tells log parser which log supposed to use. “EVT” means windows event.

We have had “TOP 5” in SELECT query, it means that we will get only first 5 event based on the condition we have specified in WHERE clause.

For more information, look at screen capture given below:

Now, if you want to see the result in DATAGRID, add “-o DATAGRID” which is output engine argument.

 logparser.exe -i:EVT -o:DATAGRID "SELECT * FROM System WHERE EventID>10" 

As soon as you execute the command in command prompt, you will get datagrid something like this:

This is just an introduction of one of the versatile and powerful yet under-appreciated tool. I will have follow-up articles to show different usage of Log Parser.

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.