Link to home
Start Free TrialLog in
Avatar of rossdagley
rossdagley

asked on

Convert Dotted Notation IP address to IP Number

Hi guys

I've searched long and hard for this but cant find an easy way to do it - I hope someone here can help me, and im in sort of a hurry, so I've made it worth your while with the points ;)

I have a database with two tables. One with fields and records as below, listing ranges of IP addresses, and their coresponding country. (example:)

ID      Start Range      End Range             2 Digit C Code      3 Digit C Code      Country
1      33996344                      33996351             GB                      GBR                      UNITED KINGDOM
(etc x 42k records ;)

Now, I have a seperate table with only one field, listing lots of dotted notation ip addresses (eg 10.10.1.1)
(etc x about 4k records ;)

What i want is a query to pipe these 4k odd records (which change weekly) into a new table, simply listing two fields, the original dotted notation IP address, and its corrisponding country.

Points and a bar of chocolate to anyone who can show me how to do it ;)

Thanks in advance guys

Ross
Avatar of JimConrad
JimConrad

I'm assuming that you already know how to translate the ip address into a number and that your problem is creating the query to find the correct country based on the ip range?
Hi Ross.

I'd probably have a sub do it.  
Define recordsets for the tables.
In the recordset for IP addresses (tblIP), move through one at a time.  Remove punctuation, and store the result in a variable "IPnum".
In the recordset for countries (tblCountries), findfirst StartRange > IPnum, and move back one record.
In the recordset for your new table (tblList), Addnew and copy in the country from tblCountries and the original IP from the tblIP recordset.
Then go back and do it all again with the next record, until done.

Sounds laborious but I don't think it will take long.  

Good luck.

-- Eric
I think it is possible to do what you want with just a simple query.

If you drop both tables in the Query Grid (and make sure that there are no "join" lines), then Access will create a cartesian product (every row in the first table combined with every row from the second table).  So if you have 3 rows in TableA and 5 rows in TableB, the result is 15 rows, A1 with B1, A1 with B2,... A2 with B1, A2 with B2... etc.

Then you can add Criteria like
WHERE A1.Column1 >= B1.Column1 AND A1.Column1 <= B1.Column2

Or for your purposes:
WHERE IP_Address >= StartRange AND IP_Address <= EndRange  
Avatar of rossdagley

ASKER

Hi guys, and thanks for the quick responses as always.

The problem lies in the conversion between dotted notation and 'raw' ip address format. I've managed to find like a VB script or somesuch that can convert it, but no way to do it within access.

The problem is I am given an ip address, and I have 42k records in access that show a 'range' (not a range of dotted notation address, but a range of their 'raw' counterparts). There is a formula for this conversion, but I have no idea how to integrate it into access.

http://www.psacake.com/web/he.asp is a good example of the conversion.

so to summise, I'm still stuck lol. Excuse my ignorance and access noob-ness, I know what I want to achieve, but I guess this will require some sort of expression (which is beyond me).

Thanks again guys.

Ross
Is it just the string manipulation you need?

if the IP is a string 111.222.333.444
then you can use
ip="111.222.333.444"
right(ip,3)
444
left(ip,3)
111
mid(ip,5,3)
222
mid(ip,9,3)
333
then put these together to get your raw ip.


Looks like you should have smooth sailing from there.

-- Eric
Of course, you may have to allow for the cases where the ip segments are not 3 digits long, by doing some tests.

-- Eric
You say you know the conversion formula so here's how you integrate that into Access:  

Open a new module as create the function there:
public someFxn(OldAddress as long)  (I'm assuming it'll be long)
'convert it
someFxn = conversion result
end function

Save the module.

Query your table.  The first field might be OldAddress, in the second field put this:
NewAddress:someFxn([OldAddress])

That'll do your conversion for you.

Is that what you're looking for?

Walt


Instr(<start>,ip,".") will give you the position of the points, e.g. Instr(5,ip,".")  
Leaving out <start> gives you, say 3 (for a 2-digit first segment). Call this point1.
Set <start> to point1+1 to get the second point's position; etc.
Then pick out each segment with the mid or right or left functions as above.

Good luck,
-- Eric
no, the ip conversion isn't unfortunately a string concatination (sp?), its an actual formula. :( This formula needs to be computed record by record, then matched to its corrisponding 'section' in the master table, and then outputted to the new table.

Please guys - take a loot at this: http://www.cyscape.com/products/country/samples/ch_convertIP.asp.txt

which shows the formula, and my reasoning. I just need a way to get this intoa query somehow.

Thanks a million everyone, really. You're all being so helpful.

