Reporting needs after upgrading from 4.1 to 7.1

Answered Question
Mar 30th, 2010
User Badges:

We use to run reports stright from the SQL database used in 4.1 and below. The query was


     QRY_CDR

bcp "Select dateTimeOrigination,

        callingPartyNumber,

        originalCalledPartyNumber,

        finalCalledPartyNumber,

        origDeviceName,

        destDeviceName,

        duration,

        'CM server'

   from CDR..CallDetailRecord

  Where dateTimeOrigination >= ${StartTmStamp}"

 

     QRY_NonPhoneList  

bcp "select

          left(d.Description,4),

          d.Description,

          dp.Name,

          d.Name

        from CCM0300..Device d left outer join

              CCM0300..DevicePool dp on d.fkDevicePool=dp.pkid

       where tkClass <> 1

          and dp.Name in ('KFX','MBX','PMM','WD')

          and d.Description is not NULL" 

     QRY_PhoneList

bcp "select

        left(d.Description,4),

        d.Description,

        dp.Name,

        d.Name

       from CCM0300..Device d left outer join

            CCM0300..DevicePool dp on d.fkDevicePool=dp.pkid

      where tkClass = 1

        and dp.Name in ('KFX','MBX','PMM','WD')

        and d.tkModel not in (20,61,72)

        and (right(left(d.Description,4),1) >= '0')

        and (right(left(d.Description,4),1) <= '9')"     

Now, with 7.1, this is no longer possible. I am trying to see if I can get this from a CDR dump but everything does not seem to be there. Does anyone know a good way to get a dump of the 7.1 data so I can try to create the same query? Is there an application out there that can pull all of the data? This is part of what we need to report back to our clients and I have been trying for a month to pull this out.

Thanks for any ideas!

Dave

Correct Answer by Aaron Harrison about 7 years 3 months ago

Hi


There is an Enterprise Parameter (System/Ent Params in ccmadmin) which is set to 1 minute by default - it's CDR File Time Interval.


Change that to something larger...


Regards


Aaron


Please rate helpful posts...

Correct Answer by Aaron Harrison about 7 years 3 months ago

Hi


Yeah, that's your best option. CCM doesn't load CDR into a DB any more, and you can't get at it through AXL either (well, unless you count CAR, but I don't think you can query that direct either).


You'll need to set up an FTP server as a billing server, CCM will export CSVs, and then you'll have to import those to a DB and report from there.


Regards


Aaron


Please rate helpful posts...

Correct Answer by snooter about 7 years 3 months ago

Hi Dave.  I went through the same thing myself about two years ago.  Our whole reporting scheme was completly rewritten after the upgrade. I searched all over the place for a way to directly query the UCM, but nothing ever panned out.  Again, that was two years ago so there may be third party software out there now..??


Anyway, I ended up doing a monthly CDR dump, followed by importing into Excel, formatting, and finally a dump into mysql, and that's how we get our reports now.  I know this doesn't help your question directly, but just thought I'd share as I've already went down that road.  It's a pain to have to manually do this every month..  Here's to hoping you find a better way!

  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 5 (3 ratings)
Loading.
Correct Answer
snooter Tue, 03/30/2010 - 08:59
User Badges:

Hi Dave.  I went through the same thing myself about two years ago.  Our whole reporting scheme was completly rewritten after the upgrade. I searched all over the place for a way to directly query the UCM, but nothing ever panned out.  Again, that was two years ago so there may be third party software out there now..??


Anyway, I ended up doing a monthly CDR dump, followed by importing into Excel, formatting, and finally a dump into mysql, and that's how we get our reports now.  I know this doesn't help your question directly, but just thought I'd share as I've already went down that road.  It's a pain to have to manually do this every month..  Here's to hoping you find a better way!

Correct Answer
Aaron Harrison Tue, 03/30/2010 - 09:20
User Badges:
  • Super Bronze, 10000 points or more
  • Community Spotlight Award,

    Member's Choice, May 2015

Hi


Yeah, that's your best option. CCM doesn't load CDR into a DB any more, and you can't get at it through AXL either (well, unless you count CAR, but I don't think you can query that direct either).


You'll need to set up an FTP server as a billing server, CCM will export CSVs, and then you'll have to import those to a DB and report from there.


Regards


Aaron


Please rate helpful posts...

David Graham Wed, 03/31/2010 - 12:34
User Badges:

You both confirmed my worst fears. Well, maybe not my worse but just the most annoying. I have had the billing going out to a sftp server, but it is doing that every minute. Now to figure out ow to get just a nightly one time dump and I will be good.


Thanks for all the help!


Dave

Correct Answer
Aaron Harrison Wed, 03/31/2010 - 12:39
User Badges:
  • Super Bronze, 10000 points or more
  • Community Spotlight Award,

    Member's Choice, May 2015

Hi


There is an Enterprise Parameter (System/Ent Params in ccmadmin) which is set to 1 minute by default - it's CDR File Time Interval.


Change that to something larger...


Regards


Aaron


Please rate helpful posts...

Actions

This Discussion