cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
7982
Views
55
Helpful
11
Replies

CDR Export CUCM - IP address format

mmedwid
Level 3
Level 3

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.


1 Accepted Solution

Accepted Solutions

Jonathan Schulenberg
Hall of Fame
Hall of Fame

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.

View solution in original post

11 Replies 11

Jonathan Schulenberg
Hall of Fame
Hall of Fame

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.

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

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

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)

Muhammad Abdul, Awesome work, THANK YOU!!

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

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

You are welcome @zpalmer :-)

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.

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
Level 1
Level 1

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?