Cisco Call Reporting in Excel

Post date: Feb 14, 2019 10:16:59 PM

Save $1,200/Year or More

For $1,200/year (or more) SideViewApps can provide call reporting for Cisco phone systems. I'm sure it is a lot better than what I'm about to share. But what I'm about to share is all we (my employer) need - a simple way to determine who called who, when, and for how long. So if you are like us, your Excel skills can save your company $1,200/year of more.

Requisites

• • Excel w/PowerQuery (Included w/Office 365, Excel 2016 & 2019. Available as an add in for Excel 2010)

  • Authorization to Cisco Unified Communications Manager and Cisco Analysis and Reporting

Getting Call Logs Out of CUCM

The first step is to get the call logs from Cisco CDR. You will need to know the URL to your Cisco Analysis and Reporting (CAR) application. In our installation it is on our Cisco Unified Communications Manager in sub-directory CAR which looks like this: https://cucm1/car

Use your favorite browser, navigate to the URL and log in.

Use menu option CDR > Export CDR/CMR

Getting Call Logs Into Excel

Open a new workbook and start Power Query

• • In Office 365: Click Data > Get Data > From Other Sources > Blank Query

  • In XL 2016: Click Data > New Query > From Other Sources > Blank Query

  • In XL 2013: Click Power Query > Launch Editor

See: Getting Started With Power Query

Find the new query (Query1) on the left side. Click It. On the right side rename it CDR. We will come back to it in a minute but first I prefer to create a function to handle CDR's odd date format. Having a function to do this just makes it a bit easier if we have to convert CDR dates in another report.

Create fnCDRDate2Date

This function converts CDR's date field, which is in seconds elapsed from Jan. 1 1970, to a normal date field. To create this function click the Home tab > New Source > Other Sources > Blank Query. On the right side should be a panel labeled Query Settings. Change the Name (under PROPERTIES) to fnCDRDate2Date. Now click on the Home tab Advanced Editor. Copy this code and paste it into Advanced Editor's code window.

/*

Description:Convert CDR Date to Date

Requisites: *None

Inputs: CDR Date value

Outputs: Date Value

Date Ini Description

02/14/19 CWH Original Development

*/

(CDRDate as number) =>

let

Result = DateTime.FromText("1970-01-01T00:00:00") + #duration(0, 0, 0, CDRDate)

in

Result

Close the code window

Create Query CDR

On the left side is a panel labeled Queries. Make sure it is open and find CDR. Click it then click on the Home tab Advanced Editor. Copy this code and paste it into Advanced Editors code window.

let

Source = Csv.Document(File.Contents("C:\Temp\CDR.txt"),[Delimiter=",",

Columns=123, Encoding=1252, QuoteStyle=QuoteStyle.None]),

SetHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

ChgType1 = Table.TransformColumnTypes(SetHeaders,{{"duration", Int64.Type},

{"dateTimeOrigination", Int64.Type}}),

AddColumn = Table.AddColumn(ChgType1, "Call Start",

each fnCDRDate2Date([dateTimeOrigination])),

ChgType2 = Table.TransformColumnTypes(AddColumn,{{"Call Start", type datetime}}),

RmvColumns = Table.SelectColumns(ChgType2,

{"callingPartyNumber", "callingPartyUnicodeLoginUserID",

"originalCalledPartyNumber", "finalCalledPartyNumber",

"finalCalledPartyUnicodeLoginUserID", "duration", "Call Start"}),

RnmColumns = Table.RenameColumns(RmvColumns,

{{"callingPartyUnicodeLoginUserID", "Calling User"},

{"finalCalledPartyUnicodeLoginUserID", "Final Called User"},

{"callingPartyNumber", "Calling Number"},

{"originalCalledPartyNumber", "Original Called Number"},

{"finalCalledPartyNumber", "Final Called Number"},

{"duration", "Duration"}}),

OrdColumns = Table.ReorderColumns(RnmColumns,

{"Call Start", "Calling Number",

"Calling User", "Original Called Number",

"Final Called Number", "Final Called User", "Duration"}),

SrtRows = Table.Sort(OrdColumns,{{"Call Start", Order.Ascending}})

in

SrtRows

Close the code window then click on the Home tab Close & Load

Format the table to suit. Add slicers if you like. Save your workbook.

Repeatable Success

When next month comes around, repeat steps in Getting Call Logs Out of CUCM then just open your workbook and click Data > Refresh All.

Keep XL'n.

Keywords: BXL Beyond Excel, Excel, VBA, XML, Cisco, Call Manager