--Ross
Sorry walt - didnt see your post - I think you're the closest so far with the steps i need to take - any chance you can guide me (hold my hand!) now I've posted that working asp example?

Ross
Sorry Ross, I guess I am still not getting the drift of what you want to do.  Is you actual aim to get the IP addresses stored as numbers instead of strings?

In that case, why not manipulate the strings as above, then convert the "point-less" string to Long?

I thought your aim was just to get raw string and country code beside each other in a new table.

-- Eric
lol - I'm consusing everyone it seems! Sorry guys... Ok. One last try!

I have two tables. One with a range of IP addresses in numerical form (say the range 12122220 - 12129999) along with their corresponding country, and another table with the dotted notation format ip addres (say 10.10.1.1).

I need to convert the dotted notation into its numerical form (using something like the examples I've posted), THEN find the corresponding record in the first table, and create a new record in a new table, displaying the dotted notational address and its corresponding country, based on which range it lies in. !

Clear as mud. But if you read through from the beginning of what I said, I hope it IS clear. I'm really sorry guys...


Ross

Yes, but it seems the suggestions above would get you there...my trouble is seeing what else you need!

Hope somebody else's fresh viewpoint can help you out.

Regards,

-- Eric
Avatar of Suat M. Ozgur
May be something like this ?

Function retIP()
    ip = "120.10.1.126" '[IPField]
    retIP = (Left(ip, 3) * 256 ^ 3) & _
        (Mid(ip, InStr(1, ip, ".") + 1, InStr(InStr(1, ip, ".") + 1, ip, ".") - InStr(1, ip, ".") - 1) * 256 ^ 2) & _
        (Mid(ip, InStr(InStr(1, ip, ".") + 1, ip, ".") + 1, InStr(InStr(InStr(1, ip, ".") + 1, ip, ".") + 1, ip, ".") - InStr(InStr(1, ip, ".") + 1, ip, ".") - 1) * 256) & _
        Mid(ip, InStr(InStr(InStr(1, ip, ".") + 1, ip, ".") + 1, ip, ".") + 1, Len(ip) - InStr(InStr(InStr(InStr(1, ip, ".") + 1, ip, ".") + 1, ip, "."), ip, "."))
End Function

Suat
That might do it, but I wouldn't know. bugger. lol - thanks for the posting - remember your talking to a novice - I can do stuff in access but not write functions and use them - how would i implement that?

Thanks for trying to help Suat :)
wow.  that function really is something.  it might help to take it apart for you (and avoid doing the string search a gadzillion times):

Function RawIP(dottedIP As String) As Long
   'variables to store the positions of the dots
   Dim firstDot As Integer, secondDot As Integer, thirdDot As Integer

   'find the dots and store their positions
   firstDot = InStr(1,dottedIP,".")
   secondDot = InStr(firstDot+1,dottedIP,".")
   thirdDot = InStr(secondDot+1,dottedIP,".")

   'calculate RawIP from the values between the points
   RawIP = Left(dottedIP,firstDot-1)*256^3
   RawIP = RawIP + Mid(dottedIP,firstDot+1,secondDot-firstDot-1)*256^2
   RawIP = RawIP + Mid(dottedIP,secondDot+1,thirdDot-secondDot-1)*256
   RawIP = RawIP + Right(dottedIP,Len(dottedIP)-thirdDot)

   'as both code samples said, we now bias the value to make use of the full long integer range
   RawIP = RawIP - 2147483648
End Function

go ahead and create a new module.  paste that code in there.  save the module as anything you like.

now create a new query.  add the two tables you're referring to.  add the dotted_ip field from the one table and the country code from the other.  then add the [Start Range] and [End Range] fields, but uncheck the boxes that say "show" (or is it "display"?).  in criteria under [Start Range], put:
 <= RawIP(DottedIPTable.dotted_ip)      'of course, change the names to whatever you actually have

in the criteria under [End Range], put:
 >= RawIP(DottedIPTable.dotted_ip)

your query should now bring up the dotted ip's matched with their corresponding countries.  you can save the query and use it whenever you need it (it will always display current data), or you can go to the little button up top with a drop-down next to it that signifies the query type and change it to "Make Table".  then when you hit the exclamation point button (or double-click the query's name when it's closed), it'll run the query and throw the data in a table for you.

i don't think anyone who answered previously really looked that hard at the conversion code you gave.  a simple string concatenation will not do it!!  Suat had the idea better than the others (multiplying by the right factors of 256), but had you concatenating still, instead of adding, like you're supposed to.  if you concatenate, there's no way to tell where one number ends and the next begins (that why we use dots).

