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