2007 Jun 06 - Wed

Writing Callmanager Call Detail Records to Excel Spreadsheet

In an earlier article, I described how to access the Cisco Callmanager CDR tables to obtain billing records through the use of the Sybase DBI drivers for Perl. The database needs to be placed into "mixed mode" for this type of thing to work.

I've created two perl modules to make use of this technique: cdr2file.pm and billing2file.pm.

cdr2file.pm takes as input three parameters: an extension, a starting date, and an ending date. It creates an Excel spreadsheet using a temporary file name, which allows multiple requests to be processed simultaneously. The spreadsheet contains four worksheets: a list of inbound calls, a list of local calls, a list of long distance calls, and a list of internationally placed calls. The selection is based upon four digit extensions and the North American Dialling plan. The duration of each call is included. Because both the 'Original Called' and the 'Final Called' fields are included, one can determine which calls ended up going to voicemail or transferred elsewhere.

The other module, billing2file.pm, takes as input a starting date and an ending date. For all outbound calls placed within that date range, a summary spreadsheet is created. The first worksheet contains a summary of call duration by user with the following fields: Extension Number, Name of the Phone, Total Duration in Seconds, Total Duration in Minutes. Each line contains a hyper link to a worksheet containing the actuall call details for that extension. On the detail worksheet for each user, the following details are provided: Date the call was placed, extension number originating the call, the original called number, the duration in seconds for the call, the phone identifier, and the phone description. This spreadsheet makes it convenient to produce monthly billing reports by user. For large companies, this may not be suitable as a spreadsheet may not be able to handle enough rows or worksheets (a limitation of Excel).

By creating suitable web pages, say through mod_perl or mason, members of the accounting department have direct, easy, live access to the Callmanager Call Detail Records in a ready to use format.

[/Cisco/Callmanager] permanent link