Would you like to view inbound and outbound web connections in "real-time"? With a combination of Microsoft ISA Server 2000, Microsoft SQL Server Desktop Engine 2000 (MSDE 2000), and the .NET Framework, it can be done. This article is packed with complete information and all source code is available for download.

NOTE: To download the code for this article (or even view the printable html) you will need to login as a registered user. Don't run away! Registration is simple (and free) and your privacy is high on our priority list. If you have noticed we are so anti-spam that we don't even advertise. We will never be a source of unwanted information and can be trusted with your email address.


Article Index

• Background
• Assumptions
• Requirements
• Architecture
• Procedure
• Database
• Connectivity
• Web Server
• Browser Support
• The Code
• Puting It All Together
• Screen Shot
• Summary
• Updated For MS ISA Server 2004 Beta 2

In what seems like a previous life I wrote "real-time" applications for the National Aeronautics And Space Administration (NASA), and before that for paper industry. So I have a fondness for viewing meaningful data "as it hapens" (or very shortly thereafter). This will suffice as a definition for "real-time" in this article. So please do not bombard me with other definitions of "real-time" because I a painfully aware of them.

With this fondness for "real-time" applications and utilities, I tend to notice when certain products do not contain the functionality, like MS ISA Server 2000. Of course there are probably third party vendors out there who sell "real-time" monitoring add-on's but why buy when we can build it ourselves, rather easily, right?

An obvious assumption is that you are either running MS ISA Server 2000 or another firewall product that can redirect its web proxy log to MDSE 2000. I also assume that you have a solid understanding of all the technologies mentioned in this article such as networking, MS Windows Servers, MS ISA Server 2000, MDSE 2000, Windows XP, .Net Framework, C# and of course a text editor.

My first design for this tool was at an enterprise level, utilizing MS Windows SQL Server 2000, and MS Windows Server 2003 Enterprise Server IIS 6. But that would limit the use of the article to those with access to similar resources, or would require others to scale down my effort to a smaller one. So after giving it a little thought I quickly realized that extremely minimal resources are necessary. So the "bare bones" minimum requirements are as follows:

• Microsoft ISA Server 2000
• Microsoft Windows XP Professional (SP1 Integrated)
• Microsoft Internet Information Services 5
• Microsoft .Net Framework 1.1

OK, I know what some of you are thinking. "What about Visual Studio .NET? Can we do this without our precious Integrated Development Environment (IDE)?" The answer is fun! Of course you can do it without that rather large piece of luggage (baggage). We are simply writing ASP.NET code with C# behind the scene. No need for anything other than a text editor (notepad will suffice), and your thinking cap!!!!

The following diagram illustrates the configuration used in this article. It also depicts the "bare bones" requirements listed above. Of course I have other diagrams for more enterprise-centric configurations and would certainly share if asked, but they would be outside the scope (I love that phrase!) of this article.

