Lync 2013 CDR Report – Recharge Call Costs

I needed a method to recharge departments call costs from Microsoft Lync and came across the following article by Frederik Lefevre:

http://www.ucprofessional.com/2012/01/lync-cdr-report.html

I found this was exactly what I required but unfortunately the SQL views were not compatible with Lync 2013. This is a simple guide on how to get this working on Lync 2013 for call costs in the UK:

SQL Database Configuration

On the Lync Monitoring and Archiving SQL instance, create a Linked Server named “ADSI” with the following settings:

Configure Security so connections will be made using the security context: Enter an LDAP lookup service accounts credentials.

Once complete, right click ADSI and click “Test Connection”

Create a new database named LyncBilling on the same instance as your Lync LcsCDR database.

Create a view named “LyncEVUsers” (change domain name to suit your environment)

Create a view named “OutgoingCallsUK”

 

You should now have two views created in the LyncBilling database.


Create a report in Microsoft Excel:

Create a new Data Connection in Excel: Data > From Other Sources > From SQL Server

Enter SQL server name and instance and provide credentials:


Select the “LyncBilling” database and the “OutgoingCallsUK” table:


Click Finish.

Click OK on the Import Data window.

The billing data should import into Excel:

Rename Sheet1 to “Data”

Rename Sheet2 to “Tariffs” and populate the following rates:

Create two new columns in the Data sheet, “TotalCost” and “Month”

Formula for TotalCost:

Format TotalCost as currency

Formula for Month:

The Data sheet should now look like this:


Click in the top left corner of the Data sheet to highlight all cells.

Click “Insert” > “Pivot Table”

Configure the Pivot Table fields as follows:

Right click the Count of TotalCost field and select “Value Field Settings”. Select “Sum” and change the number format to currency. Rename the Pivot Table sheet to “Report”

You can now use the Pivot Table filters to find out call costs for Departments based on a particular Gateway and Month.

To refresh the data click “Data” > “Refresh” on the Data sheet, then repeat on the Report sheet:

Author: Chris Hayward

Share This Post On

11 Comments

  1. Hey,
    I hope you can help me. I was trying your tutorial but I stopped at the LyncEVUsers view because I don’t get my server right. Can you give me another example or can you tell me where I get the information of my server?
    Thanks,

    Raphael

    Post a Reply
    • Hi,

      In the LyncEVUsers view you need to change LDAP://DC=domain,DC=local to your Active Directory LDAP distinguished name.

      E.g if your AD DNS name is shyit.co.uk then you would change it to LDAP://DC=shyit,DC=co,DC=uk

      Thanks
      Chris

      Post a Reply
      • Hey Chris,
        after I changed the code into “LDAP://DC=Contoso,DC=com” I got a SQL Execution Error.
        Source: .Net SqlClient Data Provider
        Error Message:
        An error occurred while preparing the query “SELECT displayName, msRTCSIP-PrimaryUserAddress, department FROM ‘LDAP://DC=Contoso,DC=com’ WHERE objectClass = ‘User’ AND msRTCSIP-OptionFlags = 385” for execution against OLE DB provider “ADsDSOObject” for linked server “ADSI”.
        Do you have any idea?
        Thanks

        Post a Reply
  2. Sorry I’m not sure. Maybe check the SQL Linked Server. Was the connection test successful?

    Post a Reply
  3. This is great Chris. I’m trying to customize “OutgoingCallsUK” code to fit US dialing Local and international. Any help will be appreciated.

    Thanks

    Post a Reply
    • Hi,

      You will need to modify the CASE section of the SQL. I haven’t worked with US numbering but I can see that +1 is the international code for the whole of North America which makes it slightly more tricky.

      You would need the CASE section to match each pattern for USA (May be possible with a SQL regex?)

      E.g. in the UK +44 is our country code

      “01” numbers = National, in E164 we omit the first 0 so if the first 4 digits = +441 then this call is National:
      WHEN LEFT(ToUri, 4) = ‘+441’ THEN ‘National’

      Same for premium, “09” numbers = Premium, in E164 omit the first 0. If the first 4 digits = +449 then this call is a Premium number:
      WHEN LEFT(ToUri, 4) = ‘+449’ THEN ‘Premium’

      Because the UK is the only country that uses the +44 code, I can safely assume that after all of pattern matching, if the call doesn’t start with +44 then it must be an international call:
      WHEN LEFT(ToUri, 3) ‘+44’ THEN ‘International’

      This is where it gets tricky with the USA as +1 is used in other countries. Hope that helps.

      Post a Reply
  4. Hi Chris, I was wondering If I could have permission to use one of your images in a small training video I am doing. You can reach me at cward23@gmail.com Cheers!

    Post a Reply
  5. Hi,
    is it possible from your query to filter the current month data only. Sesssiondetailsview will have how many months data.

    Post a Reply

Submit a Comment

Your email address will not be published. Required fields are marked *