Solved

mysql script to convert IP address to integer

Posted on 2015-01-10
10
285 Views
Last Modified: 2015-01-17
Hi Team,

  I have a mysql database where in the NETWORK table, the IP_ADDRESS field is varchar and holds standard IP addresses in different lengths  like 1.2.3.4  or 64.55.66.566,  or 123.456.789.123  etc.  I  need another field (i.e. IP_ADDRESS_DECIMAL) to hold their decimal values.   I have seen ip2long in php but I am not sure how to use that......an ordinary bash, perl script with similar functionality will work for me.  I need to put my script in a cron job.    Some bash/perl script like this:

 
mysql -u root -pmypass  mydatabase -e "UPDATE NETWORK SET IP_ADDRESS_DECIMAL = Somefunction(IP_ADDRESS);

Open in new window

Thanks very much.
0
Comment
Question by:rleyba828
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 37

Assisted Solution

by:Neil Russell
Neil Russell earned 50 total points
ID: 40541907
Question is, are you storing THOUSANDS of addresses?  Do you ONLY ever want to store and display never anything else? Never want to know about how many on a network? How many in a country... examples...

The only point of the conversion to a long would be to save space on a very large database.  For small databases it would make no sense to have the over head of conversion everytime you needed to do anything IPADDRESS related.
0
 
LVL 25

Assisted Solution

by:Fred Marshall
Fred Marshall earned 50 total points
ID: 40542099
Perhaps some illumination on why you want to do this would be helpful.
Do you want to do the inverse conversion as well?
Do you have rules for the conversion in mind?  The specification at first glance does not appear to offer a 1:1 mapping.
For example, if one uses a straight powers-of-two summation then the result is not unique:
100.000.000.000 and 020.000.000.000 yield the same result: 2048.
This suggests the trivial but useful conversion which would simply remove the decimal points.
etc.
0
 

Author Comment

by:rleyba828
ID: 40542489
Thanks guys... bur first of all, my apologies....  I didn't use realistic IP addresses in my example above, and that might have added to the confusion...   I was just typing away to illustrate that the field would contain variable length strings.

Hi Neilsr,

   I will be only storing at most....about 1000....and we will only be doing it for our own comany's IP addresses....particularly those in our branches....  I will be doing computations, sorting, comparisons, based on the decimal equivalent of the IPs.  So it is not going to be expensive for a fairly small database.

Hi fmarshall,
   I will only be converting one way....   IP   --> Decimal.  

I am looking for a bash or perl script  that does this   -->  http://www.ipaddressguide.com/ip

1.2.3.4  --> 16909060.
 64.55.66.223   --> 1077363423.

Thanks very much.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 84

Assisted Solution

by:ozo
ozo earned 100 total points
ID: 40542508
perl -le 'print unpack "N",pack"C4",split/\./ for @ARGV' 1.2.3.4   64.55.66.223
0
 
LVL 25

Expert Comment

by:Fred Marshall
ID: 40542591
Well, I can't write this out in perl but I can write it out in pseudo code of sorts:

Get the dotted IP address:
Split it into 4 parts.
Multiply the leftmost part by 16,777,216 or 256^3 or 2^(8*3)=2^24
Multiply the 2nd part by 65,536 or 256^2 or 2^(8*2)=2^16
Multiply the 3rd part by 256 or 256^1 or 2^(8*1)=2^8
Multiply the 4th part by 256^0 orr 2^(8*0) or 2^0.
Add the 4 results.

Now, if someone can explain how ozo's perl statement does this then I'll be pleased to learn.

This indeed is a 1:1 mapping but it's not the only 1:1 mapping.
For example, one could have just as readily used 257^n as the base.
So then, 1.2.3.4 would convert to 17,107,466
and 64.55.66.223 would convert to 1,090,023,832.
The mapping wouldn't be contiguous any longer but that wasn't a specification until the examples were given OR if what the website does were taken as the only possibility.
0
 

Author Comment

by:rleyba828
ID: 40542631
Hi Ozo,

Thanks very much.  Brilliant one liner.   This is what I need and the output matches  what is computed in  --> http://www.ipaddressguide.com/ip

* Not being a PERL expert, I just need your help converting that statement into a function so i can do this:

mysql -u root -pmypass  mydatabase -e "UPDATE NETWORK SET IP_ADDRESS_DECIMAL = Somefunction(IP_ADDRESS);

Open in new window


Where IP_ADDRESS_DECIMAL and IP_ADDRESS are fields in my mysql table called NETWORK.

Hi fmarshall,

   The first half or your reply above was I think the formula being used to compute decimals equivalents, but for the purpose of my objectives, it should be good enough.

Thanks and regards.
0
 
LVL 29

Accepted Solution

by:
fibo earned 300 total points
ID: 40551016
- depending on your final objective, keeping the IP addresses as usual IP might in fact be the best solution: on every computer you can know its IP address as an IP, so what would really be the use of the conversion?

- you did not mention how these addresses are collected. if you real need a complete decimal value, collection time is probably the best time to make the conversion

- the mysql function to convert IP to decimal exists as a standard: INET_ATON()
 cf http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_inet-aton
0
 

Author Comment

by:rleyba828
ID: 40554988
Thanks Fibo,

   That function was exactly what I was looking for.   My SQL statement, which works now is something like this:

 mysql -uroot -ppassword  mydatabase  -e "UPDATE IP_ADDRESS_TABLE SET IP_DECIMAL=INET_ATON(IP_ADDRESS);"       

Open in new window

0
 
LVL 29

Expert Comment

by:fibo
ID: 40555355
B-) Glad we could help. Thx for the grade and points.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

It is a general practice to get rid of old user profiles on a computer  in a LAN environment. As I have been working with a company in a LAN environment where users move from one place to some other place at times. This will make many user profil…
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question