Home     RSSRSS

Archives: Others

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,


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,


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



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



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).


    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.


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.


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: , , , ,

Unit Tests Test Case – Is My Code Output Correct

November 20, 2013 by kiranbadi1991 | Comments Off on Unit Tests Test Case – Is My Code Output Correct | Filed in Development, Others, Testing

One of the goals for writing unit tests is to ensure that code does what it is supposed to do. When we write and compile the code, we know it gets through perfectly without any errors. However not having any errors do not necessarily mean that it does what it’s supposed to do. We need to have some mechanism to know that the code produces the results we require and we also have other means to validate the code output. Your test case for Unit test should test for results or code output validity.

Consider the below simple program add 2 numbers (I know this example is too easy and not practical one ,however this is best example coming to my mind now while writing this post,sorry I do have some great unit tests for my piece of code, I will share those as well later on)

public class AddTwoNumbers {

public double add (double number1, double number2) {

Return number1 + number2;



The Intended purpose of AddTwoNumbers is to take 2 double values and do the addition. We need some mechanism to know that when this program gets executed we are indeed doing addition and program is also not giving us back any runtime exception. Probably we can write the test for this program something like below,

public class AddTwoNumbersTest {

public static void main(String[] args) {

AddTwoNumbers addtwonum = new AddTwoNumbers();

double output = addtwonum.add(40,50);

if (output != 90) {

System.out.println("Bad result: " + result);




One of the other ways to test this program is with Junit and use its assertEquals Method.

import static org.junit.Assert.assertEquals;

import org.junit.Test;

public class AddTwoNumbersTest {


public void testAddTwoNumbers() {

AddTwoNumbers addtwonum = new AddTwoNumbers();

double output = addtwonum.add(40,50);

assertEquals(60, result, 0);



Junit probably is one of the best library for unit testing the java code. Probably sometimes later I will write more on Junit. In case while writing the code ,the requirements are not clear, then probably making some assumptions surely helps. However we need to ensure that our assumptions are repeatedly clarified with users or relevant stakeholders.

So the first unit test case for your piece of code should be to verify that your code does what it is supposed to do.


Tags: , , , ,

Unit Testing–What to Test

October 9, 2013 by kiranbadi1991 | Comments Off on Unit Testing–What to Test | Filed in Development, Others, Quality, Testing

One of the frequent discussions I often get into with developers especially in agile projects is how to write the test case for the class or method and what should we be testing in the unit tests.

There seems to be difference in opinion among folks as what unit test case needs to cover? Or what should we be testing with unit tests?

Should we write the test for both positive and negative input values or should we write the test that shows the code is just enough working? Or should we write the test case for all the possibilities that the code is going to fail or it’s going to be used?

All of these questions seems to be perfectly valid and there isn’t a single right answers to it. However based on my experience and little bit of wisdom, I can say that if you are working with experienced veteran programmer, then probably he will suggest that we write test cases for all possibilities and if you are part of team where everyone is young or most of them are in their midlife, then probably they will not be bothered and will ask you to write tests that shows the code is working as expected. I don’t think there is anything wrong with this approach either, however I feel it’s good that more unit tests we write, better the shape of project in the downstream phases, however this needs to be done correctly and results should be obviously visible. It does not make sense that we do unit testing for every piece of code flow and there are thousands of defects logged by the QA in the later phase. This just don’t make sense economically nor from the management prospective.

So probably I will focus on areas which I feel developers need to focus in their unit testing in this post without getting themselves buried in the art of functional testing,

There are infinite number of ways the code might fail, it might fail due to environmental changes, code might fail due to data changes or it might fail due to real bug in it. All these conditions happens all the time in various environment. So it’s practically not possible to know the failure mode beforehand. However being the developer of the code, we do assume that our code will be working will be working certain fixed environment with certain fixed perquisites for it to run. So it’s always better that we do some assumption and use our time judiciously for writing tests which reflects real time behavior.

I would suggest each of the unit tests exercised on the code under test should minimum show that.

  • The output given the code is correct and it does give us expected results.
  • Extreme boundary conditions for the input values are handled correctly.
  • Error handling is done appropriately by the code.
  • Tests should exercise the performance of the code under test

Maybe in next post I will come up with proper example to show as how to prepare test cases for each of the above conditions. We need to ensure that our unit tests are lean and thin in nature and we need to test just enough code.

Technorati Tags: ,

Tags: , ,