03-30-2010 07:21 AM - edited 03-19-2019 12:42 AM
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
Solved! Go to Solution.
03-30-2010 08:59 AM
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!
03-30-2010 09:20 AM
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...
03-31-2010 12:39 PM
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...
03-30-2010 08:59 AM
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!
03-30-2010 09:20 AM
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...
03-31-2010 12:34 PM
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
03-31-2010 12:39 PM
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...
03-31-2010 12:42 PM
Well, that was easy! Thanks again!
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide