01-03-2011 10:06 AM - edited 03-16-2019 02:40 AM
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.
Solved! Go to Solution.
01-03-2011 10:18 AM
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.
01-03-2011 10:18 AM
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.
01-03-2011 10:28 AM
That's a huge supply of CDR info. Jackpot! Thank you.
02-20-2013 09:17 AM
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
02-21-2013 03:10 AM
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)
06-27-2017 11:58 AM
Muhammad Abdul, Awesome work, THANK YOU!!
06-27-2017 12:04 PM
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
08-15-2019 08:41 AM
This is awesome work @Muhammad Al-Hussein Worked perfectly for me.
09-06-2019 06:28 AM
You are welcome @zpalmer :-)
08-11-2015 08:07 AM
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.
10-17-2020 03:56 AM - edited 10-17-2020 04:00 AM
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
10-22-2021 03:46 AM
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?
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide