[cisco-voip] CDR question..

[Tim Reimers]

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 <mailto:tim.reimers at asheville.k12.nc.us>

		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/d1543e38/attachment-0001.html