Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: IP address transformation

  1. #1
    Join Date
    Aug 2007
    Posts
    8

    Default IP address transformation

    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. #2
    Join Date
    Apr 2006
    Posts
    8

    Default

    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 ip = ipaddr.getInteger(); // replace ipaddr with your field name
    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. #3
    Join Date
    Aug 2007
    Posts
    8

    Default IP address transformation

    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. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    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. #5
    Join Date
    Apr 2006
    Posts
    8

    Default

    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. #6
    Join Date
    Aug 2007
    Posts
    8

    Default IP address transformation

    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
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.