Now that I have laid the groundwork, we can talk about the components and how they work together and how to set them up. Of course I could assume that you are well versed in these (but I won't) and just throw the code at you, like so many authors. But it is our mission to give complete detailed information so let's get on with it.

We will need a data repository and I have chosen the "free" version of MS SQL Server called MSDE 2000, Release A. This is the desktop version of SQL Server and runs on prety much every Microsoft operating system. From my experience it runs VERY well on Windows XP Professional, which just happens to be what we are using. Rather than embedding a ton of information in this article on the setup of MSDE 2000, or pointing to an external link for information, it just so happens that I have written a rather extensive article on the subject myself. So if you require help in this area, I refer you to MDSE 2000 Release A: Setup And Configuration.

IMPORTANT! If you do not read the article mentioned above, you will not be aware of a column addition that I made to database table used to contain web proxy data. I have added the column definition "SQLserverDateTime" which is a time stamp of when the data reached the database server. I use this column in the code for the Real-Time Web Proxy Monitor. We will see this later.

Now we have or ISA server(s) in place, and our data repository up and running. The next step is to direct our ISA servers to send Web Proxy logs to the database server as opposed to whatever it is currently doing. This involves a couple of steps. First, the database server MUST contain a properly formatted table in which to store the data. Once again, in my article MDSE 2000 Release A: Setup And Configuration this is covered.

Also there must be a way for the MS ISA Server to connect with the database server (MSDE). I will use Open DataBase Connectivity, or ODBC for short. Most everyone reading this article will undoubtably skip this section, but for those of you who need assistance, please see my article ISA Server 2000/2004: Route The Web Proxy Log To MSDE 2000.

I am going to make an assumption here, so if it is a problem be sure to note it via feedback (below). Referring to the diagram above, notice that the Windows XP Professional workstation is pretty much doing ALL of the work. Capturing Web Proxy data, in my case, does not require a solution that will scale. This might be totally different in your environment. For me, this workstation (733MHz processor, 1GB RAM, and a seperate disk for the database) works just fine. However in a real production environment you would need much more horsepower. Also notice the workstation is running Internet Information Server (IIS). My assumption is that you have (or know how to) configure IIS on the workstation. And that you have installed version 1.1 of the .Net Framework. If you have not installed these components, IIS can be added through Start > Settings > Control Panel > Add or Remove Programs > Add/Remove Windows Components. For IIS you will also need the Windows XP Professional CDROM. The .Net Framework can be installed via Microsoft's web site via Start > Windows Update. Even though IIS on Windows XP has the "one site" limitation it will pose no problem for us.

If you install IIS as a result of this article, when you are finished visit the URL http://localhost and what you should see is something like the picture below. If you see this, you are ready to proceed.

IMPORTANT: It is very possible that even though you have IIS set up correctly AND have "instaled" the .Net Framework that you still need to "initialize" the environment. If after you move my files to your web server, you experience problems (like a completely blank browser screen while running this app), execute the following command:

C:\Windows\Microsoft.Net\Framework\V1.1.4322\aspnet_regiis.exe -i

Help on this command yields the following: "Install this version of ASP.NET and update scriptmaps at the IIS metabase root and for all scriptmaps below the root. Existing scriptmaps of lower version are upgraded to this version."

This application has been successfully tested against Microsoft Internet Explorer 6 and Netscape 7.1. Other browsers may yield undesirable results. If you are using a browser other than these two, I only have one thing to say, UPGRADE.

This brings us to the code which consists of two files. One contains the ASP.NET and C# code and the other is a an external Cascading Style Sheet (CSS) file. I am not going to talk about CSS at all in this article, but here is the contents of the file:

The following is the ASP.NET and C# code for this article. It is not commented; there really is no reason. It's all fairly standard stuff with a couple of exceptions, which I will discuss. If you have any questions and/or observations, please do not hesitate to contact me via article feedback. I do not profess to know everything and will always want to improve at whatever I do. As always, if you have comments that lead to a change on this code or article, I will give you the credit.

NOTE #1: This code produces a web page that refreshes every 5 seconds. That interval can easily be changed. For those who may be unfamiliar, simply modify the following statement from the code above:

NOTE #2: It should be fairly obvious that the SQL/MSDE connection string is "hard coded" and site specific. You will most likely be using different authentication, and there are ways to avoid hard coding the string, however those ways are outside the scope of this article.

NOTE #3: The following SQL SELECT statement is very site and user specific. Once again hard coded for convenience (of this article). I found that that I did not want to see everything that passed through the firewal (ISA Server) so I excluded certain addresses. A better way to accomplish this would be to incorporate selectable filters into the app. If I get around to doing this I will either modify this article of write a short update to it talking about filter implementation. In any case you will want to modify the select statement below.

The only remaing task is to copy the two files to a web server and test it. A good way to do this is to first create a directory under wwwroot. I used the following comand:

c:\Inetpub\wwwroot\Intranets

Then copy the two files into the new diretctory and use the following URL in either IE or Netscape:

http://localhost/Intranets/RealTimeWebProxyMonitor.aspx

Your browser should successfully navigate and yield the screen seen below.

Below is a screen shot of the Real-Time Web Proxy Monitor. It is not pictured to show any particularly relavent information, rather just to show the reader what to expect. This is the only output the application produces. However, it could easily be expanded to do more.

I have barely scratched the surface of possibilities with this article. Microsoft ISA Server 2000, like every firewall that I have seen, can-does-must capture a myriad of data including firewall, web proxy and packet filter. If stored in a relational database a wealth of useful information reporting can be accomplished, thus becoming an invaluable trouble-shoting tool.

A small example of the usefulness of the Real-Time Web Proxy Monitor came about the very first time I ran it. I noticed that every internal workstation and server was downloading virus updates from Symantec. This is inconsistent with the site architecture of my client. The architecture for virus updating is that one server would download virus updates from Symantec, and ALL other clients and servers would update from the internal server. After discovering the problem with the Real-Time Web Proxy Monitor, we were quick to resolve this issue.

For such a small amount of code this article turned out to be quite lengthy. But the code isn't the main topic of this article is it? It has more to do with the concept and architecture than anything else. I would be more than happy to hear of improvements upon any part of this article. So drop me a line via article feedback.

For the record, after I completed this article I used it as a guide for a fresh install. Everything tested perfectly. Nothing irks me more than to get bogus information from a web article, but it hapens all the time. Thanks for reading, and for visiting SymmetricWebSites.Com.

Oh no! Just when I thought we were finished, Microsoft releases ISA Server 2004 Beta 2, and it is actually stable. Yikes! Well, since I have implemented the new version obviously I needed to tie it in with the Real-Time Web Proxy Monitor. However, when I attempted to send the web proxy log to MSDE 2000, I received an error. I should have known better, of course. I knew what the problem was even before looking at the error log. The database table format has changed. Actually the table is just bigger by a few columns, as shown below:

If you plan to have ISA Server 2004 store web proxy data to you MSDE database, you will need to alter the repository table structure. I will not cover this procedure in this article. For detailed information please refer to MDSE 2000 Release A: Setup And Configuration.