Call Accounting

While there are a number of call accounting solutions out there for Lync, they are all fairly expense and do far more than the average company would ever require. To solve this problem I have written a basic call accounting solution that can calculate call costs and assign them to a person, company or department.

One of the difficulties in accounting for Lync calls is when the call is forwarded. Lync passes the A-Party caller ID through to the C-Party, and lists the B-Party who forwarded the call as the "referrer". This is included in the SIP header Referred-By field, and also included in the Lync monitoring server reports. All of the SIP providers I have dealt with here in New Zealand don't seem to cater for this situation. Instead I get call records that show A-Party called C-Party, with no indication of who actually initiated the call to the C-Party, and therefore should be charged to that leg of the call.

There is now a script and application version of the solution:

Lync Call Reports (App)

Lync Call Reports processes data from your Lync Monitoring Server database (LcsCDR database) and presents it in an easy to read and searchable format. Additionally you can import vendor rate cards to calculate call costs, which get associated a users Department and Company specified in Active Directory.

• Search and filter results
• CSV export
• Ability to bill based on users Active Directory department or company field
• Import vendor rate cards to perform call rate and cost calculations
• Determines if the destination and callers number is in the same calling region to apply a local call rate
• Import vendor gateways - Determines call direction - inbound, outbound, forwarded, Lync-to-Lync
• Handles billing of forwarded calls using the Referred-By information

Instructions

1. Download the application here
2. Ensure that you have the required pre-requisites noted below
3. Add PSTN gateways from your topology to Gateways.csv in the program directory
4. Add call rates to RateCard.csv in the program directory (see more about this below)
5. Run LyncCallReports.exe (as admin if required)
6. Enter environment and report settings
7. Select "Run" to run the report
8. After the report has run you can view and filter results on the "Results" tab, or from the produced CSV file

Prerequisites
The script should be run with the follow pre-requisites:

• Lync 2010 or Lync 2013 environment
• PowerShell 2.0+
• User account which is a member of CSAdministrator
• SQL permissions to read, create and drop databases in temdb (sysadmin to drop and create, db_datareader and db_datawriter to run query. The next version will require less permissions)
• SQL permissions to read LcsCDR database (db_datareader)
• SQL Server Management Studio installed or Shared Management Objects.msi from the SQL Server feature pack
• Make sure that SQL allows remote connections - theres a good article about this here
  
  
  

Lync Call Accounting (Script)

This script queries the Lync CDR database (LcsCDR), performing calculations on the data to provide you useful call billing information.

• Ability to bill based on users Active Directory department or company field
• Import vendor rate cards to perform call rate and cost calculations
• Import vendor gateways- Determines call direction - inbound, outbound, forwarded, Lync-to-Lync
• Handles billing of forwarded calls using the Referred-By information

How does it work?

1. Firstly using another script of mine Get-LyncEnabledObjects,  the script collects all Lync enabled objects. Those objects are then searched for in Active Directory, and the Company and Department fields added to the results. This data is then stored as a table called LyncCallAccountingUsers on your SQL Monitoring server in the tempdb.
2. The vendor rate card is imported from CSV to a table called LyncCallAccountingRateCard in the tempdb.
   
   The CSV should be named RateCard.csv and include all calling regions globally. The SQL query matches the caller and called numbers to the best matching destination number pattern to determine the calling region. See more about this below.
   
   
3. The vendor gateways are imported from CSV to a table called LyncCallAccountingGateways in the tempdb.
   
   The CSV should be named Gateways.csv and include Vendor, GatewayIP and Location. The SQL query matches the gateway used in the call, to the GatewayIP addresses in the CSV. This will be further utilised in a later release to support multiple vendor rate cards.
   
   
4. At this point we have all the important information stored in SQL temporary tables, and we will use this in the next step to perform the calculations required to get the information we want.
5. The LyncCallAccounting.sql query is now executed. This can take quite some time to complete depending on the date range specified and the amount of call data in the monitoring server. In my demo environment I am processing about 80 call records per second, so a date range that involves 10,000 call records will take about 2 minutes to complete. If you have huge amounts of monthly data to process, I suggest you modify the script to process daily and append to a CSV file.
6. Once the query completes it will save the output to a CSV file.
   
   
   
   An example output can be found here

Instructions

1. Download the script from here
2. Unzip the file and extract to the location of your choice
3. Open LyncCallAccounting.ps1, read the release notes, and update the parameters to match your environment
4. Check prerequisites below
5. Run the script as a user with CSAdministrator and SQL db_owner or sysadmin rights in accordance with the following usage examples.

Usage
Parameters