hope this gets you where you need to be

weston
P.S.  you may want to check to make sure whether your database is using biased values or not.  If not, get rid of that line of code.  In your first link, the sample output is unbiased.  But there's a line commented out that says if you're using Access to store it, then to bias it.

Just from your sample raw IP's you listed, it looks to me like they ARE the biased values, so in that case just leave my code as is (if you can, I would find out for sure, though)
rossdagley,

Please create a table and just one field named as "ip" in it then save, open and enter some IP data. And create a query which uses this table then put the following code into the first field of the query :

=(Left(ip, 3) * 256 ^ 3) & (Mid(ip, InStr(1, ip, ".") + 1, InStr(InStr(1, ip, ".") + 1, ip, ".") - InStr(1, ip, ".") - 1) * 256 ^ 2) & (Mid(ip, InStr(InStr(1, ip, ".") + 1, ip, ".") + 1, InStr(InStr(InStr(1, ip, ".") + 1, ip, ".") + 1, ip, ".") - InStr(InStr(1, ip, ".") + 1, ip, ".") - 1) * 256) & Mid(ip, InStr(InStr(InStr(1, ip, ".") + 1, ip, ".") + 1, ip, ".") + 1, Len(ip) - InStr(InStr(InStr(InStr(1, ip, ".") + 1, ip, ".") + 1, ip, "."), ip, "."))

Run the query, you will see what it is easily (I told you creating a table for this. For your database you will just change the "ip" field name in the function with yours. Then you got the converted ip address)

Suat
"ip" field is text field, you know..

Suat
Just so you guys know, I'll try the suggestions today as soon as I get the chance. I'm grateful everyone is being so helpful (as always!). I'll post as soon as i know anything!

Thanks!

Ross
Okay!

We're getting warm

Pilby - thats *exactly* what I'm looking for - thankyou so much. buty there is a problem when I try and run it.

I get "Runtime Error '6':" "Overflow"

When I hit debug, its on the line:

RawIP = Left(dottedIP, firstDot - 1) * 256 ^ 3

I have renamed my table and field exactly as in your example to avoid any confusion (so my table is called 'DottedIPTable' and the field is called 'dotted_ip'.

Any advice?

Thanks a million!

Ross
Ross,

I don't know if you ever play on my solution but following is the (same function) VB function version of it. Please give it a try.

Function ResIp(ip as string)

respi=(Left(ip, 3) * 256 ^ 3) & (Mid(ip, InStr(1, ip, ".") + 1, InStr(InStr(1, ip, ".") + 1, ip, ".") - InStr(1, ip, ".") - 1) * 256 ^ 2) & (Mid(ip, InStr(InStr(1, ip, ".") + 1, ip, ".") + 1, InStr(InStr(InStr(1, ip, ".") + 1, ip, ".") + 1, ip, ".") - InStr(InStr(1, ip, ".") + 1, ip, ".") - 1) * 256) & Mid(ip, InStr(InStr(InStr(1, ip, ".") + 1, ip, ".") + 1, ip, ".") + 1, Len(ip) - InStr(InStr(InStr(InStr(1, ip, ".") + 1, ip, ".") + 1, ip, "."), ip, "."))

End Function

calling the function

MsgBox ResIP("121.125.36.45")

Suat
oops!

Function ResIp(ip as string)

resip=(Left(ip, 3) * 256 ^ 3) & (Mid(ip, InStr(1, ip, ".") + 1, InStr(InStr(1, ip, ".") + 1, ip, ".") - InStr(1, ip, ".") - 1) * 256 ^ 2) & (Mid(ip, InStr(InStr(1, ip, ".") + 1, ip, ".") + 1, InStr(InStr(InStr(1, ip, ".") + 1, ip, ".") + 1, ip, ".") - InStr(InStr(1, ip, ".") + 1, ip, ".") - 1) * 256) & Mid(ip, InStr(InStr(InStr(1, ip, ".") + 1, ip, ".") + 1, ip, ".") + 1, Len(ip) - InStr(InStr(InStr(InStr(1, ip, ".") + 1, ip, ".") + 1, ip, "."), ip, "."))

End Function

Suat
alright, here's how you solve you overflow problem:  you need to bias the value BEFORE adding huge numbers to it (the reason you bias it is so you don't have that problem).  so do it this way instead:

