DesignBais Tip – Getting Client Info

A question came up in the DesignBais forum : "Is there any way for me to find the IP Address or Hostname of the requesting machine?" There are a couple "unorthodox" ways to do this. With the information provided here you will get an idea of how to get any data into your application from other sources in your network.

This may not work in v4, or there may be a better solution in v4, and this article may go obsolete quickly. I’ll post timely updates as required. We can get the client IP address and hostname from the IIS activity log. The trick is to take information we know and use it to find information we want.

Configure IIS

To start, we need to set the logging in IIS:

• In IIS, go to website properties and enable logging.
• Set the Active Log Format to W3C Extended.
• Click Properties for the log format, set the log time period to Hourly (this gives us smaller buffers to look at).
• Under the Extended Properties tab, click the Extended Properties checkbox, then click the Cookie and Host properties.

IIS will now save the "DesignBAIS" cookie in logs. I think the only time a log is written by DB is when we first login. Each log entry for DB has this data (depending on other settings in your properties setup):

Time (GMT)	04:31:35
IP Address	192.168.1.116
Username	–
Get/Post	GET
URI	/jspellhtml2k4/jspell.js
Status	404
Host	clientpc
Cookie	DesignBAIS=machine= %7BD0E8B9BF%2D53D9%2D4A72%2D8775%2D224E11702069%7D %2E15868; +ASPSESSIONIDCAAQQDAB=PHLDAAEBIOOJODDMDLCFGHCJ

I don’t know why DB logs a 404 error for jspell.js, maybe I don’t have something configured properly on this system, but I get this error all the time, which may be a good thing for our purposes. I also don’t know why the Username isn’t recorded either, but these things aren’t important at the moment.

Accessing the logs

Note the time is recorded in GMT, so you get consistent logging no matter where your server is and no matter where the client is. The ID to the log file also uses the GMT Hour value, "04" in the case above, along with the current GMT date. The format of the log filename is:
   exYYMMDDHH.log
An example for today, when I am writing this article is:
   ex06082204.log
Note that the date and time in the UK (GMT) is the 22nd at 4am, even though here in the US when I wrote this it was still the 21st at 9pm PST. Using this information you should be able to figure out what the log filename is for any give time.

IIS logfiles are stored wherever the logging properties says. The default is:
  C:\WINDOWS\system32\LogFiles\W3SVC1
So once you’re changed the settings and restarted IIS, you can expect to find logs in that folder.

The next step is to get from your MV environment to the logs. (This information applies for any application that requires getting data from a remote environment.) You need to reach from BASIC into your web server, read the logfile, then you can parse it for the data you want.

If your DBMS is on the web server localhost then it should be no problem to use a statement like OPENSEQ, or with D3 use the OSFI to open "c:/windows/system32…".

If your DBMS is on another Windows server, then you may want to setup a share to the web server. You can use a command like this:
  net use w: \\websvr\windows\system32… psw /user:administrator
That can be executed with a "!" or other shell command, or from a script when your Windows OS boots. The idea is to create a mapped drive "w:" that points into the \\websvr system, folder \windows… To login and get access to that path you need a username and password. The resulting OPENSEQ would open "w:" and read the "ex06…log" file. In D3 you need to create an item in the dm,hosts, file called "w", so that you can open "w:" to logpath. As you can see I’m trying to get away from going into detail on this topic which is documented elsewhere.

If your DBMS in in Linux or Unix then you can setup a Samba share.

Extracting desired data from the logs

So at this point let’s assume that the log is being generated and you can access it from BASIC. Now how do we link a given user request to their data in the logs?

Take a look at that Cookie field in the log:
DesignBAIS=machine=
%7BD0E8B9BF%2D53D9%2D4A72%2D8775%2D224E11702069%7D%2E15868;
+ASPSESSIONIDCAAQQDAB=PHLDAAEBIOOJODDMDLCFGHCJ

That cookie is the very same value returned by the DBCOOKIE variable in DesignBais! There are some differences of course:

• The log entry is URL encoded.
• The DBCOOKIE doesn’t include the "machine=" text.
• It also doesn’t include the ASPSESSIONID info, which by the way, this is not the same as the SESSIONID variable available in DesignBais.

The DBCOOKIE for that above value looks like this:
  {D0E8B9BF–53D9–4A72–8775–224E11702069}.15868

To encode the DBCOOKIE, we need to translate the non-numeric values as follows. These and other character translations are found here and on many other sites:

Character	Encoded Value
{	%7B
}	%7D
–	%2D
.	%2E

So, taking the DBCOOKIE variable and using a SWAP or CHANGE function in BASIC, we can get the same value found in the log. The only thing left is to loop on all lines (attributes) in the log and use an INDEX function to see if the cookie we want is in the line we’re testing. If it is, we can use a FIELD statement to extract the IP address, hostname, or other data. In my example data in the table above, the IP address is in field 2 and the host is in field 7.

Considerations:

• If you have a high-activity site, the logs will have a lot of data to look through, even if the files are recycled hourly. This can slow down this operation, so don’t do it too often.
• If someone changes your IIS logging this process may fail.
• If DBI updates the DesignBais software to provide this data, it would be much better to use DB tools than this cumbersome process.

I mentioned in the opening that there is another way to do this. Rather than having BASIC operate on your server logs it might be more "elegant" to have your BASIC program execute a routine on the web server which returns the desired values. This separates the request for data from the implementation of the request. Of course this means writting a little server-side code in whatever language you find convenient. The article on Alternative Graphs describes this sort of process at-length.

When I come up with this stuff it doesn’t mean I like or advocate any specific solution, I’m simply providing answer to problems that otherwise didn’t seem solvable. Let me know if you’d like to me to implement these or similar solutions for you. I can’t guarantee they’ll be elegant but we’ll get the job done.