[cisco-voip] DestIPAddr field in CDR record

[Frutin, Amy]

There's some info in the 'CallManager Best Practices' book on using an
Excel formula to convert the 32-bit signed integer value in CDR to
dotted-decimal format:

--------------------

The following steps show you how to convert an IP address to
human-readable format. After you have exported a CDR into an Excel file,
open the spreadsheet, add a row at the top, and follow these steps:

Step 1. Install or verify that the optional Analysis ToolPak package has
been installed (check for the Analysis ToolPak under Tools > Add-Ins).
Without the optional ToolPak installed, the formula does not operate.
You can install the Analysis ToolPak from the Microsoft Office Suite
installations CDs or check the Microsoft Office\Office\ Library\Analysis
directory.

Step 2. Insert the following long formula into the cell to display the
value as an IP address. Replace all four instances of H3 with the field
in the spreadsheet that you want to convert to a human-readable form:

=CONCATENATE(HEX2DEC(MID((DEC2HEX(ABS(H3),8)),7,2)),".",HEX2DEC(MID((DEC
2HEX(ABS(H3),8)),5,2)),".",HEX2DEC(MID((DEC2HEX(ABS(H3),8)),3,2)),".",HE
X2DEC(MID((DEC2HEX(ABS(H3),8)),1,2)))

The IP address is displayed in dotted-decimal format.

--------------------

 

I found that it wasn't calculating correctly in some cases, depending on
the length of the integer, so I ended up using this even longer ugly
formula (I wanted to convert all the IP addresses in Column H of my
exported data, so I inserted a column to the right of that, and copied
the formula all the way down):

 

=CONCATENATE(HEX2DEC(RIGHT(DEC2HEX(H2,8),2)),".",HEX2DEC(MID(DEC2HEX(H2,
8),LEN(DEC2HEX(H2,8))-3,2)),".",HEX2DEC(MID(DEC2HEX(H2,8),LEN(DEC2HEX(H2
,8))-5,2)),".",HEX2DEC(MID(DEC2HEX(H2,8),LEN(DEC2HEX(H2,8))-7,2)))


CONFIDENTIALITY STATEMENT
This communication and any attachments are CONFIDENTIAL and may
be protected by one or more legal privileges. It is intended
solely for the use of the addressee identified above. If you
are not the intended recipient, any use, disclosure, copying
or distribution of this communication is UNAUTHORIZED. Neither
this information block, the typed name of the sender, nor
anything else in this message is intended to constitute an
electronic signature unless a specific statement to the
contrary is included in this message. If you have received this
communication in error, please immediately contact me and delete
this communication from your computer. Thank you.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: https://puck.nether.net/pipermail/cisco-voip/attachments/20070801/e8a48f9c/attachment.html