<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:Arial;
color:windowtext;}
p.doctext, li.doctext, div.doctext
{mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:10.0pt;
font-family:Verdana;
color:#333333;}
span.docemphstrong1
{font-weight:bold;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>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:<o:p></o:p></span></font></p>
<p class=doctext style='margin:0in;margin-bottom:.0001pt'><font size=2
color="#333333" face=Arial><span style='font-size:10.0pt;font-family:Arial'>--------------------<o:p></o:p></span></font></p>
<p class=doctext><font size=2 color="#333333" face=Arial><span
style='font-size:10.0pt;font-family:Arial'>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:<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-bottom:12.0pt'><a name=ch11pr05></a><b><font
size=2 color="#333333" face=Arial><span style='font-size:10.0pt;font-family:
Arial;color:#333333;font-weight:bold'>Step 1.</span></font></b><b><font size=2
color="#333333" face=Verdana><span style='font-size:10.0pt;font-family:Verdana;
color:#333333;font-weight:bold'> </span></font></b><font size=2 color="#333333"
face=Arial><span style='font-size:10.0pt;font-family:Arial;color:#333333'>Install
or verify that the optional Analysis ToolPak package has been installed (check
for the Analysis ToolPak under <span class=docemphstrong1><b>Tools > Add-Ins</b></span>).
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.<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-bottom:12.0pt'><b><font size=2 color="#333333"
face=Arial><span style='font-size:10.0pt;font-family:Arial;color:#333333;
font-weight:bold'>Step 2.</span></font></b><b><font size=2 color="#333333"
face=Verdana><span style='font-size:10.0pt;font-family:Verdana;color:#333333;
font-weight:bold'> </span></font></b><font size=2 color="#333333" face=Arial><span
style='font-size:10.0pt;font-family:Arial;color:#333333'>Insert the following
long formula into the cell to display the value as an IP address. Replace all
four instances of <b><span style='font-weight:bold'>H3</span></b> with the
field in the spreadsheet that you want to convert to a human-readable form:<br>
</span></font><font size=2 color="#333333" face=Verdana><span style='font-size:
10.0pt;font-family:Verdana;color:#333333'><br>
</span></font><font size=2 color="#333333" face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:#333333'>=CONCATENATE(HEX2DEC(MID((DEC2HEX(ABS(<span
class=docemphstrong1><b>H3</b></span>),8)),7,2)),".",HEX2DEC(MID((DEC2HEX(ABS(<span
class=docemphstrong1><b>H3</b></span>),8)),5,2)),".",HEX2DEC(MID((DEC2HEX(ABS(<span
class=docemphstrong1><b>H3</b></span>),8)),3,2)),".",HEX2DEC(MID((DEC2HEX(ABS(<span
class=docemphstrong1><b>H3</b></span>),8)),1,2)))<o:p></o:p></span></font></p>
<p class=doctext><font size=2 color="#333333" face=Arial><span
style='font-size:10.0pt;font-family:Arial'>The IP address is displayed in
dotted-decimal format.<a name=ch11index254></a><a name=ch11index253></a><a
name=ch11index252></a><a name=ch11index251></a><a name=ch11index250></a><a
name=ch11index249></a><o:p></o:p></span></font></p>
<p class=doctext style='margin:0in;margin-bottom:.0001pt'><font size=2
color="#333333" face=Arial><span style='font-size:10.0pt;font-family:Arial'>--------------------<o:p></o:p></span></font></p>
<p class=doctext style='margin:0in;margin-bottom:.0001pt'><font size=2
color="#333333" face=Arial><span style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=doctext style='margin:0in;margin-bottom:.0001pt'><font size=2
color="#333333" face=Arial><span style='font-size:10.0pt;font-family:Arial'>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):<o:p></o:p></span></font></p>
<p class=doctext style='margin:0in;margin-bottom:.0001pt'><font size=2
color="#333333" face=Arial><span style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=doctext style='margin:0in;margin-bottom:.0001pt'><font size=2
color="#333333" face=Arial><span style='font-size:10.0pt;font-family:Arial'>=CONCATENATE(HEX2DEC(RIGHT(DEC2HEX(<b><span
style='font-weight:bold'>H2</span></b>,8),2)),".",HEX2DEC(MID(DEC2HEX(<b><span
style='font-weight:bold'>H2</span></b>,8),LEN(DEC2HEX(<b><span
style='font-weight:bold'>H2</span></b>,8))-3,2)),".",HEX2DEC(MID(DEC2HEX(<b><span
style='font-weight:bold'>H2</span></b>,8),LEN(DEC2HEX(<b><span
style='font-weight:bold'>H2</span></b>,8))-5,2)),".",HEX2DEC(MID(DEC2HEX(<b><span
style='font-weight:bold'>H2</span></b>,8),LEN(DEC2HEX(<b><span
style='font-weight:bold'>H2</span></b>,8))-7,2)))<o:p></o:p></span></font></p>
</div>
<P><FONT face=Arial color=#808080 size=1>
<HR>
</FONT></P>
<P><FONT color=#808080><FONT face=Arial size=1>CONFIDENTIALITY STATEMENT<BR>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.</FONT> </FONT></P>
<P><FONT color=#808080>
<HR>
</FONT></P>
</body>
</html>