Home     RSSRSS

Posts Tagged ‘IIS 7’

Automated way of logging IIS Logs to MSSQL Database using ODBC

March 15, 2014 by kiranbadi1991 | Comments Off on Automated way of logging IIS Logs to MSSQL Database using ODBC | Filed in Development, Environment, Others, Web Server

Sometimes for few of the internal applications it helps to automate the application logging process to the database so that operation’s team can measure and benchmark the operation efficiencies of the systems.So in order to achieve these objectives, it helps to log the information directly to the database and use the facilities offered by database. So in this post I will share some of my experience in automating these processes and few of the risks elements we need to take into consideration..

The first step of this entire process is to create the database table which will hold your IIS log information.IIS 6,IIS 7 and IIS 7.5 in fact provides you the script to create the table. The sql script for creating table is located in below path,

%windir%\system32\inetsrv\logtemp.sql

However if you are on IIS 8 or IIS 8.5, then probably you might not find it in that location. So you can use the below script and create the table,

USE [IISLOGS]
GO

/****** Object:  Table [dbo].[InternetLog]    Script Date: 3/16/2014 1:23:42 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[InternetLog](
    [ClientHost] [varchar](255) NULL,
    [username] [varchar](255) NULL,
    [LogTime] [datetime] NULL,
    [service] [varchar](255) NULL,
    [machine] [varchar](255) NULL,
    [serverip] [varchar](50) NULL,
    [processingtime] [int] NULL,
    [bytesrecvd] [int] NULL,
    [bytessent] [int] NULL,
    [servicestatus] [int] NULL,
    [win32status] [int] NULL,
    [operation] [varchar](255) NULL,
    [target] [varchar](255) NULL,
    [parameters] [varchar](255) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

For each of the table columns, Microsoft has below explanation, its always safe to go with Microsoft suggestions since having incorrect column types means that at some point we might see data truncation issues.

FieldName: ClientHost
Data Source/Type: Varchar(255)
Explanations: Client IP address.

FieldName: Username
Data Source/Type: Varchar(255)
Explanations: User name for the client. If the page is not password-protected, this is always the anonymous user name.

FieldName: LogTime
Data Source/Type: Datetime
Explanations: Date and time that the log entry was created.

FieldName: Service
Data Source/Type: Varchar(255)
Explanations: Name of the service. This can be WWW, FTP, or some other name.

FieldName: Machine
Data Source/Type: Varchar(255)
Explanations: Server name.

FieldName: ServerIP
Data Source/Type: Varchar(255)
Explanations: Server IP address.

FieldName: ProcessingTime
Data Source/Type: Int
Explanations: Time spent on request processing (in milliseconds).

FieldName: BytesRecvd
Data Source/Type: Int
Explanations: Number of bytes received.

FieldName: BytesSent
Data Source/Type: Int
Explanations: Number of bytes sent.

FieldName: ServiceStatus
Data Source/Type: Int
Explanations: Service status, such as 200.

FieldName: Win32Status
Data Source/Type: Long Integer
Explanations: Windows NT status code. 0 typically indicates success.

FieldName: Operation
Data Source/Type: Varchar(255)
Explanations: Type of the operation or command. For example, this may be USER for FTP or GET for WWW.

FieldName: Target
Data Source/Type: Varchar(255)
Explanations: Target of the operation. For example, this may be Default.htm.

FieldName: Parameters
Data Source/Type: Varchar(255)
Explanations: Any parameters for the operation. This can be either name/value pairs for invoking CGI or an ISAPI extension. It is a user name for the FTP command USER. 

Once the table is created, the next step is to configure ODBC System DSN on the machine which hosts the IIS Server.If you are 32 Bit systems , then you need to Configure 32 bit System ODBC and for 64 bit systems you need to enable 64 Bit System ODBC.Below steps should help you to configure ODBC DSN,

  1. On the IIS Server, open Control Panel, double-click the ODBC data source, click the System DSN tab, and then click Add.When the Create New Data Source window appears, click to select SQL Server, and then click Finish
  2. image[8]

  3. Click Add and in the Name box, type HTTPLOG(Name of DSN), type a description, click to select the SQL server that you want to connect to, and then click Next. If the SQL server is on the same computer, select (local).

image

    3. In the creation wizard, make sure that you click to select With Windows NT  authentication using the network login ID for the computer that is running SQL Server. Examine the client configuration, and use the default Named Pipe setting. Make sure that the SQL server name is correct, and then click OK.Click Next.

image

4. Map the IIS Logs database to the database where the original created table resides, and then click Next.If you want to, you can click to select Save long running queries to the log file and Log ODBC driver statistics to the log file in the wizard.Saving these queries will help you to debug any issues you find in operational or during set up phase.Else its not required to save queries.

Click Finish.At the end of the wizard, click Test Data Source. Make sure that you have successfully connected to the computer that is running SQL Server, and then click OK to exit. You need to ensure that Data Source test is successful.

image

With the above steps , you have now successfully done the setup for System DSN for logging IIS Logs into SQL Database.Microsoft provides you the DSN to log information to My SQL , Oracle and MS SQL Database. So if your database is different than these ,like Postgre or Derby then you need to install the relevant drivers for the same before they can be used.

Now the final step is  configuring the IIS to use the System DSN and start logging the application logs into the database table which we created earlier,On IIS 7, below steps can be followed to configure IIS to do ODBC logging,

  1. In the ISM MMC, right-click the Web site, and then click Properties.
  2. Click the Web Site tab.You can use configure the logging based on per site basis.
  3. In the Active log format list, click to select ODBC Logging. You can ignore the user name and password on the ODBC Logging Properties page if you selected Windows NT Integrated authentication when you set up the system DSN that is mapped to the computer that is running SQL Server.
  4. Click Apply, and then click OK.NOTE: If an account is specified on the ODBC Logging Properties page, the Username field in the SQL Server table is blank or contains a dash (-). If a domain account is used, the account name appears in the SQL Server logging table.
  5. Stop and Restart the Website.

Now IIS will direct all logging information to the table.However there are some risks for using this approach and they definitely needs to be taken into consideration,

  1. If your IIS Site is very busy , then logging using ODBC might consume lot of system resources which might impact the performance of the server.
  2. Its recommended that database holding the logging information should never be shared with live application else there exists substantial risks that database performance also might get impacted and slow down the application.

Few of the situation where I feel we can use these types of automation process is when your application is internal in nature and does not deal with very heavy loads.

For all heavy load application, I would recommend that we log IIS logs from the file to the database using  this approach as we can also automate that process on daily, nightly or weekly basis to load logs into the database.

 

Tags: , , , ,

How to enable logging in IIS 7.0

February 24, 2014 by kiranbadi1991 | Comments Off on How to enable logging in IIS 7.0 | Filed in Development, Environment, Performance Engineering, Web Server

IIS 7 on Windows 2008 Server has increased the logging capability of IIS compare to IIS 6. IIS 7 ships with below modules which are default on set up,

HTTP Logging Module(loghttp.dll)

Interacts with HTTP.sys process and processes request status. It is required for generation of logs.

Failed Request Tracing Module(iisfreb.dll)

Logs failed Requests for debugging purpose

Request Monitor Module(iisreqs.dll)

Watches the worker process activity

Tracing Module(iisetw.dll)

ETW tracing to capture trace file

Custom log Module(logcust.dll)

Logs custom module information

All the modules are located in system 32 /inetsrv directory of the server (Please see below screenshot),

image

It’s now possible to log information on per site basis or globally with IIS. It’s also possible to log just only the failed requests or only successful requests. Centralized logging can be done in Binary or W3C format.

In Order to enable logging, below steps needs to be followed,

  • Open Server Manager. Open command Prompt window and Type Run. Run Window will open. In Run window type CompMgmtLauncher.exe
  • Click on Roles (Web Server Roles).Check if Http logging is installed. If it’s not then you need to install it by adding appropriate Role.
  • Check the HTTP Logging box and install it.
  • Close the Server manager.
  • Once enabled you should be able to see something like below screen once you click on HTTP logging Module. (Below screen is from IIS 8).

image

Once the logging is enabled, logs tags are automatically created in applicationhost config file.

<log>
<centralBinaryLogFile enabled=”truedirectory=”%SystemDrive%\inetpub\logs\LogFiles/>
<centralW3CLogFile enabled=”truedirectory=”%SystemDrive%\inetpub\logs\LogFiles/>
</log

Its always a best practice to generate logs in the separate drive from your main drive where we have installed IIS 6 or the drive where heavy lifting of requests take place.

Tags: , , , ,

Loading Microsoft IIS Logs into SQL Server 2012 with Log Parser

February 1, 2014 by kiranbadi1991 | Comments Off on Loading Microsoft IIS Logs into SQL Server 2012 with Log Parser | Filed in Database, Development, Performance Engineering, Performance Test Tools, Scripting, Web Server

If you ever thing of building the customized tool for viewing and analyzing the IIS logs, the first thing, you will probably do is to think of some way of loading the IIS logs to some database specially MS SQL Express or MY SQL.

Parsing the IIS logs and loading it into the database has its own challenges, we can always write the customized code which will read each line of log file and then load it to the database table. However it will  require a specialized programming skills since parsing logs file will require you to first read the log file, remove the headers or unnecessary data in the log file and then proceed to load the required data into the Database table. So your program should be aware of all the format and complete structure of your log file so that it can handle all unexpected characters of the log file.Its quite a tedious and time consuming  task.I have loaded the IIS logs using Powershell and Log Parser into the MS SQL Express database.

Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows operating system such as the Event Log, the Registry, the file system, and Active Directory®.You tell Log Parser what information you need and how you want it processed. The results of your query can be custom-formatted in text based output, or they can be persisted to more specialty targets like SQL, SYSLOG, or a chart.

So in this post, I will show you as how to Load the IIS Logs into the MSSQL Express database.First thing you need to do , is to download the IIS Log Parser and then Install it in your local machine or Server. Once you have installed the log parser tool, then you need know the location of your IIS logs files and you need to access that you have all the rights to access and read the IIS Logs.You will also need to ensure that you have SQL Server installed and you have complete rights on the database.Probably you need to have rights to create the table and have full rights to database.If your database is located in other machine, then you will need to ensure that you are able to connect the machine where log parser is installed and it is able to connect to the database and has access the location of IIS logs files.

Once you have installed log parser , have access to SQL Server and IIS logs location, then you can use the below example query to load iis logs to sql server database.(Below example I have loaded multiple logs into the Database)

C:\Program Files (x86)\Log Parser 2.2>LogParser.exe "SELECT * INTO TBIIS76 FROM
C:\Users\Kiran\Desktop\IISLogs\u_ex130327.txt" -o:SQL -oConnString:"Driver=SQL S
erver;Server=KIRAN\SQLExpress; Database=IISLOGS;Trusted_Connection=yes" -createT
able:OFF -e:10

Statistics:
-----------
Elements processed: 75
Elements output:    75
Execution time:     0.07 seconds


C:\Program Files (x86)\Log Parser 2.2>LogParser.exe "SELECT * INTO TBIIS76 FROM
C:\Users\Kiran\Desktop\IISLogs\u_ex130318.txt" -o:SQL -oConnString:"Driver=SQL S
erver;Server=KIRAN\SQLExpress; Database=IISLOGS;Trusted_Connection=yes" -createT
able:OFF -e:10
Task completed with parse errors.
Parse errors:
Cannot find '#Fields' directive in header of file "C:\Users\Kiran\Desktop\IIS
  Logs\u_ex130318.txt". Lines 1 to 285 have been ignored

Statistics:
-----------
Elements processed: 12968
Elements output:    12968
Execution time:     20.93 seconds


C:\Program Files (x86)\Log Parser 2.2>LogParser.exe "SELECT * INTO TBIIS76 FROM
C:\Users\Kiran\Desktop\IISLogs\u_ex130319.txt" -o:SQL -oConnString:"Driver=SQL S
erver;Server=KIRAN\SQLExpress; Database=IISLOGS;Trusted_Connection=yes" -createT
able:OFF -e:10

Statistics:
-----------
Elements processed: 25219
Elements output:    25219
Execution time:     54.60 seconds


C:\Program Files (x86)\Log Parser 2.2>LogParser.exe "SELECT * INTO TBIIS76 FROM
C:\Users\Kiran\Desktop\IISLogs\u_ex130320.txt" -o:SQL -oConnString:"Driver=SQL S
erver;Server=KIRAN\SQLExpress; Database=IISLOGS;Trusted_Connection=yes" -createT
able:OFF -e:10

Statistics:
-----------
Elements processed: 25922
Elements output:    25922
Execution time:     53.50 seconds


C:\Program Files (x86)\Log Parser 2.2>LogParser.exe "SELECT * INTO TBIIS76 FROM
C:\Users\Kiran\Desktop\IISLogs\u_ex130321.txt" -o:SQL -oConnString:"Driver=SQL S
erver;Server=KIRAN\SQLExpress; Database=IISLOGS;Trusted_Connection=yes" -createT
able:OFF -e:10

Statistics:
-----------
Elements processed: 27395
Elements output:    27395
Execution time:     59.74 seconds


C:\Program Files (x86)\Log Parser 2.2>LogParser.exe "SELECT * INTO TBIIS76 FROM
C:\Users\Kiran\Desktop\IISLogs\u_ex130322.txt" -o:SQL -oConnString:"Driver=SQL S
erver;Server=KIRAN\SQLExpress; Database=IISLOGS;Trusted_Connection=yes" -createT
able:OFF -e:10

Statistics:
-----------
Elements processed: 22772
Elements output:    22772
Execution time:     60.12 seconds (00:01:0.12)


C:\Program Files (x86)\Log Parser 2.2>LogParser.exe "SELECT * INTO TBIIS76 FROM
C:\Users\Kiran\Desktop\IISLogs\u_ex130323.txt" -o:SQL -oConnString:"Driver=SQL S
erver;Server=KIRAN\SQLExpress; Database=IISLOGS;Trusted_Connection=yes" -createT
able:OFF -e:10

Statistics:
-----------
Elements processed: 17497
Elements output:    17497
Execution time:     42.62 seconds


C:\Program Files (x86)\Log Parser 2.2>LogParser.exe "SELECT * INTO TBIIS76 FROM
C:\Users\Kiran\Desktop\IISLogs\u_ex130325.txt" -o:SQL -oConnString:"Driver=SQL S
erver;Server=KIRAN\SQLExpress; Database=IISLOGS;Trusted_Connection=yes" -createT
able:OFF -e:10

Statistics:
-----------
Elements processed: 670
Elements output:    670
Execution time:     1.35 seconds


C:\Program Files (x86)\Log Parser 2.2>LogParser.exe "SELECT * INTO TBIIS76 FROM
C:\Users\Kiran\Desktop\IISLogs\u_ex130326.txt" -o:SQL -oConnString:"Driver=SQL S
erver;Server=KIRAN\SQLExpress; Database=IISLOGS;Trusted_Connection=yes" -createT
able:OFF -e:10

Statistics:
-----------
Elements processed: 1135
Elements output:    1135
Execution time:     1.60 seconds


C:\Program Files (x86)\Log Parser 2.2>
Let me explain the below query which inserts the logs into the sql server database,
C:\Program Files (x86)\Log Parser 2.2>LogParser.exe "SELECT * INTO TBIIS76 FROM
C:\Users\Kiran\Desktop\IISLogs\u_ex130326.txt" -o:SQL -oConnString:"Driver=SQL S
erver;Server=KIRAN\SQLExpress; Database=IISLOGS;Trusted_Connection=yes" -createT
able:OFF -e:10

If you look closely at the above query, you can see that I have connection string to the local server KIRAN\SQLExpress and I am using the driver SQL Server and I have a database IIS Logs. Since I have windows authentication on my local machine, I am using Trusted_Connection = Yes. However if you are using network based logs, then you need to replace,

 
Trusted_Connection=yes

with

-username:yourusername -password:yourpassword 

I have given the create table flag as Off since I have table TBIIS76 already created in the database IISLogs. In case if you need the log parser to create the table by itself, then you need to set create table flag as ON.

e flag is very important flag and is more of extended logging for log parser.I will give you information as what went wrong in case if few records are not loaded into the database for any reason.

Below is example of parse errors which was given for one of the log files. Without this flag, you will spend hours trying to figure out as what is wrong in case if there are any errors. If you look at below error, you will understand as why it’s painful to write custom code to parse log files, if there is any missing status fields, then probably your program will die without giving you any indication.

Task completed with parse errors.
Parse errors:
Cannot find '#Fields' directive in header of file "C:\Users\Kiran\Desktop\IIS
  Logs\u_ex130318.txt". Lines 1 to 285 have been ignored

The table in case if it does not exists and you want log parser to create for you, then it will create one for you. The table structure along with column data format looks something like below,

image

Once the data is loaded into the SQL Server, it will open the new world for you to query the results and you can built the customized tools on top of it or use SQL Management studio to write customized query and do the kind of analysis you want to do. This is especially very helpful in analyzing Production metrics and doing capacity planning activity.

Also please note that you can upload any event logs/registry logs/http err logs etc to the database using the log parser tool.I feel using the above approach, you should be able to do all kinds of performance analysis on logs.

In the next post I will show you as how to load the IIS logs into the SQL Server at runtime using system ODBC driver.

Tags: , , , , , ,