Automating the collection of CQM data

Article
10/20/2015

Editor’s note: The following post was written by Office Servers and Services MVP Lasse Nordvik Wedø as part of our Technical Tuesday series.

Installing and deploying Skype for Business (formerly known as Lync) is one thing, but knowing everything is ok and in order is something entirely different. Many customers believe not every bad or poor call is submitted to helpdesk, and this leaves them with “this feeling” something is not right and does not work.

There is no need for this fear or feeling. If the deployment includes a monitor server (and every deployment should), all the Call Detail Records (CDRs) and Quality of Experience (QoE) data is right there, at you fingertips. All you have to do is to take a look at it. Unfortunately, many find the Monitor reports slightly difficult and slow to navigate, and also it can be hard to know exactly what you are looking for.

That’s where the Call Quality Methodology (CQM) comes in. It is a very powerful way of getting exact reports on what’s good, what’s bad and how your deployment is trending. It’s a small script, querying the Monitor Database and dumping a lot of metrics into text files. Using the spreadsheet that comes along with it, you can then get graphs and tables describing the health of your deployment.

I have shown this tool to many customers, and they all fall in love with it. Their only problem is, they forget to run it on a regular basis, which is needed if you want to keep monitoring your deployment and take action on possible arising issues.
That is why I have created a script which can be used to automate parts of the process, and send the collected CQM data in a zipped file to a desired mailbox. This is how you can use my script in conjunction with the CQM tool to create a monthly report on your deployment.

Prerequisites:
• Download and extract the “Microsoft Call Quality Methodology (CQM) Scorecard” package to the server from where the reporting script is run. The current release is available at TechNet: https://technet.microsoft.com/en-us/library/mt126216.aspx
• Download the Get-CqmAutomated.ps1 script from the TechNet Gallery to the server from where the reporting script is run: https://gallery.technet.microsoft.com/lync/Get-CqmAutomated-807c2095
• A valid user account which can run the script and has access to the Monitor Database. (Read the CQM for database access details.) Local admin rights on the server where it’s run.
• A valid smtp server, sender and receiver email address
• A copy of the Scorecard on the computer where analysis will take place
(figure 1: file downloaded to c:\cqm)

When run, the Get-CqmAutomated.ps1 script will identify the monitor database (presuming there only is one database), it will detect your database’s date format and launch CQM.PS1 from the specified folder (passing all required parameters).
For automation purposes, I automatically set the current date as the CQM “EndDate” and let the script calculate the “StartDate” based on parameter input with switches for 30, 60, 90 or 180 days. 30 days will be the default selection if there is no input. Given necessary email parameters, it will send a zipped file with the content to the designated address (be mindful of the size of log files, and possible mail server restrictions).

Use Scheduled Tasks to launch these scripts on a regular basis. One way to configure this is through powershell. And this is how to set the task up to run on a Sunday every 4 weeks
Create the task (edit path and mail parameters):

$task = New-ScheduledTaskAction -Execute 'Powershell.exe' `
-Argument '-NoProfile -WindowStyle Hidden -command "& {c:\cqm\Get-CqmAutomated.ps1 `
-SourceFolder C:\CQM -SendEmailOnComplete -EmailFrom "someone@somewhere.com" `
-EmailTo "someoneelse@somewhere.com" -SMTPServer "10.0.0.42"}"'

Create the trigger:
$trigger = New-ScheduledTaskTrigger -Weekly -WeeksInterval 4 -at 11pm -DaysOfWeek Sunday

Register the scheduled task:
Register-ScheduledTask -Action $task -Trigger $trigger -TaskName "Get-CQMAutomation" `
-Description "Monthly report from Monitor Database" -User "domain\user" -Password "enterpassword" -RunLevel Highest

Create the trigger:
$trigger = New-ScheduledTaskTrigger -Weekly -WeeksInterval 4 -at 11pm -DaysOfWeek Sunday

Once these steps have been completed, we should be able to see the task in the regular “Scheduled tasks”. Please note the task is set to run with highest privileges and whether a user is logged on or not.
(figure 2, scheduled tasks)

If everything is set up right, you should be receiving an email when the script is run. The email contains a zipped file with all the collected data, and information about the date format.
The email should look something like this:
(figure 3 email)

The next step is to extract the files into a folder on your local hard drive, follow the instructions 1-11 and study the results. Please not the default separator selected in step “1” is “,” in most systems. This may not be true for all deployments, and the files should be inspected before actual import.
The value for “Date Format” in step 2 is printed in the email (discovered by the script). The rest is up to individual preferences.
(figure 4 scorecard start)

There is a lot of good information on CQM out there, and one of the best places to look for information on how to use this great tool is at Jens Trier Rasmussen’s blogs at TechNet: https://blogs.technet.com/b/jenstr/ Jens Trier Rasmussen is one of the contributors to the tool. Another great place to find further information is at my fellow MVP Ståle Hansen’s blog. Here you can find links to posters, current releases and other blogger’s pages on CQM: https://msunified.net/tag/call-quality-methodology/
In addition, I will repost this post and any updates to the script on my own blog, under the following tag: https://uc.lawedo.net/search/label/CQM

I hope you all find this tool and post helpful, and I really encourage all Skype for Business administrators to use the CQM on a regular basis. It is a systematical approach to monitor and maintain service quality over time.

About the author
Lasse is chief architect of BusinessCloud Microsoft UC Solutions at Datametrix AS, as well as a conference speaker, blogger, usergroup co-founder and hobby photographer in Norway.

Automating the collection of CQM data

Additional resources