To collect the Call Quality data from the Lync Quality of Experience Database you will have to go to the “Microsoft Call Quality Methodology Scorecard for Lync Server” folder in the extracted Lync Server Networking Guide.
In this folder you will find more information on how to run the CQM Queries and the scripts required for collecting the CQM Data. The CQM.ps1 Script takes many parameters so we will need to find out all the correct values. We have to gather the following information:
- Monitoring Server SQL Store
- Monitoring Server SQL Store Date Format
- Lync Server Version
- Poor Stream Conditions
Example parameter values for the CQM .ps1 script
.\CQM.ps1 -StartTime "01/01/2014" -EndTime "01/31/2014" -QoeInstance "sql01\lync" -DataRoot "C:\CQM" -LyncVersion “Lync2013” –PoorStreamCondition CQM
Monitoring Server SQL Store
To find out which Monitoring Server SQL Store is being used by the Lync Monitoring component you will have to look into Lync Topology Builder to get this information.
Note: If the Monitoring SQL Server store has the following format “SQLServer\Default” it means that the SQL Server Default instance is used. You then don’t need to specify the Instance in the powershell script, and should only have to specify “SQLServer” in the script as it is in our case.
Monitoring Server SQL Store Date Format
The CQM script uses the StartTime and EndTime parameters directly in the SQL queries against the SQL server. It is therefore important that you specify the same date format that is used by the SQL Server.
The first thing we need to do run is the GetSqlDateFormat script to verify the current Data Configuration on the SQL Server.
Run the following command
.\GetSqlDateFormat.ps1 -QoeInstance "<SQLServer>\Instance”
.\GetSqlDateFormat.ps1 -QoeInstance “sql01.contoso.com”
In my case the date format on the SQL Server is “MM/DD/YYYY” I will need to use this date format in the CQM script.
StartTime & EndTime
We then must look at the Lync Configuration in the Control Panel or with Lync Management Shell to view the current configuration of the Quality of Experience Data settings (This will be the maximum number of days we can have trending in the CQM Reports, in this case the value is 220 days)
You can also do this in PowerShell by running the following command:
If you want to get all the CQM Data then you need to calculate the StartDate and subtract KeepQoEDataForDays. You can do that with the following script.
(Get-Date).AddDays(<minus KeepQoEDataForDays from Lync Configuration>)).ToShortDateString()
In my case the results indicate: 1/26/2014 which I can use for the StartTime Parameter or I can choose any date later than that depending on how much information I want to collect.
Lync Server Version
This is something you probably know by head. Possible options are Lync2010 & Lync2013.
Poor Stream Conditions
You can select from the following three different conditions:
- CQM: PacketLossRate > .01 or PacketLossRateMax > .05
This is the Default option which specifies that all call that have an Average packet loss rate of 1% and a Maximum packet loss rate of 5% are considered poor calls
- ClassifiedPoorCall: PacketLossRate > 0.1 or DegradationAvg > 1.0 or RoundTrip > 500 or JitterInterArrival > 30 or RatioConcealedSamplesAvg > 0.07
This option matches the conditions used to flag a call as poor in Lync which is not as good as CQM when it comes down to identifying call quality issues, CQM essentially makes the quality issues more visible.
- Custom: PacketLossRate > .01
This option allows you to create custom conditions.
Now that we have gathered the following information:
- Monitoring Server SQL Store (sql01.contoso.com)
- Monitoring Server SQL Store Date Format (mm/dd/yyyy)
- (Maximum) StartTime (1/26/2014)
- EndTime (9/3/2014)
- Lync Server Version (Lync 2013)
- Poor Stream Conditions (CQM)
We can start gathering the CQM data from the Monitoring Server SQL Store by running the following command and fill in the required values.
.\CQM.ps1 -StartTime "01/26/2014" -EndTime "9/3/2014" -QoeInstance "sql01.contoso.com" -DataRoot "C:\CQM" -LyncVersion “Lync2013” –PoorStreamCondition CQM
The script does not take very long and the result of the script is a collection of CSV files that have been collected from the Monitoring Server SQL Store via SQL Queries.
The Collection process is complete and can now copy the result folder onto your local Workstation so that you can use the Score Card that is based in Excel.
You are ready to import all of the captured date in the Score Card.