<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.2802" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT size=2><EM>Remember, be careful while in the SQL tables. You can
really hurt/kill/screwup your telephony deployment. Typically with queries the
worst you could do is drive CPU load, but still, be careful. Caveat Emptor...
;)</EM></FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<UL>
<LI><FONT size=2>From the publisher, <STRONG>Start > Programs >
Microsoft SQL Server > Enterprise Manager</STRONG></FONT></LI>
<LI><FONT size=2>Find the publisher in the SQL server group listing. If you
have not done anything here, it might say local server or something like
that.</FONT></LI>
<LI><FONT size=2>Browse through <STRONG>Databases > CDR >
Tables</STRONG>, you'll see <STRONG>CallDetailRecord</STRONG>, this is the CDR
table. From here you can do queries, etc.</FONT></LI></UL>
<DIV><FONT size=2>What we did is create a local package, then schedule it. I
ended up going into the scheduled jobs to add a few extra steps to connect to a
backup data source, but it was pretty simple after that.</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2>You might also want to hunt around in your IT group for some
SQL help. What I know is based on copy/paste technology. Comes from the days of
my COBOL course and our mantra, "It's not how good you can program, it's how
good you can sort through the recycling bin." ;)</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV>--------------------------------------------------------------------------------<BR>Lelio
Fulgenzi, B.A.<BR>Network Analyst (CCS) * University of Guelph * Guelph, Ontario
N1G 2W1<BR>(519) 824-4120 x56354 (519) 767-1060 FAX
(JNHN)<BR>^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
<BR>"I had a coffee and Coke at lunch today...and now, I've got more jitter than
an<BR>IP phone on a long haul 10base2
connection"
LFJ</DIV>
<BLOCKQUOTE dir=ltr
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; font-color: black"><B>From:</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>To:</B> <A title=lelio@uoguelph.ca
href="mailto:lelio@uoguelph.ca">Lelio Fulgenzi</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Cc:</B> <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> Tuesday, January 24, 2006 4:49
PM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> RE: [cisco-voip] CDR
question..</DIV>
<DIV><BR></DIV>
<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></BLOCKQUOTE></BODY></HTML>