• -StartDate <Start Date> : Report start date and time e.g. 2014-01-01 00:00:00.00
• -EndDate <End Date> : Report start date e.g. 2014-02-01 00:00:00.00
• -CSVExportPath <File Path to save CSV> : Path to save the output file e.g. C:\CDRs.csv
• -RateLocalCallingArea <Rate for local calls e.g. 0.05> (optional) : Rate for call when destination and callers number are in the same calling region.
• -DbGateways <DROP|UseExisting> (optional) - If the LyncCallAccountingGateways table already exists this option allows you to drop and recreate or to use the existing table. In either case if the table doesn't exist it will be created. This is an optional parameter, if you don't specify anything the default is to drop any existing tables if they exist.
• -DbUsers <DROP|UseExisting> (optional) - If the LyncCallAccountingUsers table already exists this option allows you to drop and recreate or to use the existing table. In either case if the table doesn't exist it will be created. This is an optional parameter, if you don't specify anything the default is to drop any existing tables if they exist.
• -DbRateCard <DROP|UseExisting> (optional) - If the LyncCallAccountingRateCard table already exists this option allows you to drop and recreate or to use the existing table. In either case if the table doesn't exist it will be created. This is an optional parameter, if you don't specify anything  the default is to drop any existing tables if they exist.
• -RateCardCSV <File Path to RateCard CSV file> (optional) : Path to rate card CSV file. Defaults to script path.
• -GatewaysCSV <File Path to Gateways CSV file> (optional) : Path to gateways CSV file. Defaults to script path.
• -ADPath <AD search scope for Lync objects e.g. DC=domain,DC=co,DC=nz> : AD distinguished name of search scope to include for Lync objects
• -SQLDataSource_LyncMonitoring <Monitoring SQL server e.g. MonitoringDbServer\Instance> : Monitoring server SQL backend database.
• -LyncPool <Lync Pool FQDN> (optional) :  Lync pool, only required for PowerShell remoting.
• -SQLScriptPath <Path to LyncCallAccounting.sql> (optional): Used to specifiy alternative path to SQL query file
• -DebugMode <$true|$false> (optional): Enables a verbose output

Example

Prerequisites
The script should be run from a server with the follow pre-requisites:

• Lync 2010 or Lync 2013 environment
• PowerShell 2.0+
• User account which is a member of CSAdministrator
• SQL permissions to read, create and drop databases in temdb (sysadmin to drop and create, db_datareader and db_datawriter to run query)
• SQL permissions to read LcsCDR database (db_datareader)
• SQL Server Management Studio installed or Shared Management Objects.msi from the SQL Server feature pack
• Make sure that SQL allows remote connections - theres a good article about this here
• If not run from a server with Lync management tools installed, you must specify the Lync Front End pool

Notes

• Tested on Lync 2013 with SQL 2008 R2 and SQL 2012 monitoring databases
• Tested with a user account with Domain Admin and SQL sysadmin rights

The Rate Card

Call rates are calculated from the RateCard.csv file by using the destination number pattern, and its associated rate. The rate card included with the program has over 40,000 rows to capture all call destinations globally, however it doesn't need to be this complicated if all you require is basic costings. To help make sense of how it all works, here's a scenario used by a customer based here in New Zealand (NZ):

NZ International Dialling Code 64
NZ National Landline 649, 647, 646, 644, 643
NZ Mobile 642
NZ Freephone 64800, 64508
NZ Premium 64900

We can capture call costs very easily by adding the following to RateCard.csv:

Destination	Rate	Country	CallingArea
649	0.05	NZ	Upper North Island
647	0.05	NZ	Waikato and Bay of Plenty
646	0.05	NZ	Middle North Island
644	0.05	NZ	Lower North Island
643	0.05	NZ	South Island
642	0.05	NZ	Mobile
64800	0.13	NZ	NZ Freecall
64508	0.00	NZ	NZ Freecall
64900	0.00	NZ	NZ Premium
64	0.1	NZ	Any other NZ destination
1	0.30	World	World
2	0.30	World	World
3	0.30	World	World
4	0.30	World	World
6	0.30	World	World
7	0.30	World	World
8	0.30	World	World
9	0.30	World	World

At this point its worth noting that the best matching destination will be used in determining the call rate. In the above example a call to 64 9 123-4567 will match to the 649 destination, because it is a better match that the 64 destination. This scenario allows the customer to accurately bill common destinations in NZ, and apply a fixed rate to all international destinations. This is often good enough as long as the rates charged to each cost centre or department, cover the actual monthly call costs.

Additional to the rates applied from the rate card, there is an additional process that compares the destination number to the callers number, and if found to be in the same calling region, the local calling area rate is applied to the call. This is useful if local calls are free or at a lesser rate than other national calls.