Lync Call Concurrency and SIP Channel Usage

UPDATED 25/08/2014 – Thanks to SinisterPenguin for their SQL Query – see below.

The topic of ‘how many SIP channels is Lync using’ has come up in blogs and forums quite a few times, but I still get asked and I don’t really have an answer or solution that I’m truly happy giving, either Performance Monitor for “live view”, or to buy 3rd party product. So I looked into it a bit more and wrote this article.

Lync doesn’t have a notion of ‘channels’, it will always send a call to a PSTN Gateway (ok, lets not get bogged down with Call Admission Control). But considering that most, if not all, ITSPs charge for SIP Trunks on a Per-DDI / Per-Channel basis, it’s important to know that you’re using what you pay for, and you’ve got enough headroom for occasional random spikes (time of day / month / year) or specific marketing efforts.

The Providers

I’ve heard “We don’t provide that information” from a couple of providers recently and it’s a bit shocking in my opinion. They’re charging for (lets say) 80 channels, but can’t tell you how many you’re using, or if you’ve hit the limit. I imagine this would be a great up-sell for them, imagine getting a letter (ok, lets be sensible – an email) saying “We’ve noticed you’ve reached 90% channel utilisation over the past month on more than one occasion, buy more channels :)” – obviously they don’t have to say the opposite “We’ve noticed you’re not using your channels, why don’t you cancel half your contract” – that’s understandable.

Lync and Performance Counters

All hopes are not totally lost, there are a couple of ways to see exactly what Lync is doing at any given time, either by Performance Monitor, or some PowerShell.

You may not want to run the Key Health Indicator Data Collector Set continuously, but just measuring the Inbound and Outbound Calls of the Mediation Server will give you the information you need without massive files, but it would still need a bit of processing afterwards.

However, please remember that checking the number of calls now, could be very different to checking them… er.. now. As people will have hung up, or more dialled in. So be aware these figures are not ‘averages’ or ‘peek’.. but ‘right now’ readings.

Lync Server 2010 (Thanks Kaushal Mehta)

Lync Server 2013

Which shows something like this…

But, don’t forget to check ‘Outbound Calls’ too!

And you can change (_total) to be any one of your PSTN Gateways if you happen to have an SBC onsite for ISDN, and another connection to an ITSP over MPLS or whatever your setup may be.

Lync Cmdlets

You can also see that same information in one place by running the following PowerShell cmdlet

And that outputs…

Historical Data

For me, this was the hard part… But I’ve come up with the following SQL Query (I’m no DBA by any means).

Because of my problem with ‘now…. and now’ above, I first tried looking for ‘overlapping’ date ranges, thinking that if two calls overlapped that was 2 channels being used.. Well it works with 2, but doesn’t scale.

Let’s take the following example…

  • Call A starts at 10:00am, and lasts for an hour.
  • Call B starts at 10:05am, and lasts for 10 minutes.
  • Call C starts at 10:20am, and lasts for 10 minutes.

Checking the overlapping calls with Call A would show all 3 calls overlapped at some point. But only 2 of those were ever active at the same time.

I couldn’t get away from saying “How many calls were ‘active’ at this time”.

So this rather crude method checks between two points in time… at intervals of 30 seconds. Higher than that, and I found it ‘missed’ calls in the gaps… This will still miss calls, but there’s not many very often that last less than 30 seconds.

Includes sessions which contained audio, and if either URI began with a “+”, i.e. a E.164 number.

And it took 35 seconds to examine yesterday morning (9am till midday – containing just under 800 calls), it outputs something like this…

Tried and tested on Lync Server 2013.

There’s probably plenty of ways to speed this up, depending on the time range you’re searching through, and the interval, the server could be running quite a few queries maxing out the CPU on the server.

If I get any other flashes of inspiration, I’ll revisit this article and update the query. Also please feel free to add comments below.

Thanks

 

UPDATE – SinisterPenguin’s Method

First I must give a huge thanks to SinisterPenguin for the SQL Query in their comment on this article.

Their method is very elegant in it’s simplicity. Before I show you the query, I’ll do my best to explain how it works…

Basically the SQL query creates a running tally of all the calls, each time a call starts +1, when a call ends -1. Therefore you can view the number of concurrent calls each time a call starts. And because it only uses the times from the calls, and not interval sampling as in my method above, it’s more efficient; the query ran through roughly 98k calls records in roughly 12 seconds in my sample LogCDR database.

And without any further delay, here it is… (as provided).

And there you have it…

SIP-Call-ConcurrencyIncluding a nice table of the ‘most concurrent’ times, in descending order, so you can see when you hit your maximum too.

Tweet about this on TwitterShare on LinkedInShare on Facebook
Pin on PinterestShare on Google+Digg thisShare on RedditShare on StumbleUponEmail this to someone

About Graham Cropley

Working as a Senior Consultant for Skype for Business, Exchange, and Office 365.

5 Comments

  1. Hi – my local Lync expert asked me to look at this problem (I’m a SQL nerd). This solution allows you to get the concurrent calls across the whole table based on your code above – I’ve tried to put in sensible comments. Hope it makes sense & is helpful 🙂

    ** SQL Query added to the article above.

  2. Great Work, could be nice to have this as a Sql report, in the normal Lync reporting site 🙂 do you know if anyboddy have created this for Responce Groups, so you could get information on your responce Groups ???

  3. Cool stuff. I just ran the SinsterPenguin query against my Lync 2013 monitoring server’s CDR db. Concurrent calls in progress, is this just PSTN calls, or does it include conferences as well? Assuming it is all calls but I wanted to be sure.

    Thanks for sharing this information! I agree with your original comment, question that comes up all the time and I too haven’t always had a good answer for.

    • Thanks for your comment, it’s probably one of my favourite SQL Queries – I use it all the time now 🙂

      The table used is for peer to peer activity, the above example filters based on the UserUri starting with a ‘+’, if you remove that it will be ALL audio calls (but still only Peer to Peer – no conferencing audio).

      I’ve got a collection of other queries that I’m sure I’ll share in an upcoming blog post at some point that may help with that. Stay tuned.

Leave a Reply

Your email address will not be published. Required fields are marked *