[cisco-voip] CDR question..
Lelio Fulgenzi
lelio at uoguelph.ca
Tue Jan 24 17:06:54 EST 2006
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... ;)
a.. From the publisher, Start > Programs > Microsoft SQL Server > Enterprise Manager
b.. 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.
c.. Browse through Databases > CDR > Tables, you'll see CallDetailRecord, this is the CDR table. From here you can do queries, etc.
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.
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." ;)
--------------------------------------------------------------------------------
Lelio Fulgenzi, B.A.
Network Analyst (CCS) * University of Guelph * Guelph, Ontario N1G 2W1
(519) 824-4120 x56354 (519) 767-1060 FAX (JNHN)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
"I had a coffee and Coke at lunch today...and now, I've got more jitter than an
IP phone on a long haul 10base2 connection" LFJ
----- Original Message -----
From: Tim Reimers
To: Lelio Fulgenzi
Cc: cisco-voip at puck.nether.net
Sent: Tuesday, January 24, 2006 4:49 PM
Subject: RE: [cisco-voip] CDR question..
Hi Lelio, et al...
At great length, I'm getting back to this-- having lost the time to do it right after everyone provided such good docs...
couple of small questions... REALLY newbie ones----
what are you guys using to run the SQL queries? What table are you querying?
Am I missing something about some basic tool available on the CCM to do actual queries with?
I have SQL Server 2005 Management console available on my PC and can browse the CCM tables---
but I don't see a table clearly marked 'CDR-anything' ....
Not only that, but I'm feeling as though I''m perhaps using '$25,000 LAN tester to figure out a broken wire' approach?
-- perhaps there's a smaller easier to use tool smoewhere in CCMland that I don't know where is???
Tim
------------------------------------------------------------------------------
From: cisco-voip-bounces at puck.nether.net [mailto:cisco-voip-bounces at puck.nether.net] On Behalf Of Lelio Fulgenzi
Sent: Saturday, December 03, 2005 12:00 PM
To: cisco-voip at puck.nether.net
Subject: Re: [cisco-voip] CDR question..
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.
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.
SELECT [CallDetailRecord].[dateTimeOrigination],
[CallDetailRecord].[callingPartyNumber],
[CallDetailRecord].[originalCalledPartyNumber],
[CallDetailRecord].[finalCalledPartyNumber],
[CallDetailRecord].[duration]
FROM [CallDetailRecord]
WHERE (dateTimeOrigination > DATEDIFF([SECOND], '1/1/1970', GETDATE()) - 1209600)
------------------------------------------------------------------------------
From: Lelio Fulgenzi [mailto:lelio at uoguelph.ca]
Sent: Sat 12/3/2005 12:39 AM
To: Tim Reimers; gary.d.brinkman.jr at census.gov
Cc: cisco-voip-bounces at puck.nether.net; cisco-voip at puck.nether.net
Subject: Re: [cisco-voip] CDR question..
I use this tiny awk script:
[hostname]$ more cdate
#!/bin/sh
echo $1 | /bin/awk '{ print strftime("%m/%d/%Y %H:%M:%S",$1), $2, $3, $4, $5, $6}'
and I call it with "cdate epochtime"
----- Original Message -----
From: gary.d.brinkman.jr at census.gov
To: Tim Reimers
Cc: cisco-voip-bounces at puck.nether.net ; cisco-voip at puck.nether.net
Sent: Thursday, December 01, 2005 5:16 PM
Subject: Re: [cisco-voip] CDR question..
Not sure about 4.0 but on 3.3 you can run this query against the CDR
database and get what you need. Just change the originalCalledPartyNumber
to the number you need. And while 1128142800 is equivalent to 10/1/2005
12:00 AM EST you may need to get a Epoch time converter to get a different
time.
SELECT duration
FROM CallDetailRecord
WHERE (dateTimeOrigination > '1128142800') and
(originalCalledPartyNumber like '%8005551212')
-----
Gary D. Brinkman
U.S. Census Bureau
Telecommunications Office
"Tim Reimers"
<tim.reimers at ashe
ville.k12.nc.us> To
Sent by: <cisco-voip at puck.nether.net>
cisco-voip-bounce cc
s at puck.nether.net
Subject
[cisco-voip] CDR question..
11/30/05 11:26 AM
Hi everyone---
I have a CDR newbie kind of question---
Can someone give me the steps needed to determine the following: (CCM 4.0)
All calls to a given 800 number in say, the last two months.
Number of calls
Duration of each call
If I can get it into Excel I can munge around the data to get the right
things I want in terms of averages, etc.
But I've not done any CDR stuff as yet..
_______________________________________________
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/20060124/5a478611/attachment.html
More information about the cisco-voip
mailing list