0 Replies Latest reply: Feb 6, 2017 11:07 AM by Douglas Dougherty RSS

    Cisco CDR IP Conversion in Qlik Sense

    Douglas Dougherty

      Hello,

       

      I have an IP address in a Cisco CDR that I need to convert from 32-bit hex in reverse order to normal dotted decimal notation.

       

      From the Cisco CDR Admin Guide....

      "The system stores IP addresses as unsigned integers. The CDR file displays IP addresses as signed integers. To convert the signed decimal value to an IP address, first convert the value to a hex number, taking into consideration that it is really an unsigned number. The 32-bit hex value represents four bytes in reverse order (Intel standard). To determine the IP address, reverse the order of the bytes and convert each byte to a decimal number. The resulting four bytes represent the four-byte fields of the IP address in dotted decimal notation.


      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 found this Excel formula that works but have no idea how to translate it into Qlik Sense syntax or if it's even possible in Qlik Sense...


      =CONCATENATE(BITAND(A1, 255), ".", BITAND(ROUNDDOWN(A1 / 256, 0), 255), ".", BITAND(ROUNDDOWN(A1 / 256^2, 0), 255), ".", BITAND(ROUNDDOWN(A1 / 256^3, 0), 255))


      Can anyone assist?


      Thanks,

      Doug