<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2900.2802" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV dir=ltr align=left><SPAN class=995524421-24012006><FONT face=Arial
color=#0000ff size=2>Hi Lelio, et al...</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=995524421-24012006><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=995524421-24012006><FONT face=Arial
color=#0000ff size=2>At great length, I'm getting back to this-- having
lost the time to do it right after everyone provided such good
docs...</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=995524421-24012006><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=995524421-24012006><FONT face=Arial
color=#0000ff size=2>couple of small questions... REALLY newbie
ones----</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=995524421-24012006><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=995524421-24012006><FONT face=Arial
color=#0000ff size=2>what are you guys using to run the SQL queries? What table
are you querying?</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=995524421-24012006><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=995524421-24012006><FONT face=Arial
color=#0000ff size=2>Am I missing something about some basic tool available on
the CCM to do actual queries with?</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=995524421-24012006><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=995524421-24012006><FONT face=Arial
color=#0000ff size=2>I have SQL Server 2005 Management console available on my
PC and can browse the CCM tables---</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=995524421-24012006><FONT face=Arial
color=#0000ff size=2>but I don't see a table clearly marked 'CDR-anything'
....</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=995524421-24012006><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=995524421-24012006><FONT face=Arial
color=#0000ff size=2>Not only that, but I'm feeling as though I''m
perhaps using '$25,000 LAN tester to figure out a broken wire'
approach?</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=995524421-24012006><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=995524421-24012006><FONT face=Arial
color=#0000ff size=2>-- perhaps there's a smaller easier to use tool smoewhere
in CCMland that I don't know where is???</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=995524421-24012006><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=995524421-24012006><FONT face=Arial
color=#0000ff size=2>Tim</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=995524421-24012006><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=995524421-24012006><FONT face=Arial
color=#0000ff size=2></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 12:00 PM<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></BODY></HTML>