<br><font size=2 face="sans-serif">Using Matthew's excellent query and
the SQL built-in dateadd function, you can run queries with results for
human eyes.</font>
<br>
<br><font size=2 face="sans-serif">Install Matthew's function and then
run somthing like this:</font>
<br>
<br><font size=2 face="sans-serif">Select </font>
<br><font size=2 face="sans-serif"> dbo.decimaltoip(origipaddr)as
origipaddr, </font>
<br><font size=2 face="sans-serif"> dbo.decimaltoip(destipaddr)as
destipaddr, </font>
<br><font size=2 face="sans-serif"> callingpartynumber,
</font>
<br><font size=2 face="sans-serif"> originalcalledpartynumber,
</font>
<br><font size=2 face="sans-serif"> finalcalledpartynumber,
</font>
<br><font size=2 face="sans-serif"> dateadd(ss,datetimeconnect,'19700101'),
</font>
<br><font size=2 face="sans-serif"> dateadd(ss,datetimedisconnect,'19700101'),
</font>
<br><font size=2 face="sans-serif"> origdevicename,
</font>
<br><font size=2 face="sans-serif"> destdevicename</font>
<br>
<br><font size=2 face="sans-serif">From</font>
<br><font size=2 face="sans-serif"> calldetailrecord</font>
<br>
<br><font size=2 face="sans-serif">where</font>
<br><font size=2 face="sans-serif"> dateadd(ss,datetimeconnect,'19700101')
> '2007-08-01 16:20:51.000'</font>
<br>
<br><font size=2 face="sans-serif"><br>
<br>
<br>
Luis Yrigoyen<br>
<br>
Network Operations<br>
Bayview Financial, L.P.<br>
4425 Ponce de Leon Blvd.<br>
Coral Gables, FL 33146<br>
<br>
T 305.817.5083<br>
F 305.646.4744<br>
E luisyrigoyen@bayviewfinancial.com<br>
</font>
<br>
<br>
<br>
<table width=100%>
<tr valign=top>
<td width=40%><font size=1 face="sans-serif"><b>Matthew Saskin <matt@saskin.net></b>
</font>
<br><font size=1 face="sans-serif">Sent by: cisco-voip-bounces@puck.nether.net</font>
<p><font size=1 face="sans-serif">08/01/2007 09:41 AM</font>
<td width=59%>
<table width=100%>
<tr valign=top>
<td>
<div align=right><font size=1 face="sans-serif">To</font></div>
<td><font size=1 face="sans-serif">Lelio Fulgenzi <lelio@uoguelph.ca></font>
<tr valign=top>
<td>
<div align=right><font size=1 face="sans-serif">cc</font></div>
<td><font size=1 face="sans-serif">CarlosOrtiz@bayviewfinancial.com, cisco-voip@puck.nether.net</font>
<tr valign=top>
<td>
<div align=right><font size=1 face="sans-serif">Subject</font></div>
<td><font size=1 face="sans-serif">Re: [cisco-voip] DestIPAddr field in
CDR record</font></table>
<br>
<table>
<tr valign=top>
<td>
<td></table>
<br></table>
<br>
<br>
<br><font size=2><tt>IP addresses are stored in the cdr table in a decimal
format. Here is <br>
an (ugly) SQL server function I wrote a while back that does the <br>
conversion to dotted decimal format....once again, it's ugly and only <br>
relatively efficient, but it works :)<br>
<br>
-matt<br>
<br>
------------------------------------------------------------<br>
------------------------------------------------------------<br>
CREATE function dbo.decimalToIP(@ip INT)<br>
RETURNS VARCHAR(15)<br>
AS<br>
BEGIN<br>
<br>
DECLARE @binip BINARY(4)<br>
SET @binip = CONVERT(BINARY(4),@ip)<br>
<br>
DECLARE @charip varchar(8)<br>
SET @charip = CONVERT(varchar(8),@binip)<br>
<br>
DECLARE @1 int, @2 int, @3 int, @4 int<br>
<br>
SET @4 = ASCII(SUBSTRING(@charip,1,1))<br>
SET @3 = ASCII(SUBSTRING(@charip,2,1))<br>
SET @2 = ASCII(SUBSTRING(@charip,3,1))<br>
SET @1 = ASCII(SUBSTRING(@charip,4,1))<br>
<br>
DECLARE @output varchar(15)<br>
SET @output = CONVERT(varchar,@1) + '.' + CONVERT(varchar,@2) + '.' + <br>
CONVERT(varchar,@3) + '.' + CONVERT(varchar,@4)<br>
RETURN @output<br>
<br>
END<br>
------------------------------------------------------------<br>
------------------------------------------------------------<br>
<br>
<br>
<br>
<br>
Lelio Fulgenzi wrote:<br>
> The CAR/ART tool , whatever you call it.<br>
> <br>
> --------------------------------------------------------------------------------<br>
> Lelio Fulgenzi, B.A.<br>
> Senior Analyst (CCS) * University of Guelph * Guelph, Ontario N1G
2W1<br>
> (519) 824-4120 x56354 (519) 767-1060 FAX (JNHN)<br>
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^<br>
> "Buffalo buffalo Buffalo buffalo buffalo buffalo Buffalo buffalo."
WJR<br>
> <br>
> ----- Original Message -----<br>
> *From:* CarlosOrtiz@bayviewfinancial.com<br>
> <mailto:CarlosOrtiz@bayviewfinancial.com><br>
> *To:* Lelio Fulgenzi <mailto:lelio@uoguelph.ca><br>
> *Cc:* cisco-voip@puck.nether.net <mailto:cisco-voip@puck.nether.net><br>
> ; cisco-voip-bounces@puck.nether.net<br>
> <mailto:cisco-voip-bounces@puck.nether.net><br>
> *Sent:* Wednesday, August 01, 2007 9:27 AM<br>
> *Subject:* Re: [cisco-voip] DestIPAddr field in CDR
record<br>
> <br>
> <br>
> Which tool?<br>
> <br>
> <br>
> <br>
> <br>
> *"Lelio Fulgenzi" <lelio@uoguelph.ca <mailto:lelio@uoguelph.ca>>*<br>
> Sent by: cisco-voip-bounces@puck.nether.net<br>
> <mailto:cisco-voip-bounces@puck.nether.net><br>
> <br>
> 08/01/2007 09:23 AM<br>
> <br>
>
<br>
> To<br>
>
<CarlosOrtiz@bayviewfinancial.com<br>
> <mailto:CarlosOrtiz@bayviewfinancial.com>><br>
> cc<br>
>
cisco-voip@puck.nether.net <mailto:cisco-voip@puck.nether.net><br>
> Subject<br>
>
Re: [cisco-voip] DestIPAddr field in CDR record<br>
> <br>
> <br>
>
<br>
> <br>
> <br>
> <br>
> <br>
> <br>
> sorry, i was using the tool. it probably did the conversion
for me.<br>
> <br>
> --------------------------------------------------------------------------------<br>
> Lelio Fulgenzi, B.A.<br>
> Senior Analyst (CCS) * University of Guelph * Guelph,
Ontario N1G 2W1<br>
> (519) 824-4120 x56354 (519) 767-1060 FAX (JNHN)<br>
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^<br>
> "Buffalo buffalo Buffalo buffalo buffalo buffalo
Buffalo buffalo." <br>
> WJR<br>
> ----- Original Message -----<br>
> *From:* _CarlosOrtiz@bayviewfinancial.com_<br>
> <mailto:CarlosOrtiz@bayviewfinancial.com><br>
> *To:* _Lelio Fulgenzi_ <mailto:lelio@uoguelph.ca><br>
> *Cc:* _cisco-voip@puck.nether.net_ <mailto:cisco-voip@puck.nether.net><br>
> *Sent:* Wednesday, August 01, 2007 9:16 AM<br>
> *Subject:* Re: [cisco-voip] DestIPAddr field in CDR
record<br>
> <br>
> <br>
> I see a 9 digit numeric number. Are you looking
in the CDR<br>
> database, calldetailrecord table? I started searching
the archives<br>
> and I did find this email which you posted a reply to
!<br>
> <br>
> <br>
> *_lelio at uoguelph_*<br>
> <http://www.gossamer-threads.com/lists/engine?user=2532;list=cisco><br>
> <br>
> Sep 27, 2006, 5:00 PM<br>
> <br>
> Post #2 of 4 (258 views) _<br>
> __Permalink_<br>
> <http://www.gossamer-threads.com/lists/cisco/voip/52547#52547>
<br>
> *Re: CDR IP Address Conversion* _Remove Highlighting_<br>
> <http://www.gossamer-threads.com/lists/cisco/voip/52547?nohighlight=1#52547><br>
> [_In reply to_<br>
> <http://www.gossamer-threads.com/lists/cisco/voip/52546?search_string=CDR;#52546>]<br>
> <br>
> ------------------------------------------------------------------------<br>
> <br>
> <br>
> <br>
> <br>
> found this using google... _<br>
> __<br>
> __http://www.cisco.com/en/US/products/sw/voicesw/ps556/prod_troubleshooting_guide_chapter09186a0080382d82.html#wp1003527_<br>
> <br>
> <br>
> <br>
> ----- Original Message -----<br>
> From: STEVEN CASPER<br>
> To: cisco-voip[at]puck.nether.net<br>
> Sent: Wednesday, September 27, 2006 7:55 PM<br>
> Subject: [cisco-voip] *CDR* IP Address Conversion<br>
> <br>
> <br>
> <br>
> Does anyone have a tool or formula they could share
that can be used to<br>
> convert the signed decimal value in the *CDR* database
to an IP<br>
> address?<br>
> <br>
> Thanks!<br>
> Steve<br>
> <br>
> <br>
> <br>
> <br>
> <br>
> <br>
> <br>
> <br>
> *"Lelio Fulgenzi" <lelio@uoguelph.ca>*<br>
> Sent by: cisco-voip-bounces@puck.nether.net<br>
> <br>
> 08/01/2007 09:10 AM<br>
> <br>
>
<br>
> To<br>
>
<cisco-voip@puck.nether.net>, <CarlosOrtiz@bayviewfinancial.com><br>
> cc<br>
>
<br>
> Subject<br>
>
Re: [cisco-voip] DestIPAddr field in CDR record<br>
> <br>
> <br>
> <br>
>
<br>
> <br>
> <br>
> <br>
> <br>
> <br>
> <br>
> I see dotted decimal form a.b.c.d<br>
> <br>
> what do you see?<br>
> <br>
> --------------------------------------------------------------------------------<br>
> Lelio Fulgenzi, B.A.<br>
> Senior Analyst (CCS) * University of Guelph * Guelph,
Ontario N1G 2W1<br>
> (519) 824-4120 x56354 (519) 767-1060 FAX (JNHN)<br>
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^<br>
> "Buffalo buffalo Buffalo buffalo buffalo buffalo
Buffalo buffalo." <br>
> WJR<br>
> ----- Original Message ----- *<br>
> From:* _CarlosOrtiz@bayviewfinancial.com_<br>
> <mailto:CarlosOrtiz@bayviewfinancial.com> *<br>
> To:* _cisco-voip@puck.nether.net_ <mailto:cisco-voip@puck.nether.net>
*<br>
> Sent:* Wednesday, August 01, 2007 8:56 AM *<br>
> Subject:* [cisco-voip] DestIPAddr field in CDR record<br>
> <br>
> <br>
> When viewing the DestIPAddr for a call in the CDR database
of the<br>
> calldetailrecord table a numeric number is shown. Anyone
know how<br>
> to convert this to IP address format?<br>
> <br>
> Carlos<br>
> <br>
> ------------------------------------------------------------------------<br>
> <br>
> _______________________________________________<br>
> cisco-voip mailing list_<br>
> __cisco-voip@puck.nether.net_ <mailto:cisco-voip@puck.nether.net>_<br>
> __https://puck.nether.net/mailman/listinfo/cisco-voip________________________________________________<br>
> cisco-voip mailing list<br>
> cisco-voip@puck.nether.net<br>
> https://puck.nether.net/mailman/listinfo/cisco-voip_______________________________________________<br>
> cisco-voip mailing list<br>
> cisco-voip@puck.nether.net<br>
> https://puck.nether.net/mailman/listinfo/cisco-voip<br>
> <br>
> <br>
> ------------------------------------------------------------------------<br>
> <br>
> _______________________________________________<br>
> cisco-voip mailing list<br>
> cisco-voip@puck.nether.net<br>
> https://puck.nether.net/mailman/listinfo/cisco-voip<br>
<br>
<br>
_______________________________________________<br>
cisco-voip mailing list<br>
cisco-voip@puck.nether.net<br>
https://puck.nether.net/mailman/listinfo/cisco-voip<br>
</tt></font>
<br>