Function RawIP(dottedIP As String) As Long
   'variables to store the positions of the dots
   Dim firstDot As Integer, secondDot As Integer, thirdDot As Integer

   'find the dots and store their positions
   firstDot = InStr(1,dottedIP,".")
   secondDot = InStr(firstDot+1,dottedIP,".")
   thirdDot = InStr(secondDot+1,dottedIP,".")

   'bias the value FIRST
   RawIP = -2147483648

  'NOW calculate RawIP from the values between the points
   RawIP = RawIP + Left(dottedIP,firstDot-1)*256^3
   RawIP = RawIP + Mid(dottedIP,firstDot+1,secondDot-firstDot-1)*256^2
   RawIP = RawIP + Mid(dottedIP,secondDot+1,thirdDot-secondDot-1)*256
   RawIP = RawIP + Right(dottedIP,Len(dottedIP)-thirdDot)

   'as both code samples said, we now bias the value to make use of the full long integer range
   RawIP = RawIP - 2147483648
End Function


I didn't test it, but I think that should take care of the problem.  If it doesn't I'll figure it out for ya another way.

Suart, your function would work if you replaced all of your &'s with +'s.  The return value needs to be a number, not a string.  That's the whole point of converting it.... numbers use less storage space than strings.
oops....  you need to take out that last line in the code i just gave (you don't need to bias twice).

Suat, sorry for getting your name wrong on that last post.  Wasn't paying enough attention when I went to send.
okay, i tested....  that solves the overflow problem.  so once again, here's the code you need (hopefully i can get it right this time):

Function RawIP(dottedIP As String) As Long
   'variables to store the positions of the dots
   Dim firstDot As Integer, secondDot As Integer, thirdDot As Integer

   'find the dots and store their positions
   firstDot = InStr(1,dottedIP,".")
   secondDot = InStr(firstDot+1,dottedIP,".")
   thirdDot = InStr(secondDot+1,dottedIP,".")

   'bias the value FIRST
   RawIP = -2147483648

  'NOW calculate RawIP from the values between the points
   RawIP = RawIP + Left(dottedIP,firstDot-1)*256^3
   RawIP = RawIP + Mid(dottedIP,firstDot+1,secondDot-firstDot-1)*256^2
   RawIP = RawIP + Mid(dottedIP,secondDot+1,thirdDot-secondDot-1)*256
   RawIP = RawIP + Right(dottedIP,Len(dottedIP)-thirdDot)

End Function

now unless i spelled something wrong somewhere or something like that, you should have what you need.
Pilby

Different error this time! - Type mismatch -
debugging gives it as being on this line:    RawIP = RawIP + Left(dottedIP, firstDot - 1) * 256 ^ 3

Is there anything i can check as you've tested it at your end. I must have overlooked something obvious... Maybe if I put the database online for download anyone could look at my actual database...

http://rossdagley.dyndns.org/ross/ip2co.zip (2.5mb)

And Suat - I'm very grateful for your help, but I tried pasting that code into a query and couldn't get along with it - with the greatest of respect, Pilby's solution was what I was ideally looking for (the creation of the query to produce a total list, rather than one ip) - please dont be disheartened - you've helped my understanding if nothing else!

Regards,

Ross
I've increased the points on this, as as nothing else, you've deserved it no end guys. I'm sorry I've been so much trouble - I thought this was going to be quite straightforward!

Ross
Sure, Ross. I thought that I got what you needed and you saw that, I was trying to "step" on it by crerating the sample translator function.

Glad to hear that you are closer to solution with Pilby's solution, of course.

BTW : you should remove the temp.csv data in the table, it causes that error.

Suat
ok, may be I wasn't clear enough to be understood:

DottedIPTable table has a value "temp.csv" which is not an IP and doesn't include 3 dot. So it causes that error. (Sort the field descending to see and delete that record).

Suat
Okay - thanks for ointing that out! I removed it, and the query ran - and ran ;)

