<HTML >
<HEAD>
<META http-equiv="Content-Type" content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2900.2769" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV>
<DIV dir=ltr align=left><SPAN class=858314507-04122005><FONT face=Tahoma
color=#0000ff size=2>I just read Lelio's email after I sent my reply to
Tim. Lelio's SQL query is actually something I would prefer to run.
It converts it in the query which is one less step than what I had replied
to.</FONT></SPAN></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> cisco-voip-bounces@puck.nether.net
[mailto:cisco-voip-bounces@puck.nether.net] <B>On Behalf Of </B>Lelio
Fulgenzi<BR><B>Sent:</B> Saturday, December 03, 2005 9:00 AM<BR><B>To:</B>
cisco-voip@puck.nether.net<BR><B>Subject:</B> Re: [cisco-voip] CDR
question..<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV><FONT face=Arial size=2>I should have thought out my response a little more
clearly. That converter I use is a command line converter when I'm doing work
and troubleshooting. I use a similar awk statement when I'm doing my CDR
processing to save the epoch date for our admin staff so they CDR records have a
human readable format.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>As far as the SQL query goes, we run one every day
at 12:15am using a scheduled job and use the following query. This gives us
everything from the time the job is run to two weeks prior.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV>
<P style="MARGIN-TOP: 0px"><FONT face=monospace size=1>SELECT
[CallDetailRecord].[dateTimeOrigination],
<BR>
[CallDetailRecord].[callingPartyNumber],
<BR>
[CallDetailRecord].[originalCalledPartyNumber],
<BR>
[CallDetailRecord].[finalCalledPartyNumber],
<BR> [CallDetailRecord].[duration]
<BR>FROM [CallDetailRecord] <BR>WHERE (dateTimeOrigination
> DATEDIFF([SECOND], '1/1/1970', GETDATE()) - 1209600)</FONT></P>
<P style="MARGIN-TOP: 0px"> </P>
<P style="MARGIN-TOP: 0px">
<HR tabIndex=-1>
<P></P>
<P style="MARGIN-TOP: 0px"><FONT face=Tahoma size=2><B>From:</B> Lelio Fulgenzi
[mailto:lelio@uoguelph.ca]<BR><B>Sent:</B> Sat 12/3/2005 12:39 AM<BR><B>To:</B>
Tim Reimers; <A
href="mailto:gary.d.brinkman.jr@census.gov">gary.d.brinkman.jr@census.gov</A><BR><B>Cc:</B>
cisco-voip-bounces@puck.nether.net;
cisco-voip@puck.nether.net<BR><B>Subject:</B> Re: [cisco-voip] CDR
question..<BR></FONT><BR></P></DIV>
<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>
<DIV><FONT face=Arial size=2>I use this tiny awk script:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>[<EM>hostname</EM>]$ more
cdate<BR>#!/bin/sh<BR>echo $1 | /bin/awk '{ print strftime("%m/%d/%Y
%H:%M:%S",$1), $2, $3, $4, $5, $6}'<BR></FONT></DIV>
<DIV><FONT face=Arial size=2>and I call it with "cdate
<EM>epochtime</EM>"</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2> </DIV></FONT>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial"><B>From:</B> <A
title=gary.d.brinkman.jr@census.gov
href="mailto:gary.d.brinkman.jr@census.gov">gary.d.brinkman.jr@census.gov</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=tim.reimers@asheville.k12.nc.us
href="mailto:tim.reimers@asheville.k12.nc.us">Tim Reimers</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Cc:</B> <A
title=cisco-voip-bounces@puck.nether.net
href="mailto:cisco-voip-bounces@puck.nether.net">cisco-voip-bounces@puck.nether.net</A>
; <A title=cisco-voip@puck.nether.net
href="mailto:cisco-voip@puck.nether.net">cisco-voip@puck.nether.net</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Thursday, December 01, 2005 5:16
PM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: [cisco-voip] CDR
question..</DIV>
<DIV><BR></DIV>Not sure about 4.0 but on 3.3 you can run this query against
the CDR<BR>database and get what you need. Just change the
originalCalledPartyNumber<BR>to the number you need. And while
1128142800 is equivalent to 10/1/2005<BR>12:00 AM EST you may need to
get a Epoch time converter to get a
different<BR>time.<BR><BR><BR>SELECT
duration<BR>FROM
CallDetailRecord<BR>WHERE (dateTimeOrigination >
'1128142800') and<BR>(originalCalledPartyNumber like
'%8005551212')<BR><BR>-----<BR><BR>Gary D. Brinkman<BR>U.S. Census
Bureau<BR>Telecommunications
Office<BR><BR><BR><BR>
<BR>
"Tim
Reimers"
<BR>
<<A
href="mailto:tim.reimers@ashe">tim.reimers@ashe</A>
<BR>
ville.k12.nc.us>
To
<BR>
Sent
by:
<<A
href="mailto:cisco-voip@puck.nether.net">cisco-voip@puck.nether.net</A>>
<BR>
cisco-voip-bounce
cc
<BR>
<A
href="mailto:s@puck.nether.net">s@puck.nether.net</A>
<BR>
Subject
<BR>
[cisco-voip] CDR question..
<BR>
11/30/05 11:26
AM
<BR>
<BR>
<BR>
<BR>
<BR>
<BR><BR><BR><BR><BR>Hi everyone---<BR><BR>I have a CDR newbie kind of
question---<BR><BR>Can someone give me the steps needed to determine the
following: (CCM 4.0)<BR><BR>All calls to a given 800 number in say,
the last two months…<BR><BR>Number of calls<BR>Duration of each
call<BR><BR>If I can get it into Excel I can munge around the data to get
the right<BR>things I want in terms of averages, etc…<BR><BR>But I’ve not
done any CDR stuff as
yet..<BR> _______________________________________________<BR>cisco-voip
mailing list<BR><A
href="mailto:cisco-voip@puck.nether.net">cisco-voip@puck.nether.net</A><BR><A
href="https://puck.nether.net/mailman/listinfo/cisco-voip">https://puck.nether.net/mailman/listinfo/cisco-voip</A><BR><BR>_______________________________________________<BR>cisco-voip
mailing list<BR><A
href="mailto:cisco-voip@puck.nether.net">cisco-voip@puck.nether.net</A><BR><A
href="https://puck.nether.net/mailman/listinfo/cisco-voip">https://puck.nether.net/mailman/listinfo/cisco-voip</A><BR></BLOCKQUOTE></DIV></BLOCKQUOTE></DIV>
<DIV>
<HR>
</DIV>
<DIV>
<FONT FACE="Tahoma" COLOR="silver" SIZE="2">IRS Circular 230 Disclosure: To ensure compliance with requirements imposed by the IRS, we inform you that any U.S. federal tax advice in this communication (including attachments) is not intended or written by Fenwick & West LLP to be used, and cannot be used, for the purpose of (i) avoiding penalties under the Internal Revenue Code or (ii) promoting, marketing, or recommending to another party any transaction or matter addressed herein.</FONT>
</DIV>
<DIV>
<FONT FACE="Tahoma" COLOR="silver" SIZE="1">
<HR>
ATTENTION:<BR>The information contained in this message may be legally privileged and confidential. It is intended to be read only by the individual or entity to whom it is addressed or by their designee. If the reader of this message is not the intended recipient, you are on notice that any distribution of this message, in any form, is strictly prohibited.</FONT>
</DIV>
<DIV>
<FONT COLOR="silver">
</FONT>
<FONT FACE="Tahoma" SIZE="1"> </FONT>
</DIV>
<DIV>
<FONT FACE="Tahoma" COLOR="silver" SIZE="1">If you have received this message in error, please immediately notify the sender and/or Fenwick & West LLP by telephone at (650) 988-8500 and delete or destroy any copy of this message.</FONT>
</DIV></BODY></HTML>