We know where you are

One of the projects I’m helping on is a web based work order system. The site won’t just log work orders. It will also keep track of inventory as well as time sheets. Everything is being written in ASP using a Microsoft SQL Server 2005 backend. Because of my (lack of) knowledge of ASP, I’m only working on the SQL portion.

The IP address for the PC the person is connected from is being logged whenever they clock-in and clock-out. The request was made to be able to click the IP address in the report and see if the person was actually at the location they picked from the drop down menu. That would require the IP ranges of each location to be stored in the SQL database.

To accomplish the task, I created a table in the database that holds the following information:

Location ID (there’s already a location table)
IP Range Start (e.g. 10.0.10.1) - stored as varchar(15)
IPRange End (e.g. 10.0.13.255) - stored as varchar(15)

Every location has at least 4 subnets hence the third octet of the end IP being higher than the start IP.

It should be noted that a more efficient way of storing IP’s is storing each octect as a tinyint instead of storing the entire IP address as varchar(15). This helps in the storage size of the table in which the IP’s are being stored. Since this particular table is not holding many IP ranges, the storage size of the table doesn’t really matter.

The next step was to create a SQL table function that would pull the third octet from the IP being passed from the workstation, find it within the IP ranges and return the location data. I did this by using the parsename function. The function I created compares the third octet from the IP address being passed from the workstation to the third octet in the start and end IP’s. I only have to compare the third because the first two, 10.0, is always the same. The table function I created can be viewed here. An explanation of how the function works is included.

The web developer will probably start using the function in order to automatically set the location instead of the user choosing a location. This would simplify the clock-in/out process. For now, we’re just going to use it as a check up to see if they really were where they said they were.

We never really grow up, we only learn how to act in public. - Bryan White

12.Feb.07 Microsoft SQL, Web Apps