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.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.