[cisco-voip] CDR question..

[Lelio Fulgenzi]

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/20051203/87e5b403/attachment.html