Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. And see here for current known issues.

New Member

Reporting needs after upgrading from 4.1 to 7.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
New Member

Re: Reporting needs after upgrading from 4.1 to 7.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!

Super Bronze

Re: Reporting needs after upgrading from 4.1 to 7.1

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!
Super Bronze

Re: Reporting needs after upgrading from 4.1 to 7.1

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!
5 REPLIES
New Member

Re: Reporting needs after upgrading from 4.1 to 7.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!

Super Bronze

Re: Reporting needs after upgrading from 4.1 to 7.1

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!
New Member

Re: Reporting needs after upgrading from 4.1 to 7.1

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

Super Bronze

Re: Reporting needs after upgrading from 4.1 to 7.1

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!
New Member

Re: Reporting needs after upgrading from 4.1 to 7.1

Well, that was easy! Thanks again!

246
Views
0
Helpful
5
Replies
CreatePlease to create content