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 a script and application version of the solution.

If you find this useful and wish to support future development, please consider donating.

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. MonitoringDbServerInstance> : 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.

 

 

78 COMMENTS

  1. Hey Jeff,

    Most of my testing has been on Lync 2013, however I have worked with someone recently to improve compatibility on Lync 2010. The main deference is that Lync 2010 doesn't come with as many built in SQL views, so I add to work around this in the SQL query. If you have any issues let me know so I can help out. I also have a bunch of improvements I will be releasing in the coming weeks.

  2. Hi, just trying to test your script but getting error "ERROR: Invoke-SQLCmd2 – Could not connect to database". I have given myself sysadmin to tempdb, am part of the CSADMIN groups and read access to LcsCDR. I'm assuming it uses the logged in user credentials to connect to the DB?
    Any sugestions. Thanks

  3. Hey Stewart, Yes it will run as the logged on user. Something I missed in the pre-req's and a possible cause of the issue is the requirement for the SQL Server Management Studio or Shared Management Objects.msi from the SQL Server feature pack. If you are using the GUI version I have just updated to include a debug mode. Download the new exe's and run in debug mode, then use the contact page to send me the results. I will try my best to figure this out for you.

  4. Hi Andrew

    I've been trying to get the Lync Call Accounting Script to work but the script keeps failing with the below error:

    Add-SqlTable : Cannot create Table that does not contain at least one Column.
    At C:rawsoftToolsLyncCallAccountingLyncCallAccounting.ps1:161 char:21
    + Add-SQLTable <<<< -ServerInstance $SQLDataSource_LyncMonitoring -Database $SQLDatabase_t
    empdb -TableName "LyncCallAccountingUsers" -DataTable $LyncEnabledObjects -ColumnCollation "Latin1_
    General_CI_AI"
    + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Add-SqlTable

    Write-DataTable : System.Management.Automation.MethodInvocationException: Exception calling "WriteT
    oServer" with "1" argument(s): "Cannot access destination table 'LyncCallAccountingUsers'." —> Sy
    stem.InvalidOperationException: Cannot access destination table 'LyncCallAccountingUsers'. —> Sys
    tem.Data.SqlClient.SqlException: Invalid object name 'LyncCallAccountingUsers'.

    The account I am using is a member of CsAdministrators and is a SysAdmin on the Arc Mon SQL instance in the environment.

    Regards

  5. Hey Dewalt, I haven't seen that error before but I will do my best to assist you resolve it. Have you tried the GUI version? It has better error checking and a debug mode. Could you try that and post back the errors you get?

    • Thanks Andrew, for some reason the GUI version does not run from any of the Server 2008 R2 servers I've tried to launch it from. It keeps failing when I try and open it just showing an unexpected error occurred.

      I am able to run it from my Windows 7 and 8.1 desktop, I will be at the client site today and test the GUI version from my desktop and let you know if I get any errors.

    • Hi

      I am getting a different response now, seems like the LyncCallAccountingUsers table in the TempDb database cannot be created. The LyncCallAccountingGateways table does get created so I don't think this is caused lack of permissions.

      Add-SqlTable : Cannot create Table that does not contain at least one Column.
      At C:rawsoftToolsLyncCallAccountingLyncCallAccounting.ps1:161 char:21
      + Add-SQLTable <<<< -ServerInstance $SQLDataSource_LyncMonitoring -Database $SQLDatabase_tempdb -TableName "Ly
      ncCallAccountingUsers" -DataTable $LyncEnabledObjects -ColumnCollation "Latin1_General_CI_AI"
      + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException
      + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,Add-SqlTable

      Write-DataTable : System.Management.Automation.MethodInvocationException: Exception calling "WriteToServer" with "1" ar
      gument(s): "Cannot access destination table 'LyncCallAccountingUsers'." —> System.InvalidOperationException: Cannot a
      ccess destination table 'LyncCallAccountingUsers'. —> System.Data.SqlClient.SqlException: Invalid object name 'LyncCa
      llAccountingUsers'.

  6. Have you got SQL Management Studio or "Shared Management Objects.msi" from the SQL Server feature pack installed where you are running the app? Also please confirm Lync 2010/2013?

    The users table gets created after querying Lync for all objects which are enabled for Lync. It looks as though this process has failed resulting in an attempt to create a database with no columns. Are there any other errors before this? Were you able to run the GUI version? It is much more verbose in its error output, something I haven't quite got to adding to the script version. I am currently working on project that requires the script version so will be updating in the next few weeks.

    • Thanks Andrew, I have run the script from a server with SSMS installed and still get errors for the users table not being created. I don't get any error prior to this.

      Unfortunately I was not able to use the GUI version at the client site, but I tested the script in my lab and it worked fine. I suspect that the account I am using does not have all the required SQL permissions.

    • Thanks for this, below is results, I have asked the clients SQL team to se if they can assist me as well.

      STEP: Check databases
      –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
      –> DEBUG: Exception calling "Fill" with "1" argument(s): "Invalid object name 'tempdb.dbo.LyncCallAccountingUsers'."
      –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
      –> DEBUG: Exception calling "Fill" with "1" argument(s): "Invalid object name 'tempdb.dbo.LyncCallAccountingRateCard'.

      –> Creating SQL table LyncCallAccountingUsers…
      –> ERROR: There was an error while running the Add-SQLtable command
      –> DEBUG: Exception calling "Create" with "0" argument(s): "Create failed for Table 'dbo.LyncCallAccountingUsers'. "
      –> Writing DataTable to SQL table LyncCallAccountingUsers…
      –> ERROR: There was an error writing to the database while running Write-DataTable
      –> DEBUG: Exception calling "WriteToServer" with "1" argument(s): "Cannot access destination table 'LyncCallAccounting
      Users'."
      "

    • No worries. Are they the only errors you are seeing? I cant help but think the "Exception calling "Create" with "0" argument(s)" is happening because the Get-LyncEnabledObjects is failing. Do the other 2 databases get created successfully?

    • Yes the other tables gets created, I had the SQL guys run a trace when I run the script and they say they can't see that the users table even attempts to create. I am busy trying to break down the script so I can run the get-LyncEnabledObjects portion on its own and see if there are any failures here.

  7. Thank you for this great application, i am running the script but the report is not showing any data , i have added a gateway and still i am not getting anything in the report , please find below the output of running the script

    Thanks in Advance

    PS C:UsersAdministrator.ContosoDesktopLyncCallAccountingLyncCallAccounting_v2.0and2.1> C:UsersAdministrator.ContosoDesktopLyncCallAccountingLyncCallAccounting_v2.0and2.1LyncCallAccounting.ps1
    Lync Commandlets already loaded…

    AD Commandlets already loaded…

    SQL table LyncCallAccountingGateways already exisits, dropping and recreating…

    Importing vendor gateways card to SQL table…

    SQL table LyncCallAccountingUsers already exisits, dropping and recreating…

    Getting Lync enabled objects and writing to SQL table…

    WARNING: Region "Redmond" does not exist.
    WARNING: Access number "sip:caa@contoso.com" contains some invalid properties. Use Set- cmdlet to fix the invalid properties.
    SQL table LyncCallAccountingRateCard already exisits, dropping and recreating…

    Importing vendor rate card to SQL table…

    Running CDR SQL query, this may take some time to complete depending on record volume…

    Processing of SQL query results completed, creating output file (C:3-12-2014-07.03.18-LyncCDRs.csv)…

    Elapsed Time: 39.2889199 seconds

    Please remember to provide feedback at http://www.lync.geek.nz/p/call-accounting.html

  8. Hello Andrew i have fixed the issue , it was in my Monitoring reports 🙂 i really like your application its awesome thank you very much for this great app 🙂 can you please advise how can i get the GUI version ?

    Thanks

  9. I'm sure I'm doing something stupid, I just cant figure it out…..
    I have tried different Monitoring Database settings… FQDNreportserver, FQDNLcsCDR, FQDNDefault, FQDNCDRDB

    —– Started at 2015-01-16 21:11:50 —–
    STEP: Load required PowerShell Modules
    –> Lync Commandlets already loaded…
    –> AD Commandlets already loaded…
    STEP: Start Lync Call Reports processing
    STEP: Check databases
    –> ERROR: Could not connect to database while running Invoke-SQLCmd2
    –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
    –> ERROR: Could not connect to database while running Invoke-SQLCmd2
    –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
    –> ERROR: Could not connect to database while running Invoke-SQLCmd2
    –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
    –> SQL table LyncCallAccountingGateways does not exist, creating…
    STEP: Create SQL Table LyncCallAccountingGateways…
    –> Importing vendor gateways csv and creating DataTable…
    –> Creating the SQL table LyncCallAccountingGateways…
    –> ERROR: There was an error creating the SQL table LyncCallAccoutingGateways while running the Add-SQLTable command
    –> Writing DataTable to the SQL table LyncCallAccountingGateways…
    –> ERROR: Could not connect to database while running the Write-DataTable command
    –> ERROR: There was an error writing to the database while running Write-DataTable
    –> SQL table LyncCallAccountingUsers does not exist, creating…
    STEP: Create SQL Table LyncCallAccountingUsers
    –> Finding all Lync enabled objects…
    –> ERROR: There was an error creating the LyncCallAccountingUsers DataTable
    –> Creating SQL table LyncCallAccountingUsers…
    –> ERROR: There was an error creating the SQL Table LyncCallAccountingUsers
    –> Writing DataTable to SQL table LyncCallAccountingUsers…
    –> ERROR: There was an error writing DataTable to the SQL table LyncCallAccountingUsers
    –> SQL table LyncCallAccountingRateCard does not exist, creating…
    STEP: Create SQL table LyncCallAccountingRateCard
    –> Importing vendor rate card csv to DataTable…
    –> Creating the SQL table LyncCallAccountingRateCard…
    –> ERROR: There was an error creating the SQL table LyncCallAccountingRateCard while running the Add-SQLTable command
    –> Writing DataTable to the SQL table LyncCallAccountingRateCard…
    –> ERROR: Could not connect to database while running the Write-DataTable command
    –> ERROR: There was an error writing to the database while running Write-DataTable
    STEP: Run CDR SQL query – this may take some time to complete depending on record volume…
    –> ERROR: Could not connect to database while running Invoke-SQLCmd2
    –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
    STEP: Create output – Processing of SQL query results completed!
    –> Switch to the results tab to search and filter your data
    –> Creating output file (C:LyncCDR121sd.csv)…
    STEP: All Done!!
    –> Elapsed Time: 161.6375574 seconds
    –> To help improve this free tool, please remember to provide feedback at http://www.lync.geek.nz/p/call-accounting.html

    • Hey, It looks like there is a SQL connection or permission issue. In the latest version of the script there is a switch for debug mode, which will give a more verbose output and hopefully narrow things down. The SQL server should be in format FQDNSQL_Db_Instance_Name, and requires remote connections are enabled in SQL. It is also a requirement that SQL Management Studio is installed on the machine where you run the script. When I have connection issues I always fire up Management Studio and login with the same account the script runs with. This will confirm connectivity and credentials are OK. Let me know how you go, and post the debug output back here if you are still stuck.

  10. Hi Andrew…Please find the output in debug mode as mentioned below:

    —– Started at 2015-03-09 15:31:16 —–
    STEP: Start Lync Call Reports processing
    STEP: Check databases
    –> WARNING: There was an issue determining if the SQL table LyncCallAccountingGateways exists, atempting to drop and recreate…
    STEP: Create SQL Table LyncCallAccountingGateways…
    –> Importing vendor gateways csv and creating DataTable…
    –> Creating the SQL table LyncCallAccountingGateways…
    –> Writing DataTable to the SQL table LyncCallAccountingGateways…
    –> WARNING: There was an issue determining if the SQL table LyncCallAccountingUsers exisits, atempting to drop and recreate…
    STEP: Create SQL Table LyncCallAccountingUsers
    –> Finding all Lync enabled objects…
    –> Creating SQL table LyncCallAccountingUsers…
    –> Writing DataTable to SQL table LyncCallAccountingUsers…
    –> WARNING: There was an issue determining if the SQL table LyncCallAccountingRateCard exisits, atempting to drop and recreate…
    STEP: Create SQL table LyncCallAccountingRateCard
    –> Importing vendor rate card csv to DataTable…
    –> Creating the SQL table LyncCallAccountingRateCard…
    –> Writing DataTable to the SQL table LyncCallAccountingRateCard…
    STEP: Run CDR SQL query – this may take some time to complete depending on record volume…
    –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
    –> DEBUG: Exception calling "Fill" with "1" argument(s): "Invalid object name 'VoipDetails'."
    STEP: Create output – Processing of SQL query results completed!
    –> Switch to the results tab to search and filter your data
    –> Creating output file (C:LyncCallAccountingLyncCDRs.csv)…
    STEP: All Done!!
    –> Elapsed Time: 472.0736372 seconds
    –> To help improve this free tool, please remember to provide feedback at http://www.lync.geek.nz/p/call-accounting.html

  11. Hi andrew…Sorry but can you pls tell me how to chek temp databases being created in tempDb? Here, in Databases-System Databases-tempDb-Tables-dbo.LyncCallAccountingGateways..AM i looking right way or wrong? Pls guide me,,,

  12. Hello Andrew…When I ran the script in Management studio, It is giving me following error:

    Msg 208, Level 16, State 1, Line 5
    Invalid object name 'VoipDetails'

  13. "VoipDetails" is a table under the LcsCDR database. Can you confirm it exists? In the SQL script you may need to fully qualify VoipDetails (e.g. LcsCDR.dbo.VoipDetails) in the FROM section, although I have not seen this as an issue before. If that works then may also need to apply the same logic to the other JOIN's in that section.

  14. No Andrew…I did not find "VoipDetails" table under the LcsCDR database. And can you pls give me detailed steps how to do fully qualify VoipDetails in the FROM section? I have not any idea regarding the same…

  15. Hi Andrew….I implemented successfully Lync Call Accounting in my IT environment. It is showing all the call reports. Now, I have one query. In LyncCDRs.csv file, I am getting all call details, but rate,Call charge, charge to company and charge to department information is shows as —. So why it is like that?

    • Great news! Rate and call charge are calculated using the gateways and ratecard csv files – have you updated these to suit your environment? company/department charge to is pulled from the AD users company/department field – is this information populated for you users?

    • Hi Andrew.
      I am getting below
      —–
      STEP: Check databases
      –> ERROR: Could not connect to database while running Invoke-SQLCmd2
      –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
      –> ERROR: Could not connect to database while running Invoke-SQLCmd2
      –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
      –> ERROR: Could not connect to database while running Invoke-SQLCmd2
      –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
      –> SQL table LyncCallAccountingGateways does not exist, creating…
      STEP: Create SQL Table LyncCallAccountingGateways…
      –> Importing vendor gateways csv and creating DataTable…
      –> Creating the SQL table LyncCallAccountingGateways…
      –> ERROR: Could not connect to database while running Add-SQLtable
      –> ERROR: There was an error while running the Add-SQLtable command
      –> Writing DataTable to the SQL table LyncCallAccountingGateways…
      –> ERROR: Could not connect to database while running the Write-DataTable command
      –> ERROR: There was an error writing to the database while running Write-DataTable
      –> SQL table LyncCallAccountingUsers does not exist, creating…

    • HI Andrew,
      I am getting below
      STEP: Check databases
      –> ERROR: Could not connect to database while running Invoke-SQLCmd2
      –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
      –> ERROR: Could not connect to database while running Invoke-SQLCmd2
      –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
      –> ERROR: Could not connect to database while running Invoke-SQLCmd2
      –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
      –> SQL table LyncCallAccountingGateways does not exist, creating…
      STEP: Create SQL Table LyncCallAccountingGateways…
      –> Importing vendor gateways csv and creating DataTable…
      –> Creating the SQL table LyncCallAccountingGateways…
      –> ERROR: Could not connect to database while running Add-SQLtable
      –> ERROR: There was an error while running the Add-SQLtable command
      –> Writing DataTable to the SQL table LyncCallAccountingGateways…
      –> ERROR: Could not connect to database while running the Write-DataTable command
      –> ERROR: There was an error writing to the database while running Write-DataTable
      –> SQL table LyncCallAccountingUsers does not exist, creating…

    • Looks like you have a database connectivity issue. I suggest installing SQL Management tools on the machine you are running the script from so that you can confirm you can access the Db's. This will confirm network and credentials are OK. Let me know the result.

  16. Hi Andrew…I am looking for rate and call charge. I configured properly, but i am not finding any details regarding rate and call charge in LcsCDRs.csv file. So what can be the issue?

    • Totally, you could even use a permanent database rather than temp. Out of the box there are no config options to do this, however if you are vaguely familiar with PowerShell you can tweak it yourself.

      $SQLDatabase_tempdb specifies the database name to save temp data, you can leave it as tempdb or change to whatever database you want. Then you need to create a new variable to the other SQL server then replace $SQLDataSource_LyncMonitoring with the new variable wherever there is a reference to $SQLDatabase_tempdb on the same line.

      e.g.
      Invoke-SQLCmd2 -ServerInstance $SQLDataSource_NewSQLServer -Database $SQLDatabase_tempdb -Query "DROP TABLE LyncCallAccountingRateCard"

      That should sort it.

  17. Hi Andrew.

    Thank you for this work. But I’ve an issue with the SQL query. The PS script times out after a while. The same if I try to run the query from $LcsCDRQuery in SQL Management Studio. I can read all data from the LcsCDR with my account.

    The app shows this error Messages in debug mode:

    STEP: Run CDR SQL query – this may take some time to complete depending on record volume…
    –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
    –> DEBUG: Exception calling “Fill” with “1” argument(s): “Incorrect syntax near ‘,’.
    Incorrect syntax near the keyword ‘AND’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near the keyword ‘THEN’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near ‘)’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near ‘)’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near ‘)’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near ‘)’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near ‘)’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near ‘)’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near the keyword ‘AND’.
    Incorrect syntax near the keyword ‘WHEN’.
    Incorrect syntax near the keyword ‘THEN’.
    Incorrect syntax near the keyword ‘AS’.
    Incorrect syntax near the keyword ‘AND’.
    Incorrect syntax near the keyword ‘WHEN’.
    Incorrect syntax near the keyword ‘THEN’.
    Incorrect syntax near the keyword ‘AS’.”

    Thank you

    • Hi Andy,

      Haven’t seen this one before so a few questions:
      1. Where are you running the script, and does this location have SQL Management Studio or Shared Management Objects installed?
      2. Does your account have permissions to write to TempDb?
      3. Are you using the script or GUI version?
      4. Are there any other errors before or after the errors given above? Maybe post the entire output here.

  18. Hi Andrew

    1. I’m running the script on a management Server with SQL Management Studio and Lync admin tool installed
    2. Yes, and the tables are created properly
    3. The error messages above are from the app

    App output:

    —– Started at 2015-08-18 06:49:26 —–
    STEP: Load required PowerShell Modules
    –> Lync Commandlets already loaded…
    –> AD Commandlets already loaded…
    STEP: Start Lync Call Reports processing
    STEP: Check databases
    –> SQL table LyncCallAccountingGateways already exisits, dropping and recreating…
    STEP: Create SQL Table LyncCallAccountingGateways…
    –> Importing vendor gateways csv and creating DataTable…
    –> Creating the SQL table LyncCallAccountingGateways…
    –> Writing DataTable to the SQL table LyncCallAccountingGateways…
    –> SQL table LyncCallAccountingUsers already exisits, dropping and recreating…
    STEP: Create SQL Table LyncCallAccountingUsers
    –> Finding all Lync enabled objects…
    –> Creating SQL table LyncCallAccountingUsers…
    –> Writing DataTable to SQL table LyncCallAccountingUsers…
    –> SQL table LyncCallAccountingRateCard already exisits, dropping and recreating…
    STEP: Create SQL table LyncCallAccountingRateCard
    –> Importing vendor rate card csv to DataTable…
    –> Creating the SQL table LyncCallAccountingRateCard…
    –> Writing DataTable to the SQL table LyncCallAccountingRateCard…
    STEP: Run CDR SQL query – this may take some time to complete depending on record volume…
    –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
    –> DEBUG: Exception calling “Fill” with “1” argument(s): “Incorrect syntax near ‘,’.
    Incorrect syntax near the keyword ‘AND’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near the keyword ‘THEN’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near ‘)’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near ‘)’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near ‘)’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near ‘)’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near ‘)’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near ‘)’.
    Incorrect syntax near the keyword ‘ORDER’.
    Incorrect syntax near the keyword ‘AND’.
    Incorrect syntax near the keyword ‘WHEN’.
    Incorrect syntax near the keyword ‘THEN’.
    Incorrect syntax near the keyword ‘AS’.
    Incorrect syntax near the keyword ‘AND’.
    Incorrect syntax near the keyword ‘WHEN’.
    Incorrect syntax near the keyword ‘THEN’.
    Incorrect syntax near the keyword ‘AS’.”
    STEP: Create output – Processing of SQL query results completed!
    –> Switch to the results tab to search and filter your data
    –> Creating output file (S:\Accounting\LyncCDRs.csv)…
    STEP: All Done!!
    –> Elapsed Time: 104.5779362 seconds

    There is no timeout waiting for the query.

    I tried to run the query of the app in SQL management studio. I’ve uncommented line 179 with valid start and end times and commented line 180 before. Ok, we have round about a half of a million rows in the SessionDetails and a quarter of a million in Voip Details table. But I’ve reduced the time window to 1 hour and the SQL server is powerful. The query pushes the CPU to 100% and returns no data but this error messages after nearly 10 minutes:
    Msg 8114, Level 16, State 5, Line 5
    Error converting data type varchar to numeric.

    I guess some issues with regional settings and conversation of time and numbering formats. Our servers are installed in English but the regional settings are German. Maybe the same thing changes the TSQL script during import to the app. I have to test this with US English regional settings but this can take a while. I need to setup a second management system first.

    The PS script runs nearly 13 minutes for the same time window and with proper results.

    • Looks like its not handling some of the returned data correctly – could be I’m not handling something correctly. I did have a potential similar issues during early testing.

      The SQL Query has a few replacement/variables that are passed in from PowerShell. Just to make sure run this one which removes these:
      https://onedrive.live.com/redir?resid=FF04F2F514D1EFC!72759&authkey=!AIqAYf6SRPxJvrg&ithint=file%2csql

      Try running the query for the smallest time period (set at bottom of query) you know you can get the error. Say you start at 1 hour and get the error, then keep splitting that period in half until you get down to a manageable number of records. At this point pay close attention to the start/end/duration/charge columns (+ pretty much any column that you would assume is calculated) and see if you can see any abnormalities. This is how I found issues in the past – from memory this was related to start time being greater than end time; I now handle this scenario but thinking it may be similar.

      Let me know if you see any new errors running direct from SQL Management Studio.

      Also please clarify – The script version runs with no issues? Its only the app version that is having the problem?

      Hope this helps!

      • Hi Andrew,

        ive run into the same problem as Andy. Same output of the App. If i try to run the query without any modifications, it will run successfully, but without any data. If i try to set the Start Time, ive got the following errors. The Start Time i set as follows:


        ORDER BY
        m.StartTime 2016-01-18 00:00:00.000

        Systems are in english, but regional settings are in german too.

        Would be nice if you have had an answer 🙂

        BR
        Rene

  19. Hi Andrew,
    Thanks for your effort on it.
    Does this script handle normalized phone numbers stored in LcsCDR call records for matching with Rate-card to apply a rate?
    For example, we have a rate for number starting with ‘+17′ or ’17’ and also another rate for ’76’; and if the normalization rules are there at Lync server then we may get the number ‘+17654321’ as ‘7654321’, so the number will get the wrong rate-card of ’76’.
    Can you please describe how we can get the correct rate for the original number(before normalization) ?

    • Hey,

      The script will match on the number stored in the LcsCdr database. Why are you normalising to non E164? If this is for outbound PSTN routing you should do this using trunk translations – this may solve your problem, however I have not tested whether the trunk translated number is stored in the database or the normalised number – I would assume the normalised number as trunk translations are processed last.

      • Thanks for your reply.
        We use normalization rules under dial plans in order the user can dial their required number without putting +1 or +31 on their Lync clients as a prefix. But Lync LcsCDR only register the number punched on Lync client without adding +1 or +31 as a prefix that normalization rules adds to it to make punched number in E164 format. I do not think trunk translation rule can override normalization rules usage, plus even if we have translation rule to add +1 or +31 to every number punched by Lync client without prefix will register in Lync LcsCDR without prefix and make our calculation wrong again.

  20. Hello

    Does this run with Skype For business?

    We had it working on Lync2013 but after we upgraded it’s now not working

    Getting the following in debug

    —– Started at 2015-12-24 09:01:26 —–
    STEP: Start Lync Call Reports processing
    STEP: Check databases
    –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
    –> DEBUG: Exception calling “Fill” with “1” argument(s): “Invalid object name ‘tempdb.dbo.LyncCallAccountingGateways’.”
    –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
    –> DEBUG: Exception calling “Fill” with “1” argument(s): “Invalid object name ‘tempdb.dbo.LyncCallAccountingUsers’.”
    –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
    –> DEBUG: Exception calling “Fill” with “1” argument(s): “Invalid object name ‘tempdb.dbo.LyncCallAccountingRateCard’.”
    –> SQL table LyncCallAccountingGateways does not exist, creating…
    STEP: Create SQL Table LyncCallAccountingGateways…
    –> Importing vendor gateways csv and creating DataTable…
    –> Creating the SQL table LyncCallAccountingGateways…
    –> ERROR: There was an error creating the SQL table LyncCallAccoutingGateways while running the Add-SQLTable command
    –> DEBUG: Could not load file or assembly ‘Microsoft.SqlServer.ConnectionInfo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.
    –> Writing DataTable to the SQL table LyncCallAccountingGateways…
    –> ERROR: There was an error writing to the database while running Write-DataTable
    –> DEBUG: Exception calling “WriteToServer” with “1” argument(s): “Cannot access destination table ‘LyncCallAccountingGateways’.”
    –> SQL table LyncCallAccountingUsers does not exist, creating…
    STEP: Create SQL Table LyncCallAccountingUsers
    –> Finding all Lync enabled objects…
    –> Creating SQL table LyncCallAccountingUsers…
    –> ERROR: There was an error creating the SQL Table LyncCallAccountingUsers
    –> DEBUG: Could not load file or assembly ‘Microsoft.SqlServer.ConnectionInfo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.
    –> Writing DataTable to SQL table LyncCallAccountingUsers…
    –> ERROR: There was an error writing to the database while running Write-DataTable
    –> DEBUG: Exception calling “WriteToServer” with “1” argument(s): “Cannot access destination table ‘LyncCallAccountingUsers’.”
    –> SQL table LyncCallAccountingRateCard does not exist, creating…
    STEP: Create SQL table LyncCallAccountingRateCard
    –> Importing vendor rate card csv to DataTable…
    –> Creating the SQL table LyncCallAccountingRateCard…
    –> ERROR: There was an error creating the SQL table LyncCallAccountingRateCard while running the Add-SQLTable command
    –> DEBUG: Could not load file or assembly ‘Microsoft.SqlServer.ConnectionInfo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.
    –> Writing DataTable to the SQL table LyncCallAccountingRateCard…
    –> ERROR: There was an error writing to the database while running Write-DataTable
    –> DEBUG: Exception calling “WriteToServer” with “1” argument(s): “Cannot access destination table ‘LyncCallAccountingRateCard’.”
    STEP: Run CDR SQL query – this may take some time to complete depending on record volume…
    –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
    –> DEBUG: Exception calling “Fill” with “1” argument(s): “Invalid object name ‘tempdb.dbo.LyncCallAccountingRateCard’.”
    STEP: Create output – Processing of SQL query results completed!
    –> Switch to the results tab to search and filter your data
    –> Creating output file (C:\LyncCDRs.csv)…
    STEP: All Done!!

  21. Hello, Can you tell me does this work on skype for business?

    Since upgrading from Lync to skype for business it no longer works

    • Hi Paul, I haven’t actually tested this yet so I don’t know what (if anything) has changed. Its on my to do list, i’ll try and expedite this so I can help you out. In the meantime the error “Microsoft.SqlServer.ConnectionInfo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.” sticks out to me. Are you running the script from a server that has the same version on SQL Management tools as Skype for Business? You could quickly validate this as the issue if you ran the script on the Front End server. Let me know if you get anywhere with this.

  22. Hi Andy

    I have managed to resolve the first error but i am now getting this if you can help at all.

    —- Started at 2016-01-13 20:25:40 —–
    STEP: Load required PowerShell Modules
    –> Lync Commandlets already loaded…
    –> AD Commandlets already loaded…
    STEP: Start Lync Call Reports processing
    STEP: Check databases
    –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
    –> DEBUG: Exception calling “Fill” with “1” argument(s): “Invalid object name ‘tempdb.dbo.LyncCallAccountingGateways’.”
    –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
    –> DEBUG: Exception calling “Fill” with “1” argument(s): “Invalid object name ‘tempdb.dbo.LyncCallAccountingUsers’.”
    –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
    –> DEBUG: Exception calling “Fill” with “1” argument(s): “Invalid object name ‘tempdb.dbo.LyncCallAccountingRateCard’.”
    –> SQL table LyncCallAccountingGateways does not exist, creating…
    STEP: Create SQL Table LyncCallAccountingGateways…
    –> Importing vendor gateways csv and creating DataTable…
    –> Creating the SQL table LyncCallAccountingGateways…
    –> ERROR: There was an error while running the Add-SQLtable command
    –> DEBUG: Exception calling “.ctor” with “2” argument(s): “SetParent failed for Table ‘LyncCallAccountingGateways’. ”
    –> Writing DataTable to the SQL table LyncCallAccountingGateways…
    –> ERROR: There was an error writing to the database while running Write-DataTable
    –> DEBUG: Exception calling “WriteToServer” with “1” argument(s): “Cannot access destination table ‘LyncCallAccountingGateways’.”
    –> SQL table LyncCallAccountingUsers does not exist, creating…
    STEP: Create SQL Table LyncCallAccountingUsers
    –> Finding all Lync enabled objects…
    –> Creating SQL table LyncCallAccountingUsers…
    –> ERROR: There was an error while running the Add-SQLtable command
    –> DEBUG: Exception calling “.ctor” with “2” argument(s): “SetParent failed for Table ‘LyncCallAccountingUsers’. ”
    –> Writing DataTable to SQL table LyncCallAccountingUsers…
    –> ERROR: There was an error writing to the database while running Write-DataTable
    –> DEBUG: Exception calling “WriteToServer” with “1” argument(s): “Cannot access destination table ‘LyncCallAccountingUsers’.”
    –> SQL table LyncCallAccountingRateCard does not exist, creating…
    STEP: Create SQL table LyncCallAccountingRateCard
    –> Importing vendor rate card csv to DataTable…
    –> Creating the SQL table LyncCallAccountingRateCard…
    –> ERROR: There was an error while running the Add-SQLtable command
    –> DEBUG: Exception calling “.ctor” with “2” argument(s): “SetParent failed for Table ‘LyncCallAccountingRateCard’. ”
    –> Writing DataTable to the SQL table LyncCallAccountingRateCard…
    –> ERROR: There was an error writing to the database while running Write-DataTable
    –> DEBUG: Exception calling “WriteToServer” with “1” argument(s): “Cannot access destination table ‘LyncCallAccountingRateCard’.”
    STEP: Run CDR SQL query – this may take some time to complete depending on record volume…
    –> ERROR: There was an error while running the Invoke-Sqlcmd2 command
    –> DEBUG: Exception calling “Fill” with “1” argument(s): “Invalid object name ‘tempdb.dbo.LyncCallAccountingRateCard’.”
    STEP: Create output – Processing of SQL query results completed!
    –> Switch to the results tab to search and filter your data
    –> Creating output file (C:\LyncCDRs.csv)…
    STEP: All Done!!
    –> Elapsed Time: 47.7403875 seconds
    –> To help improve this free tool, please remember to provide feedback at http://www.lync.geek.nz/p/call-accounting.html

  23. Hi Andrew,

    I’ve been playing with both the script and application version of call accounting as I have a customer who needs a monthly department-based report, and this sounds like it’s perfect for them.

    The only problem I have is that the report seems to take quite some time to complete – it’s running about 5000 seconds to pull a single day’s report, so I shudder to think of how long it would take to run a full month. During the time it’s running the SQL server pegs one of the CPU cores to 100%, and if I’m using the application version it shows as ‘not responding’ until it’s complete.

    Do you have any ideas what would be causing this or suggestions on how I can speed this up?

    Cheers,
    Leigh.

    • Hey Leigh, I have noticed it can be pretty slow when there are tonnes of records. Customers of mine usually schedule it to run overnight, but I haven’t tried it on any sites over 1000 users. I am no SQL expert and I believe the issue is with the SQL query which is pretty inefficient. Its something I have been meaning to get a SQL expert to look at, but haven’t got around to it as yet. Re the application version, the GUI freezes while running because it is created using PowerShell, and there isn’t an easy way to separate the GUI and data crunching threads.

      • Hi Andrew,

        Cheers for the reply. In this case there are only about 1500 records across ~150 users, but I think the culprit here might be the grunt behind the SQL database – it’s an Audiocodes Onebox, so resources are a little tight.

        I’ll keep tinkering and see if I can speed it up any.

        Thanks again!

        • No worries. If you think its going to do the job and the customer wanted to consider a donation to the cause, I could pay someone to have a look at making it more efficient. I am nearly 100% sure the SQL query is horribly inefficient!!

  24. Hi Andrew – the app runs very well! Would there be any way to add the functionality to calculate charges for Inbound PSTN calls? For example, the use of Toll-Free Dial-in access numbers? There is a cost in receiving calls on these types of numbers and it would be nice to do determine charges (and validate what Telco is charging)

LEAVE A REPLY