Sizing appropriate hardware, bandwidth, channels, etc. is one of key activity task during the design phase of any UC project. One value which is extremely important and help you size correctly is the concurrency level of any modality – Voice, Video, desktop sharing, etc. If you are deploying a new solution you would need the concurrency levels to size up the new environment. If you are not deploying the new solution you still require the concurrency levels for reporting/billing/bandwidth forecasting purposes.
Surprisingly, I have found getting concurrency levels (e.g. concurrent voice calls) from existing environment is pretty hard. It is something pretty straightforward to work out but either the customer doesn’t have any logging tool on their ageing PBX or the PBX vendor just don’t want to give any details which may help with Lync deployment. Even after you have Lync deployed there isn’t anywhere in the Lync Monitoring reports to tell you what concurrency levels are for any of the modalities.
Over the years I have developed various tools/scripts which I use to gather information during the design phase. One of this is the Concurrency Script. This is an extremely simple but very powerful PowerShell script. This script allows you to generate reports of what your Call Concurrency levels are not just for Lync but from PBX data as well.
All that script requires is an input of a CSV file which has StartTime and EndTime columns which essentially shows the date and time when the call started and when the call ended. The script processes this information and returns you the peak load at every minute/hour/day/week.
As the script is looking for just StartTime and EndTime, the concurrency is not limited to calls, you can calculate the concurrency of anything….literally, even something not related to Lync, PBX or even Voice!
The logic of the script is simple. The diagram below illustrates this very well:
In the above diagram, the top row from 0 to 9 represent minutes e.g. 10:00 – 10:09. The coloured bars show the StartTime and EndTime of each call. The bottom row (Calls Up) represents the calls which were up and running at each minute.
If you notice at the 4th minute you have 6 calls running – showing the busiest period. All that script does is run through the similar logic as in the diagram above and present you the breakdown of concurrency at each interval.
Now let’s see how we can apply the same concept for Lync and non-Lync environment!
Concurrency in Pre-Lync /Non-Lync Environment
To find out the concurrency level from your existing environment all you need is something that gives you Start and End time of every call. If you can get that from your PBX supplier then that’s great, but if you cannot just get and itemised bill from you PSTN carrier. It doesn’t matter what other data (columns) it has as long as it has StartTime and EndTime and is a CSV file you are good to go.
Alternatively, you can download a template here to play around with the data.
Launch the Concurrency script and enter the path of the CSV. No other parameter values are mandatory but you can enter few (e.g. sampleinterval to show how often data should be sample, default is 1 minute. Shorter duration mean more accurate the results, but it takes longer for script to run.
Concurrency in Lync Environment
As long as you have got Lync monitoring server (is there any reason for not having it?) we can extract the data required for the script from there. You can get concurrency reports for as far as you have got the data in Lync Monitoring database. To give you an idea the kind of reports which can be generated are:
- Concurrency of inbound/outbound calls from/to gateway/s with granular breakdown. E.g. you can view at 10:00 AM you had 15 outbound concurrent calls, at 10:01 AM you had 20 inbound calls, and at 10:05 AM you had 30 total inbound and outbound calls.
- How many concurrent calls are made between 2 offices e.g. London and Paris?
- How many concurrent calls are being routed via Mediation server/s?
- How many concurrent conference calls are handled by each Front End server/pool?
- What is the concurrency of Video Calls (it will be lot less then you think!)?
- How many concurrent calls are going via your Edge Server?
- Concurrency of any modality e.g. Voice, Video, Application sharing which has got a StartTime and EndTime.
- How many planes were flying in the UK at any one point in time? Yes, you an work this out as well!
Now that I have given you an idea of what kind of information you can pull together, let’s see how we can achieve this. I will go through couple of key reports here, but as I mentioned anything with StartTime and EndTime will work….literally.
The first task we need to complete is to export the data from Lync database into a CSV file. There are couple of ways to do this. You can either directly go into the Lync CDR Database or export the necessary data by executing the SQL query. Alternatively, you can use the Lync Insights module to export the data.
In this example, we will use the Lync Insights module. The benefit of using the Lync Insights module is that you don’t need SQL Management studio or even logon to the server. Also when using the Lync Insights module you get output as PowerShell objects which you can easily export into a CSV. The downside of using Lync Insights module is that it can take a long time for the export if you have got lot of data, say 100,000 rows returned as it has to convert everything to PowerShell objects.
- I’ll assume that you have already downloaded the Lync Insights module, if you haven’t, please see the details here.
- Now establish the connection to the SQL server using Connect-LsMonDB. More details on how to do this cmdlet here.
- Once successfully connected, we can use Invoke-LsSqlQuery cmdlet to execute any SQL query.
- In this example I am going to export information related to Audio calls which contains information around what Mediation server, Gateway, Edge server was used, Caller, Callee, etc.
- Run the following query to export the data from Lync monitoring database. Make sure to change the value of $RecordsToReturn to how many rows you want returned. This query works with Lync 2013 only. Remember, more data you want returned longer it will take to execute.
$RecordsToReturn = 1000
$query = "SELECT TOP $RecordsToReturn
[InviteTime] As StartTime,[FromUri],[ToUri],[EndTime] ,[FromClientVersion],[ToClientVersion]
FROM [LcsCDR].[dbo].[VoipDetailsView] order by StartTime desc"
Invoke-LsSqlQuery -sqlquery $query -sqldb lcscdr | export-csv .\voipdetails.csv -NoTypeInformation
- After you have run the above query you should have CSV file which you can feed into the Concurrency Script for processing. The CSV file we will be using for this blog post can be downloaded here.
Once you have got your hands on a CSV file you can either literally feed it into the Concurrency script as it is or you can modify it based on your requirements (remove some columns, remove some data like certain gateways, etc). Make sure if you are using Excel to modify the CSV the Date column retains the Date as after opening the CSV in Excel and then saving it messes up the date.
Now run the Concurrency Script and enter the CSV file path . Although several parameters can be specified For now let’s only specify a CSVFile parameter.
The below figure displays the number of concurrent calls running at each minute. As we did not specify any value for filterby parameter no other columns like From/To Gateway, From/toMediation server, etc. are taken into consideration. Columns other than StartTime and EndTime are completely ignored.
Now let’s filterby a column. Assume you want to know the concurrency of the calls based on FromGateway column from the CSV file we exported earlier. FromGateway column shows you the calls From the Gateway to the Lync server which actually means all the inbound calls (from PSTN/Gateway) to Lync.
The above screenshot shows you the inbound calls that were running on the Gateway at the displayed time . Using this you can work out what are your busiest and quietest periods for inbound calls.
Now let’s get call concurrency for all the outbound calls passing through the gateway. Can you guess which column we need to look at? It will be ToGateway, which is all the calls made from Lync to the Gateway.
The above screenshot shows you the outbound calls that were running on Gateway at the time specified. Using this you can work out what are your busiest and quietest periods for inbound calls.
Great, so now we are at a point where you can accurately determine what your inbound and outbound call concurrency will be. But what if you want is one number– total call concurrency – inbound and outbound. Surely, you can total up the output of the above 2 commands and work it out, right? Well, there is an easier way.
The Show-ConcurrencyReport script has capability to combine as many columns you want and report on the concurrency. You achieve this by providing multiple values to the filterby parameter. An example would make it clearer.
To get Call Concurrency of both inbound and outbound calls we will combine both FromGateway and ToGateway columns as below. The result you see is the total number of calls –inbound and outbound- going through that gateway.
And Few more examples…
Concurrent calls through Mediation server
The above screenshot displays the total calls – inbound and outbound on the Mediation server. If you only want inbound calls just specify FromMediationServer or if you require outbound calls specify ToMediationServer values separately as input of filterby parameter
Concurrent Calls through Edge
The screenshot above displays the concurrent calls through the edge server. This gives you a good idea how heavily your Edge servers are being used for voice.
Getting the data out is one thing, making sense of it is different. What you get as an output is huge amount of data depending on the input. Let’s see how we can slice and dice the data to provide more meaningful reports. I realise there will be different types of consumers of the concurrency data. I have divided them into few categories:
Stuff the output of the script into a variable and do all sort of things – Sort based on your criteria (sort-object), remove unwanted data (where-object), select specific columns, etc. As you are power user I’ll leave out any examples and screenshots.
I am a busy admin, just give me the data!
One of my favourite technique in PowerShell when filtering data is using Out-GridView. You can use the Out-GridView cmdlet to filter the data and display on the console or directly export it to a CSV for further analysis. I think this is a great technique to work with the data and at the same time getting some basic visual filtering capabilities, perfect for a busy admin
The cmdlet below displays the output in a GridView which can be filtered:
Once you execute the above cmdlet you will get a window popup where you can filter the data as per you criteria.
Now you can take this further as well by filtering the data using GUI and then export the data to a CSV using the following cmdlet:
Certainly none of the techniques above will produce anything which you can just send to your manager. It too much data. You gotta represent it in a more visual and meaningful format.
Well….then all you would have to do it export the data to a CSV and do the necessary data analysis in Excel.
Open up the data in Excel, pivot it and generate few graphs.Here are few screenshots which provides a visual view of how the data looks:
The screenshot below displays the calls on each gateway at a particular time.
The graph below displays the call concurrency per gateway at a particular time
I have only touched on few examples here but as mentioned as long as you can provide StartTime and EndTime of the calls you can query any data input.
You can extend the call concurrency reporting whole lot further e.g. you can schedule an automated daily report of concurrency calls for your gateways. Or you can create a script around this to send you an alert when concurrency for a certain gateway exceeds X number of calls, providing you almost real-time alerting! What about call concurrency between sites, conferencing numbers, etc.? Well, I think we have covered plenty of ground in this post so, let’s leave something for next time!
And what about – How many planes were flying in the UK at any one point in time? Well as long as you have the departure time and the arrival time of each flight you can work out how many planes are up in the air at any one time in the UK:)
Show-ConcurrencyReport– Script to generate Concurrency report
VOIPDetails – Sample export from Lync monitoring database. You can use this CSV file to test against the script