Monday, January 16, 2012

Lync CDR Report

Every Lync deployment should contain a monitoring server. The standard reports are good as well but it's not very handy. Most of the time the content is copied to Excel or the report is saved as Excel document. It takes a lot of time so why not create your own report in 3 steps with the specific CDR information you needed directly into Excel?

Step 1:
If you read my post about RGS monitoring you know that there is an extra database created with the name gngacd. Here we created 2 new views and 1 function which should be added to this database.

Download here.


Step 2:
The next step is to add a database source in Excel.
!! You need to install Power Pivot for Excel because there is too much data fetched from the database what is out of range for Excel. www.powerpivot.com

Start Excel and go to the Power Pivot tab.


Create a new database connection.


Enter your Lync SQL Backend Server Name and select the gngacd database, this database contains the created views and function.


Select the first option.


Select OutGoingCalls.


After fetching data it should said succeed.

Here an example of the CDR data imported in Power Pivot for Excel.
Step 3:
Now you need to add 3 extra colums with the folowing information:

1th column name = TariefMobiel (Per-Minute Rate for GSM talk)
=0.15/60

2th column name = TariefNationaal (Per-Minute Rate for National Talk)
=0.05/60

3th column name = Kosten (Total Cost)
=IF(EXACT(OutGoingCalls[TariefLabel],"Mobiele nummers"),OutGoingCalls[Duration]*OutGoingCalls[TariefMobiel],IF(EXACT(OutGoingCalls[TariefLabel],"Nationaal"),OutGoingCalls[Duration]*OutGoingCalls[TariefNationaal],BLANK()))


The next step is to create a new pivot table in Excel.


Create a Pivot Table.


Click OK.


As you can see the Pivot Table is using Power Pivot as soure.


Configure the Power Pivot fields as I did.


The column with the green box contains outgoing National calls, format is Number of calls - Number of seconds - Costs

There are also 3 other types of calls like Free Service Numbers, International Calls and Mobile Calls.

So enjoy this selfmade reporting tool.

Note:
The created a SQL query which actually runs a LDAP query to get all Enterprise Voice enabled users from Active Directory needs some improvement.

2 comments:

  1. As a Newbie, I am constantly browsing online for articles that can benefit me. Thank you
    Puma Women's Soccer Pant

    ReplyDelete
  2. The tutorial is incomplete.

    No mention of linked servers or other options

    ReplyDelete