So you have deployed Lync or Skype for Business, especially with Enterprise Voice, and native monitoring tools. Eventually you will find that really digging into analytical and performance data of your Skype for Business deployment using these native tools…leaves a bit to be desired. However, while we all are aware that I am a HUGE fan of UC Commander from @eventzero, sometimes you can’t afford a 3rd party analytics solution for Skype for Business or you just need the ability to do “tactical” reporting on issues or reports for management.
Welcome to Call Quality Methodology (CQM), a set of powershell scripts, Excel queries, spreadsheets and associated documentation from Microsoft. The key components of the CQM “bundle” (you will see these in the Microsoft Call Quality Methodology.zip file once extracted) are as follows:
1. The CQM.ps1 powershell script
2. The Microsoft Call Quality Methodology Scorecard of Skype for Business v15 Excel Worksheet. (Note this is Macro enabled and as such I haven’t had much luck getting it to run correctly on Excel for Mac 2016 but I assumed that 🙂 )
Inside of the Documentation folder you will find the overall Microsoft Call Quality Methodology Word documentation that covers many aspects of running the script and the different switches that can be run. Without going into too many aspects because honestly for most organizations the default options in the CQM.ps1. A brief description of some of the switches available is included below.
Process to provide a frequent, lets say weekly, CQM report
1. Getting the data – running CQM.ps1
If you need to run a CQM report on a regular basis, here is a simple procedure to use to make a repeatable process. Using this I can just change a single parameter to run the report frequently. Lets dissect the following switches:
.\CQM.ps1 -StartTime “9/23/2015” -EndTime “9/30/2015″ -QoECdrInstance “yourmonitoringstoreSQLserver.contoso.com” -DataRoot “C:\lyncscripts\cqmdata\” -QoECdrVersion “SfB2015” -ExcludeST $true -PoorStreamCondition:CQM
Looking at the switches
.\CQM.ps1 -StartTime “9/23/2015” -EndTime “9/30/2015” – This switch is pretty obvious. Input the range of the time period of the data you want. Start Time and End Time are required
.\CQM.ps1 –QoECdrInstance “yourmonitoringstoreSQLserver.contoso.com” – This is the full path to your QoeMetrics and LcsCDR database. If you have an dedicated instance use the format “yourmonitoringstoreSQLserver.contoso.com\yourinstancename”. Required
.\CQM.ps1 -DataRoot “C:\lyncscripts\cqmdata\” – This is the output path of the CQM data. The rest will be the older that you to import into the CQM scorecard spreadsheet. This is required.
.\CQM.ps1 –QoECdrVersion “SfB2015” – This is the version of the monitoring server data you have i.e, if you are on 2010 it would be “Lync2010”, or “Lync2013”, or “SfB2015” respectively. Required
.\CQM.ps1 -ExcludeST $true – This switch will exclude streams form synthetic transactions. If you are running watcher nodes in your deployment, you can select this to exclude these transactions from being added. Optional
.\CQM.ps1 -PoorStreamCondition:CQM – This specifies what value to use to flag a call as POOR. It is important to view section 3.4.2 of the Microsoft Call Quality Methodology word document for more information on using this parameter. Optional
2. Import the data to into the CQM scorecard Excel spreadsheet
Once the script is run successfully, and again look at the Microsoft Call Quality Methodology word document for more information on this, you should see “Gathering the CQM results”. Once complete, you will have a folder with in the format ‘CQM_Data_10_1_2015” where the date is the end of the range you specified in the CQM.ps1 command.
Next, open the Open the ‘Microsoft Call Quality Methodology Scorecard for Skype for Business Server v15’ Excel workbook. (You may need to enable editing and enable content).
Now, ‘Import CQM Query Results’ and choose the CQM_X_XX_XXXX folder. It may not look like anything happened but when you click the ‘Generate Stream Distribution Charts’ button you should see data as it shifts to the tab.
One note: You may receive a pop-up when trying to run the ‘Update Scorecard’ report with a “Runtime: Error 13” and basically the workbook blows up. I have received an updated CQM scorecard that may resolve this so let me know if you need it.
So to recap, you can make a simple batch file to launch your CQM.ps1 with the switches you want in a repeatable way by just editing a single value such as date. I realize there’s probably a dozen better ways to do this but please feel free to add some suggestion in comments.
Some other great resources for Call Quality Methodology can be found below:
Thanks for stopping by.