CDR Export CUCM - IP address format

[mmedwid]

IP addresses in call detail records are exported funny.  For example on

of the "destIpAddr" - examples would include 994118154, 1175063050

etc.  Is there a way to have these export properly?  Or if not - is there

an Excel formula that would convert these to something useful -

e.g. x.x.x.x format?  Thank you.

[Jonathan Schulenberg · Accepted solution]

Your answer is documented in the Cisco Unified Communications Manager Call Detail Records Administration Guide. I'm not sure how you would convert this in Excel though.

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.

[mmedwid]

That's a huge supply of CDR info.  Jackpot!  Thank you.

[Muhammad Al-Hussein]

Hello All,

You can use the following Excel function, I created it based on the info You provided, it will convert from the format in the CDR file to the correct format:

=HEX2DEC(MID(DEC2HEX(A2),7,2))&"."& HEX2DEC(MID(DEC2HEX(A2),5,2))&"."&HEX2DEC(MID(DEC2HEX(A2),3,2))&"."&HEX2DEC(MID(DEC2HEX(A2),1,2))

Where A2 = the Excel Field to be converted

Thanks and Best Regards,

Muhammad

[Muhammad Al-Hussein]

I have just noticed that there are signed values, so, the following function will generate the correct IP address value:

=IF(LEN(DEC2HEX(A3))=8,HEX2DEC(MID(DEC2HEX(A3),7,2))&"."& HEX2DEC(MID(DEC2HEX(A3),5,2))&"."&HEX2DEC(MID(DEC2HEX(A3),3,2))&"."&HEX2DEC(MID(DEC2HEX(A3),1,2)),HEX2DEC(MID(DEC2HEX(A3),9,2))&"."& HEX2DEC(MID(DEC2HEX(A3),7,2))&"."&HEX2DEC(MID(DEC2HEX(A3),5,2))&"."&HEX2DEC(MID(DEC2HEX(A3),3,2)))

Where A3 = The Excel Filed to be converted (the decimal IP Address)

[Mary Manly]

Muhammad Abdul, Awesome work, THANK YOU!!

[Muhammad Al-Hussein]

Thank You Mary for replying, I actually forgot this formula and I think I lost it, I'm quite happy to find it again so that I do not have to do the same work again :-D

Really appreciate your tag :-D

[zpalmer]

This is awesome work  @Muhammad Al-Hussein Worked perfectly for me.

[Muhammad Al-Hussein]

You are welcome @zpalmer :-)

[Maximilian Meisel]

Hello I came across the same problem and I have the difficulty that my Excel is German (deutsch).

Unfortunately the commands are different, first of all the syntax from Muhammad Abdul is correct, so the german version of this formel is:

=WENN(LÄNGE(DEZINHEX(A3))=8;HEXINDEZ(TEIL(DEZINHEX(A3);7;2))&"."& HEXINDEZ(TEIL(DEZINHEX(A3);5;2))&"."&HEXINDEZ(TEIL(DEZINHEX(A3);3;2))&"."&HEXINDEZ(TEIL(DEZINHEX(A3);1;2));HEXINDEZ(TEIL(DEZINHEX(A3);9;2))&"."& HEXINDEZ(TEIL(DEZINHEX(A3);7;2))&"."&HEXINDEZ(TEIL(DEZINHEX(A3);5;2))&"."&HEXINDEZ(TEIL(DEZINHEX(A3);3;2)))

Hope this hellps to save some minutes.

Thanks Muhammad Abdul

Excel CDRs von Ganzzahl in IP Adressen Format Umwandeln.

[Finnur Eiriksson]

If someone is looking for a javascript one liner to do the same, here it is:

dec.toString(16).match(/(\w{2,2})/g).reverse().slice(0,4).map(d => parseInt(d, 16)).join('.')

Where dec is the ip address in decimal format.

/Finn

[Sinisa Hreljac]

I found this interesting topic as I have same problem, but I need this conversion to do in SQL query as we have CDR to SQL automatic import.

How to do that in SQL query?