1. Junior Member
Join Date
Aug 2007
Posts
8

Hi,
I have a field in my table for IP address which is this long integer format (I believe some standard). Has anyone ever seen it in this fashion and, if so, do you have a way to convert it back into common dotted decimal format?

Thanks

2. Junior Member
Join Date
Apr 2006
Posts
8
Basically an IPv4 address is a 32 bit unsigned integer. The dotted decimal format represents it with four 8 bit integers, from the most significant to least significant. The following JavaScript snippet will do the trick:

var part1 = (ip >> 24) & 255;
var part2 = (ip >> 16) & 255;
var part3 = (ip >> 8) & 255;
var part4 = ip & 255;
var ip_dot = part1.toString() + "." + part2.toString() + "." + part3.toString() + "." + part4.toString();

(>> is the bitwise right-shift operator and & is the bitwise AND operator)

3. Junior Member
Join Date
Aug 2007
Posts
8

Okay, the conversion method listed (I found it in some docs) is

Step 1 Convert the database display (-1139627840) to a hex value.
The hex value equals 0xBC12A8C0.
Step 2 Reverse the order of the hex bytes, as shown below:
CO A8 12 BC
Step 3 Convert the four bytes from hex to decimal, as shown below:
192 168 18 188
Step 4 The IP address displays in the dotted decimal format:
192.168.18.188

I'm not sure that's the same thing you proposed. Is there a way to write a kettle doc to perform this conversion?

4. Chief Data Integration
Join Date
Nov 1999
Posts
9,729
JavaScript: if your number is called "A":

Code:
```var one =   ( A.getInteger() & 0x000000FF )
var two =   ( A.getInteger() & 0x0000FF00 ) >> 8
var three = ( A.getInteger() & 0x00FF0000 ) >> 16
var four =  ( A.getInteger() & 0xFF000000 ) >> 24

if (four<0) four+=256

var ip=one+"."+two+"."+three+"."+four;```
HTH,

Matt

5. Junior Member
Join Date
Apr 2006
Posts
8
dtrobert,

I have doubts about step 2 in the method you listed... IP addresses in dot notation are written with the most significant byte on the left and least significant on the right (see http://en.wikipedia.org/wiki/IPv4). Step 2 in the proposed method would reverse this order. Except if for some reason the column in your database is a binary field with the IP address stored in little-endian form.

I googled an extract from your method, it looks like it comes from some Cisco Unified CallManager documentation... according to it, the value in records from their product is represented in little-endian form (and not being converted back to the correct form when read): "The 32-bit hex value represents four bytes in reverse order (Intel standard)". Is that the type of database you are reading from? If so, Matt's code would yield the correct result.

Perhaps you should test both methods with known data and see which one gives the expected addresses in dot notation.

6. Junior Member
Join Date
Aug 2007
Posts
8

Thanks,
I tried Matt's suggestion and it seemed to work (for the one case). Yes, the data is from CallManager.

My only question is on the A.getInteger(). I ended up substituting the actual field name for A. I have some 4 fields that need this conversion though.

Is there an easy way to use the same transformation against all 4 fields without hardcoding them into the script?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•