My Data Analytics team are asking for an automated process for getting CDR. They want it dumped to a specific network file location. I can only find instructions for sending to a billing server, Hostname/IP, login credentials, directory path. What are my options if not sending to a server, just a location?
The "Billing Server" in CUCM is nothing more than a SFTP or FTP server where you upload the CDRs to. What you do with it later on is of your choice. If you can run a (S)FTP server software on the server where your Data Analytics team want's it stored you should be all good. If not you'd need to move/copy it from the location where the (S)FTP stores it to where the final destination is.
Roger, thanks for the reply. I did get a SFTP server up and were are sending the CDR data. Unfortunately, they don't like the frequency of the file send as it is every minute. I have made adjustments to the CDR Load schedule, but the result was the same. So my new question is, does that schedule impact when the data is sent to the configured billing server or to the CAR database?
What value did you try to alter for this? Based on the help readme this would be what you'd want to modify if you want a longer time period between the files sent to the billing server.
From Serviceability > Tools > CDR Management help page
From Enterprise parameters
This is not what I was editing. I was changing parameters in Serviceability for the CDR Load schedule.
But changing this did not have an impact on the sending of files to the configured SFTP server. I suspect it only changes the writing of CDR files to the CAR database.
I will make an adjustment to the Enterprise parameters and see what happens.
After speaking with others on our team, we decided not to update that setting. There are instances where more immediate access to CDR for troubleshooting and other issues are needed. We wouldn't want to wait to be able to pull raw cdr if needed.
What exactly does the CDR Load schedule do then?
There is another option to get the CDR data. It is more complex but you may want to give it a try.
1. Run sql query on CUCM Pub e.g. once a day out of hours. Please check two tables: tbl_billing_data & tbl_billing_error
a)An example for the first CDR from tbl_billing_data
run sql car SELECT datetimestamporigination,globalCallID_callId, origLegCallIdentifier, destLegIdentifier, user_id, callingPartyUnicodeLoginUserID, finalCalledPartyUnicodeLoginUserID, callingpartynumber, originalcalledpartynumber, finalcalledpartynumber, lastredirectdn, datetimestampconnect, datetimestampdisconnect, duration, origdevicename FROM tbl_billing_data where (datetimestamporigination between '2020-09-09 01:00:00' and '2020-09-09 23:00:00')
b) An example for the 2nd CDR from tbl_billing_error (Remember date & time in this table is in Unix format so you would need to convert it later, also you may see that duration may say 0 seconds, you still can get the "ringing" time buy subtracting "datetimeorigination" from "datetimedisconnect")
run sql car SELECT origdevicename,datetimeorigination, globalCallID_callId,origlegcallidentifier,destlegidentifier, callingpartyunicodeloginuserid, finalcalledpartyunicodeloginuserid, callingpartynumber, finalcalledpartynumber, duration, datetimeconnect, datetimedisconnect, originalcalledpartynumber from tbl_billing_error where (datetimeorigination >= 1599609660) and (datetimeorigination <= 1599692400)
You may want to run additional queries for all users, devices, extensions
run sql select tkcustomuserattribute, value, fkenduser from customuserattributedata
Example: devices + extensions + foreign keys
run sql select fkenduser, d.name as device, n.dnorpattern from device as d inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid inner join numplan as n on dmap.fknumplan=n.pkid
Please remember that all the outputs will be in "flat file" format so someone would need to format that but it's not that difficult.
---Now the tricky part is to automate the process so you do not have to run it manually.---
I have used batch files that included the query + plink.x64.exe to login to CUCM PUB and execute the query. You could then use Task Scheduler on Windows and schedule a time when the script needs to start and end. ! Of course feel free to use something else than batch files and plink.exe , etc.