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:

Chris Hayward

Technical Architect at Symity