cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
534
Views
0
Helpful
5
Replies

Reporting needs after upgrading from 4.1 to 7.1

David Graham
Level 1
Level 1

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

3 Accepted Solutions

Accepted Solutions

snooter
Level 1
Level 1

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!

View solution in original post

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

Aaron Please remember to rate helpful posts to identify useful responses, and mark 'Answered' if appropriate!

View solution in original post

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

Aaron Please remember to rate helpful posts to identify useful responses, and mark 'Answered' if appropriate!

View solution in original post

5 Replies 5

snooter
Level 1
Level 1

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!

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

Aaron Please remember to rate helpful posts to identify useful responses, and mark 'Answered' if appropriate!

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

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

Aaron Please remember to rate helpful posts to identify useful responses, and mark 'Answered' if appropriate!

Well, that was easy! Thanks again!

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: