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)

SELECT displayName AS Displayname, department AS Department, [msRTCSIP-PrimaryUserAddress] COLLATE Latin1_General_CI_AI AS SipAddress
FROM OPENQUERY(ADSI,
'SELECT displayName, msRTCSIP-PrimaryUserAddress, department FROM ''LDAP://DC=domain,DC=local''WHERE objectClass = ''user'' AND msRTCSIP-OptionFlags = 385 ')
AS derivedtbl_1

Create a view named “OutgoingCallsUK”

SELECT TOP (100) PERCENT SessionDetails.FromUri, SessionDetails.ToUri, SessionDetails.InviteTime, SessionDetails.ResponseTime, SessionDetails.EndTime,
CASE WHEN LEFT(ToUri, 4) = '+441' THEN 'National' WHEN LEFT(ToUri, 4) = '+442' THEN 'National' WHEN LEFT(ToUri, 4) = '+443' THEN 'National' WHEN LEFT(ToUri,
2) = '01' THEN 'National' WHEN LEFT(ToUri, 2) = '02' THEN 'National' WHEN LEFT(ToUri, 2) = '03' THEN 'National' WHEN LEFT(ToUri, 4)
= '+447' THEN 'Mobile' WHEN LEFT(ToUri, 2) = '07' THEN 'Mobile' WHEN LEFT(ToUri, 4) = '+448' THEN 'Special' WHEN LEFT(ToUri, 2)
= '08' THEN 'Special' WHEN LEFT(ToUri, 4) = '+449' THEN 'Premium' WHEN LEFT(ToUri, 2) = '09' THEN 'Premium' WHEN LEFT(ToUri, 3)
<> '+44' THEN 'International' END AS Tariff, LcsCDR.dbo.Gateways.Gateway, dbo.LyncEVUsers.Displayname, dbo.LyncEVUsers.Department, DATEDIFF(ss,
SessionDetails.ResponseTime, SessionDetails.EndTime) AS Duration
FROM LcsCDR.dbo.SessionDetailsView AS SessionDetails INNER JOIN
LcsCDR.dbo.VoipDetails ON SessionDetails.SessionIdTime = LcsCDR.dbo.VoipDetails.SessionIdTime INNER JOIN
LcsCDR.dbo.Gateways ON LcsCDR.dbo.VoipDetails.ToGatewayId = LcsCDR.dbo.Gateways.GatewayId RIGHT OUTER JOIN
dbo.LyncEVUsers ON 'sip:' + SessionDetails.FromUri = dbo.LyncEVUsers.SipAddress
WHERE (SessionDetails.MediaTypes = 16) AND (SessionDetails.FromUriType = N'UserUri') AND (SessionDetails.ToUriType = N'PhoneUri') AND
(SessionDetails.ResponseCode = 200)
ORDER BY SessionDetails.InviteTime

 

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:

=IF(F2="Mobile",(J2/60)*Tariffs!$B$1,IF(F2="International",(J2/60)*Tariffs!$B$2,IF(F2="National",(J2/60)*Tariffs!$B$3,IF(F2="Special",(J2/60)*Tariffs!$B$4,IF(F2="Premium",(J2/60)*Tariffs!$B$5)))))

Format TotalCost as currency

Formula for Month:

=TEXT(C2,"mmmYY")

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:

11 Replies to “Lync 2013 CDR Report – Recharge Call Costs”

  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

    • 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

      • 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

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

    Thanks

    • 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.

Leave a Reply

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

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.