Reporting needs after upgrading from 4.1 to 7.1

Answered Question
Mar 30th, 2010

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 6 years 10 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 6 years 10 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 6 years 10 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

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

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

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

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