Lync Call Accounting and Departmental Billing now has its own page – http://www.lync.geek.nz/p/call-accounting.html
Following up on my previous post questioning the need for a free Lync call accounting solution, I would like to thank those who have provided feedback, and the motivation for me to get the first beta release ready for the Lync community.
This is very much a beta so please use with caution. Depending on the size of your AD environment and call volumes, the script can take quite some time to complete. In my demo environment I am processing about 15 call records per second, so a date range that involves 15,000 call records will take about 15 minutes to complete. I will look at ways to make it more efficient in future releases, but suggest for now you keep the date query range to within a month and process overnight. You could also modify the script to process daily and append to a CSV file.
At this stage I don’t have a detailed guide on how to use the PowerShell script, however hopefully the comments in the code will be enough for now. Please feel free to get in touch if you need any assistance or have any ideas to improve the script.
How it work’s
Firstly the script query’s SQL for call records within the specified date range. It then queries active directory for the users sip address that is identified as the party to be charged, and if a match is found includes this in the report. The matched users company and department details will also be included so that charged calls can be identified per department.
Some of you may notice that the tariff and rate information shown in the example output has been removed from this release. This is because the calculations are fairly complicated and need more testing to be reliable.
Instructions
1. Download the script from here
2. Unzip the file and extract to the location of your choice
3. Open the LyncCallAccounting_beta_v0.2.ps1 file and update the settings to match your environment
4. Run the script as admin
Notes
1. The credentials used to run the script will be passed to SQL for authentiacation. Make sure that you account has the required permissions.
I encourage your feedback to help grow this script in to something more mature.
I assume I'm probably doing something wrong, but when I run your script I get an error looking for a table that doesn't exist.
Exception calling "ExecuteReader" with "0" argument(s): "Invalid object name 'd
bo.SessionDetailsView'."
At C:Program FilesMicrosoft Lync Server 2010LyncCallAccounting_beta_v0.2.ps1
:51 char:37
+ $Result = $Command.ExecuteReader <<<< ()
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException
If I look at my tables on the SQL server, the table name in question is named SessionDetails. If I change the name in the .sql file, then UsersView gives me the same problem. If I fix that then I end up with a bigger error. So I assume I will keep breaking something if I keep moving down the line.
Hey, thanks for giving the script a go and more importantly for the feedback!
I will be releasing a new version on the next few days, so will see if I can figure out what the issue is. This release should interact with SQL a little more cleanly.
Could you let me know the version of Lync you are using and the version of SQL for Monitoring. Something you could try is running the SQL query directly from SQL Management Studio. Open the instance that is used by the Monitoring server select the LcSCDR database and then select new query. Paste the text from the .sql file and then replace PSvar_StartDate and 'PSvar_EndDate for the date range in the script, and replace PSvar_PSTNGateways for the gateways:
e.g.
WHERE
CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, m.StartTime), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) between '2014-01-01 00:00:00.00' AND '2014-02-01 00:00:00.00'
AND (sd.ResponseCode = 200)
–Create table variable of PSTN gateways
DECLARE @PSTNGateways table(
GatewayIP varchar(50) COLLATE Latin1_General_CI_AI, Vendor varchar(100) COLLATE Latin1_General_CI_AI )
insert into @PSTNGateways (Vendor, GatewayIP) values ('Kordia', '124.157.126.39')
The query should then run directly in SQL. Let me know what the error message is.
dbo.SessionDetailsView should exist under LcsCDR – Views. I am also looking at removing the dependency on the view in a later release.
We're using Lync 2010 and SQL Server 2008 R2 Service Pack 1.
I tried running the query directly in Management Studio, but once again due to my lack of SQL skills, it's not working. It chokes on the part where I define the Vendor and GatewayIP values. It doesn't like the apostrophes on either value. If I take the apostrophes away then it doesn't like the IP address. And since my SQL skills are limited to very basic statements, this one is above my head on what it's looking for. It doesn't like the syntax.
And I'm going to assume that if certain things are in red in the query window in Management Studio that it doesn't like the syntax. If that is the case, there is a LOT of red in the query window with the SQL statement.
Send my an email from the contact page and I'll send you some screenshots. Red is not necessarily bad. Anything inside ' ' will be red. The gateways should be on separate lines without apostrophes. The dates shouts be enclosed in ' '.
Thanks for everyone's feedback, the free Lync Call Accounting solution now has its own home!
Version 1.0 just released with major improvements over the last release.
– 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
http://www.lync.geek.nz/p/call-accounting.html
hi…
i question, it work in lync 2013
thanks
Sure does!