This is a multi-part blog series. This is Part 1.For other blog posts in this series please click the relevant links – Part 2.
After completing the Lync deployment don’t you in the back of your mind think I wish I knew how Lync is being used, who is having issues, who is not using it, etc ? In fact, the burning questions, perhaps questions like these:
- To which PSTN number highest number of phone calls are made
- Who is on the phone for the longest duration (and who the least!)
- When was the last time someone logged on to Lync
- Find all the calls which are over certain thresholds e.g. over 10% packet loss, over 3ms Jitter, etc
- Generate alerts when your boss makes a call and the call is not good quality e.g. Over 10% packet loss, over 30ms Jitter, etc
- Find who is doing the most number of file transfers in your organization?
- Find who is transferring what file and to whom?
Standard Lync Monitoring reports provides you with great information on several elements of Lync usage and health.But Lync Monitoring reports only scratches the surface of what information is actually available in Lync Monitoring database. Lync Monitoring database is a goldmine of information – unfortunately in spite of Microsoft publishing the database schemas I have barely seen anyone querying database directly.
In order to attempt to make the Lync monitoring databases more accessible and answer the above burning questions and I have created a Lync Insights PowerShell module. My idea is to include in Lync Insights all kinds of different SQL queries I use on day to day basis for various customers and share with ever growing passionate Lync community!
Lync team did a great job of releasing Networking Guide and number of Call Quality Methodology (CQM) Queries. For now I have included all Call Quality Methodology (CQM) queries and few of my queries. Over time, I will be adding whole lot of custom SQL queries to it, esp. to answer all those burning questions above and loads more!
What is Lync Insights?
Lync Insights is a PowerShell module which has pre-built cmdlets to query Lync Monitoring database and contains all the CQM queries released by the Lync team. The module also contains cmdlets to query SQL database directly if the existing pre-built cmdlets do not contain the data you are looking for. In fact as with other PowerShell tools I create the module is based on same principles:
Extensible – So that you are not restricted to inbuilt cmdlets, I have included Invoke-LsSqlQuery cmdlet. Give this cmdlet any SQL query and it will return the data as rich PS Objects.
Scalable – Converting SQL objects to PS Objects is very expensive operation and can take long time esp. if you have lot data to crunch. If you are one of those who just can’t wait Invoke-LsRawSqlQuery is for you.It will accept SQL query and return raw SQL data (not PS Objects)
Simple – Extremely simple to use, logical cmdlet naming and built-in help.
What can I do with Lync Insights?
The data you retrieve using Lync Insights module can be used for gathering statistical information from your Lync environment and assist you in troubleshooting.
- The module fully works with Lync Server 2013 Monitoring database only (although some queries may work with Lync 2010 as well).
- PowerShell v2 or higher
- Read access to Lync Monitoring databases on SQL – LcsCDR, QoEMetrics
- Name of the Monitoring SQL database server and SQL instance if applicable.
- No access to Lync environment or Lync PowerShell Module is required
- Once you have imported the Lync Insights module run Get-Command -Module LyncInsights cmdlet to discover what cmdlets are available:
- By default all cmdlets will displays data from last 30 days only. If the returned data has more then 1000 objects only top 1000 are displayed for performance reasons. You can modify the date range with fromdate/todate parameters to override this.
- If a query takes very long time to execute narrow down the results by using date filters
- Most cmdlets also have TOP parameter which you can specify to only see x amount of top results. e.g If you only want to see top 5 results.
- If you are interested in what SQL query gets executed in the background just add the verbose switch to the cmdlet. e.g : Get-LsP2PFileTransferCount –verbose will show you the associated SQL query.
Before you can use the queries the first cmdlet you need to run is Connect-LsMonDB .This cmdlet sets up the necessary connection parameters like SQL Servername, Credentials, etc which subsequent cmdlets use.
Run help Connect-LsMonDB –full to see all help associated with this cmdlet.
The key information you need to provide when using this cmdlet is the Lync Monitoring SQL server name. If you don’t know what that is run Get-CsService –MonitoringDatabase to find the Monitoring SQL server name. If you are using SQL authentication you would also need to provide SQL Username and Password and specify the –SQLAuth switch.
Below are some examples to show you how you can using Connect-LsMonDB
Once you are connected you are all good to run any of the above cmdlets. Most of the cmdlets will return data for last 30 days only by default for performance reasons, but if you have a large environment running some queries can take very long time. In that case I would recommended to narrow down the date range.
- List the number of file transfers each user has done
- List who is doing file transfers to whom and the associated file names
- Display the type of headsets used in different Audio streams and compare the MOS.
- List which subnets have higher percentage of poor call quality issues with date filters applied
- Run custom SQL queries which return PowerShell Objects
- Custom SQL query to return calls which has packet loss of >10%
$a = “SELECT SessionTime,Caller,CallerEndpoint,Callee,CalleeEndpoint FROM [QoEMetrics].[dbo].[AudioStreamDetailView] WHERE StreamID NOT IN (SELECT MAX(StreamID) FROM [QoEMetrics].[dbo].[AudioStreamDetailView] GROUP BY SessionTime) AND (PacketLossRate > '.10')”
And as with any PowerShell cmdlets you can perform advance filtering with the data to extract what you need. If you have Sonus devices you can also collect the CDR’s from there and correlate with Lync Monitoring databases for long term trending.
Download Link: You can download Lync Insights from here.
On this blog I have only demonstrated few examples but there is so much more you can do with these cmdlets, and build on top of these with your own queries.
I know I haven’t answered all of your burning questions yet, but just hang on there, the next update to Lync Insights will answer all of those questions and many more!