Let us first preface this by saying that this blog entry is going to be rather technical. We apologize in advance.
A client of ours recently needed reports built that would track what parts of the world their users came from. By analyzing their logs and doing a little bit of research we determined that with relative accuracy we could determine a user’s country of origin by their IP Address. There is a great product called IP 2 Location that publishes a database of IP Number ranges and their associated Country. Notice that we said they publish a list of ip number ranges, which are a completely different form of an ip address.
The challenge was we had a database of about 1.5 million user’s ip addresses and somehow had to be able to compare them in the database to this list of ip number ranges and countries. The vendor of the product had great documentation of how to perform the necessary conversion in ASP, C#, and PHP but unfortunately provided no instructions on how to convert the data directly in a MS SQL database. Because of some of the limitations of the SQL language data manipulation is often somewhat of a pain. We're guessing that is probably why the vendor choose to not deal with coming up with a strictly MS SQL solution, and probably why we couldn't find any articles on this topic. In the end the solution ended up being a pretty small piece of code, but it took a while to get there. Hopefully the following can help the next person facing this issue.
The process of converting an IP Address to an IP Number is relatively simple. For example take the IP Address 192.168.1.0 - To convert it to an IP Number you would do the following.
(256*256*256*192) + (256*256*168) + (256*1) + (0)
Unfortunately MS SQL doesn't provide any native Split functionality to split the decimal separated ip address into the small pieces of data that we could easily work with. We thought we were going to have to do some nasty string dissection using Substring and CharIndex and then we stumbled upon the beautiful Parsename function. Apparently this function was originally designed to help developers navigate through the MS SQL naming structure (ie databasename.dbo.tablename). For our purposes it worked great to pull apart our decimal separated string so we could perform calculations on each piece of data.
In the end what we came up with was the simple trigger that follows. Please feel free to reuse it, we do ask however that you leave the credits intact.
CREATE TRIGGER Insert_IpNumberON Downloads
AFTER INSERT,UPDATE
AS
BEGIN
--PLEASE LEAVE THE FOLLOWING CREDITS INTACT
--CODE BY BIGSTEP CONSULTING
--WWW.FIRSTBIGSTEP.NET
SET NOCOUNT ON;
declare @downloadid bigint
declare @ipaddress varchar(20)
select @downloadid = ObjectDownloadID, @ipaddress = ipaddress from inserted
update Downloads set IpNumber =256 * 256 * 256 * CAST(PARSENAME(@ipaddress, 4) AS float) + 256 * 256 * CAST(PARSENAME(@ipaddress, 3) AS float) + 256 * CAST(PARSENAME(@ipaddress, 2) AS float) + CAST(PARSENAME(@ipaddress, 1) AS float)
where objectdownloadid = @downloadid
END
After implementing this trigger and updating all the existing data we were able to finish writing the necessary reports. It was great to be able to leverage the existing rather useless ip addresses our client had stored in their database into usable metrics that will help them accurately track their website content’s usage by country.