cpu at 100% for about 4-5 minutes, then outputted only 21 results for some reason? the query ran ok (ie, it didn't crash) but only outputted these 21 results, not the 370 odd I was expecting. Any ideas?

Thanks guys!

Ross
alright, i checked out your database.  first of all, the reason that you're not getting as many results from the query as you expected is that your database has the raw IP's stored unbiased as Doubles (not long integers).  I really don't know why someone would have done it that way.  The whole reason to store an IP address as a number is to save on storage space and a double uses more than a Long.  but anyway, in order to get all your results, you need to take out that line of code on biasing altogether.  you also need to change the first line from:
Function RawIP(dottedIP As String) As Long
TO:
Function RawIP(dottedIP As String) As Double

as far as why it's taking so long..... well, the way it's running right now, it's going to run the function about 372 times 42000 or so times 2 times....  so that'll take a while.  you can speed it up some by creating a make-table query.  just create a new query with just dottedIPTable in it.  put the dotted-ip field in.  in the next field, type:
raw_ip: RawIP(dotted_ip)

go to the button up top for query type and change it to "Make Table"  tell it what to call the table (maybe Dotted_Raw_Table) and then run the query (hit the red exclamation point).  now it's got all the values converted and doesn't need to do it again.

then you'll have to change your "convert" query.  add your new table you just created.  change the table that dotted_ip is coming from from DottedIPTable to Dotted_Raw_Table.  delete DottedIPTable from the top.  then change the criteria expressions to say:
>=Dotted_Raw_Table.raw_ip
and:
<=Dotted_Raw_Table.raw_ip

i don't really have time to test this thoroughly for you, but i think it should solve your problems.  this last stuff isn't necessary, of course, it'll just speed things up a bit (now it's only running the function 372 times).  the important part is taking out that biasing code and changing it to return a Double value.
Okay I've got an update for you :)

I made those changes, and it appeared to work ok (however, there are a lot of negative numbers listed in that table that the make-table query made which cant be right...)

More to the point, I then left the second part of your instructions to run, and the query was still running 90 minutes later when I returned! (This is on a xp2400+ with 1gb of ram btw) so i cancelled it. I need to run this query a few times a day sometimes - I was sure expecting it to take a few minutes, but should it really be taking this long just to find a record in a range?

Thanks for your excellent work so far Pilby - you've got me further already than I could have done on my own, for sure :)
well if you took out the line of code that biases the values, you shouldn't have any negatives (negatives are normal if you're dealing with biased IP's).  so to clarify that, your function should look like this:

Function RawIP(dottedIP As String) As Double
   'variables to store the positions of the dots
   Dim firstDot As Integer, secondDot As Integer, thirdDot As Integer

   'find the dots and store their positions
   firstDot = InStr(1,dottedIP,".")
   secondDot = InStr(firstDot+1,dottedIP,".")
   thirdDot = InStr(secondDot+1,dottedIP,".")

  'calculate RawIP from the values between the points
   RawIP = RawIP + Left(dottedIP,firstDot-1)*256^3
   RawIP = RawIP + Mid(dottedIP,firstDot+1,secondDot-firstDot-1)*256^2
   RawIP = RawIP + Mid(dottedIP,secondDot+1,thirdDot-secondDot-1)*256
   RawIP = RawIP + Right(dottedIP,Len(dottedIP)-thirdDot)

End Function


run your make-table query again.  when i ran it with this function, it gave all positive numbers.

i can't figure out why it would be taking so long to run.  it does have to still do 372*42000*2 comparisons, but those should be a lot quicker than running the whole function that many times.  open up your "convert" query in SQL view and make sure it says:

SELECT [Dotted_Raw_Table].[dotted_ip], [ip-to-country].[Country]
FROM [Dotted_Raw_Table], [ip-to-country]
WHERE [Dotted_Raw_Table].[raw-ip] >= [ip-to-country].[Start Range] AND [Dotted_Raw_Table].[raw-ip] <= [ip-to-country].[End Range]

(of course, you should check all the names in there to make sure they match what you have).

if you check all of that and make sure it's right, and it's still taking that long, i can show you a way to write a function to actually do the querying that would run a lot quicker (because it would do a lot less comparisons).
Okay!

I pasted in your first code over mine, and then ran the make table query - result - dont know what was different, or what i did differently, but it worked - no negative numbers :D

I then pasted the sql code in (over what I had) which was this

SELECT DottedIPTable.dotted_ip, [ip-to-country].Country
FROM DottedIPTable, [ip-to-country]
WHERE ((([ip-to-country].[Start Range])<=RawIP([DottedIPTable].[dotted_ip])) AND (([ip-to-country].[End Range])>=RawIP([DottedIPTable].[dotted_ip])));

Finally, it ran, and the whole thing took under 30 seconds to run. Absolutely perfect. If you were a bird, I'd kiss you!

I'm just double checking it now, but it really does look like it worked. You're a star. Truely :DDD

Kindest possible regards,

Ross :D
(very happy)
ASKER CERTIFIED SOLUTION
Avatar of pilby
pilby

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'm at work now, but will try this as soon as I get home. As always, Very gratful. :D

--Ross
Did the above solution work for your problem? If so, please accept a comment(s) as the answer so the expert(s) will be rewarded for their time and effort.

Joe
Thanks Pilby - checked it and it works like a charm. the whole thing, even for doing 600 records at a time, takes about 45 seconds. I'm really very grateful you've been able to help me like this. Great work!