[cisco-voip] CDR question..

[Stu Packett]

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.

________________________________

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 9:00 AM
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
--------------------------------------------------------

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.
--------------------------------------------------------

ATTENTION:
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.
 
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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: https://puck.nether.net/pipermail/cisco-voip/attachments/20051204/cae5638d/attachment-0001.html