Exporting CDR in CUCM 11.5

[RedK25524]

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?

Thanks!

[Roger Kallberg]

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.

[RedK25524]

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?

[Roger Kallberg]

Hi,

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

[RedK25524]

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.

Thanks again!

[Roger Kallberg]

That's an unrelated setting for this. You'd want to change the Enterprise parameter that I wrote about.

[RedK25524]

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?

[Roger Kallberg]

It affects the data load into CM CDR Analysis and Reporting, also known as CAR.

[damianh]

Hi RedK25524

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

Example: Users 

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.