Lync Call Accounting and Departmental Billing now has its own page – http://www.lync.geek.nz/p/call-accounting.html
Wondering if there is a great demand out there for free solution to Lync call accounting? I have been playing around with SQL queries to see what I can do, and if there is a demand will spend some more time putting together something to share with the Lync community.
One of the difficulties in accounting for Lync calls is when a 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 as Referred-By, and also included in the Lync monitoring server reports.
All 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.
Its pretty crude, but here’s what I am getting straight out of SQL so far:
Example output can be downloaded here.
Leave a comment and let me know what you think.
Microsoft released a really helpful document describing the SIP Headers for SIP Trunking providers for billing:
http://www.microsoft.com/en-us/download/confirmation.aspx?id=38820
It would be nice though if you could guide through the SQL queries and the different tabled being used to export the above.
Hey Guy, cheers for your feedback! I have actually seen that one, however the monitoring database doesn't seem to record the same information. It appears that the referred by information in the monitoring database captures all calls that were forwarded, whereas in the SIP headers it could be one of 3. I will be posting the SQL query and PowerShell used to generate the report on my blog soon as I've given it a tidy up and commented all the code.
You're off to a great start Andrew – you seem to have captured everything that moves! I agree of course that you definitely need to be capturing (and 'billing') the party that initiates the transfer!
And just to be difficult, what if A calls B, B transfers to C (so far so good) – and then *C* transfers to D? Is everyone's involvement in the call being captured? (Yes, this degree of detail is often more about forensics than cost recovery).
Cheers Greig! I will need to do a bit more testing around that, but I think we might be ok there. Lync just creates another line in the report which I should be capturing. What I dont do is tie them all together so that might be worth looking at. Will investigate and post back ASAP.
I have just released the first version if you'd like to take a look – http://www.lync.geek.nz/2014/03/lync-call-accounting-for-user-or.html
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