- Manage Azure Policy using Terraform - Tue, Aug 2 2022
- Getting started with Terraform in Azure - Tue, Jul 12 2022
- Azure Bicep: Getting started guide - Fri, Nov 19 2021
The Call Quality Dashboard (CQD) available in Office 365 for Microsoft Teams and Skype for Business is not a new tool. It was born from its on-premises server counterpart and translated for cloud usage. CQD is a collection of performance and diagnostic data gathered by the Teams or Skype client during an audio call, video call, or screen-sharing session.
At the end of each session, the client packages and uploads this quality-of-experience data to a central reporting database. CQD provides a portal for analyzing this data and providing additional insights into the quality of these services. For example, you can build custom reports to see where on the corporate network audio calls are performing poorly.
CQD allows for building reports and tables of data and provides a mechanism for extracting the data in the portal. However, if you have multiple reports used to extract data on a regular basis, this can be a tedious process to export the data from the portal manually. To assist with this process, Microsoft has built an API that allows extraction of this data, and on top of that, they have provided a PowerShell module that uses simple commands to extract the data.
If you are unfamiliar with CQD, I would suggest checking out the following resources first:
Microsoft on YouTube: Skype Operations Framework (SOF) CQD training series
First, let's discuss how to install this PowerShell module. Microsoft provides this module via the PowerShell Gallery repository. Hosting the module here allows for installing it directly from a PowerShell prompt using the Install-Module command. If you have never installed a module from the PowerShell Gallery, you may receive prompts about installing additional software as well as trusting the remote repository.
Installing the CQD PowerShell module
The CQDPowerShell module has three commands:
- Get-CQDData
- Get-CQDDimensions
- Get-CQDMeasures
While Get-CQDData is the workhorse in this module, we will not get far without the dimensions and measures. Dimensions and measures determine what kind of data we want to extract and how to group the data. Running the Get commands will display the property names we need later. These are some examples of the dimensions and measures returned from these commands:
Examples of available dimensions and measures
Before we get into extracting data using these properties, let's look at a built-in report in CQD to understand why they are important. This is a typical report with the number of good, unclassified, and poor audio streams as well as the percent of poor audio streams. Given this is a small demo tenant, there's not much in the way the chart looks, but that's not what we're focusing on.
CQD example report
If we edit this report, we will see that these chart properties are all measurements, and they are grouped by month and year or a dimension:
Report details showing dimensions and measurements
Looking at the left side of the edit window, we see the full names of the dimensions and measures used for building the report. Using the Get-CQDDimensions and Get-CQDMeasures commands and some filtering, we can find the equivalent names of these properties for our PowerShell command:
Searching for matching dimensions and measures
These are translations from the CQD report to the PowerShell equivalents:
- Month Year > AllStreams.Month Year
- Audio Good Call Stream Count > Measures.Audio Good Call Stream Count
- Audio Poor Call Stream Count > Measures.Audio Poor Call Stream Count
- Audio Unclassified Call Stream Count > Measures.Audio Unclassified Call Stream Count
- Audio Poor Call Percentage > Measures.Audio Poor Call Percentage
We now have all the pieces to get the CQD through our PowerShell command. The Get-CQDData command has several parameters, but the ones we are interested in for now are Dimensions, Measures, and OutPutType. We will build the command using the PowerShell names of the same dimensions and measures used in our report. Since there are spaces in them, we need to enclose each one in quotes and separate multiple ones with commas, and we will output the data into a DataTable:
Subscribe to 4sysops newsletter!
Get-CQDData -Dimensions 'AllStreams.Month Year' -Measures 'Measures.Audio Good Call Stream Count','Measures.Audio Poor Call Stream Count','Measures.Audio Unclassified Call Stream Count','Measures.Audio Poor Call Percentage' -OutPutType DataTable
Get CQDData command with dimensions and measures
By running this command, we now have the same data from our report listed in a PowerShell window, and we can save it to a variable or export it to a CSV file using other parameters of Get-CQDData. From here, I would recommend continuing to build reports in the CQD portal, and at the same time, find the PowerShell names of the dimensions and measures to build the equivalent command. This will allow you to automate the extraction of the data to ingest it into other tools for analysis. This can be a big time-saver if you are constantly going into the CQD portal to download this data manually.
Hi Jeff,
great article!
Was really working for me. But…
When I’m trying to add the dimension ‘AllStreams.is Teams’ I got “Error Querying CQD with Error 6” I assume this is due to the fact that the module is checking on cqd.lync.com and not on the CQDv3 link?
If so, can I change this?
Best regards
Michael
Hi Michael, I tried the following command and I got results back:
I tried a lowercase ‘i’ for ‘is Teams’ and got the Error 6 you are referencing. It seems the API is case-sensitive! Very interesting.
The question on the old versus CQDv3 is a good question. Not sure how the module will work with the upcoming changes in the platform.
Hi Jeff,
I’m a bit embarrassed about the case-sensitive thing, but at the end you saved my day and that’s the important thing to look at 😊
Tried also on my side and it is working too.
This module is definitely what I was looking for. Thank you so much!!!
Best regards
Michael
Hello Jeff,
Very nice module indeed. I have walked through the code and already made some improvements. I have added a "universal filter" to the code, so you can customize the query more effectively.
For example you will append your query with parameter -universalfilter @('First Subnet=192.168.1.0','Month Year=2019-06')
It believe it is self explanatory, and you can append unlimited number of filters here.
I am willing to share if you agree, maybe we can work together on enhancements 🙂
Also I would like to ask if it is possible to add appid/secret authentication instead of windows form.
Thanks
Michal
Hi, So I had no problems with installing the CQDpowershell. However, when I try to run the import-module CQDpowershell, and after entering my credentials, I am immediately hit with:" The resource you are looking for has been removed, had its name changed, or is temporarily unavailable."
I can't find anything on the internet that lets me resolve it. Any suggestions?
Hi Victoria, what permissions do you have in Office 365? That's the first thing that comes to mind, you'll need Global Admin or Skype Admin at a minimum.
Hi,
I have installed the module, then just trying the Get-CQDDimensions and received following error:
Invoke-WebRequest : Le nom distant n'a pas pu être résolu: 'cubestructure'
At C:\Program Files (x86)\WindowsPowerShell\Modules\CQDPowerShell\1.11\CQDPowerShell.psm1:201 char:18
+ … beRequest = Invoke-WebRequest -Uri ('{0}CubeStructure' -f $DataServic …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], Web
eption
+ FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand
Have you ever encoutered that ?
You mentioned these commands work for MSFT Teams. Is that correct? Is there any ref that I can refer to?
Why is there no measure for total call count
For automation, the PS script prompts for O365 authentication each time. How can this be handled/ bypassed ?
You could save your (or some management account) password hash to the script, or a file or a registry. If you run the script on the same machine under the same account each time, it should work. Tho storing passwords in scripts is not considered as a security best practice (even the password hash is encrypted by the machine and account RSA key).
I am running the script with following command and I am login on powershell with my account and it is not asking for any other credentials.
Get-CQDData -Dimensions 'AllStreams.Month Year' -Measures 'Measures.Audio Good Call Stream Count','Measures.Audio Poor Call Stream Count','Measures.Audio Unclassified Call Stream Count','Measures.Audio Poor Call Percentage' -OutPutType DataTable
Get-JWTData: /Users/irfanmaroof/.local/share/powershell/Modules/CQDPowerShell/2.0.0/CQDPowerShell.psm1:178
Line |
178 | $JWTDecoded = Get-JWTData $Script:Token
| ~~~~~~~~~~~~~
| Cannot bind argument to parameter 'Token' because it is an empty string.
Invoke-WebRequest: /Users/irfanmaroof/.local/share/powershell/Modules/CQDPowerShell/2.0.0/CQDPowerShell.psm1:252
Line |
252 | … beRequest = Invoke-WebRequest -Uri ('{0}CubeStructure' -f $DataServic …
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| nodename nor servname provided, or not known
ConvertFrom-Json: /Users/irfanmaroof/.local/share/powershell/Modules/CQDPowerShell/2.0.0/CQDPowerShell.psm1:253
Line |
253 | $List = ConvertFrom-Json $CubeRequest
| ~~~~~~~~~~~~
| Cannot bind argument to parameter 'InputObject' because it is null.
PS /Users/irfanmaroof/code/cronacrisis> Get-CQDData -Dimensions 'AllStreams.Month Year' -Measures 'Measures.Audio Good Call Stream Count','Measures.Audio Poor Call Stream Count','Measures.Audio Unclassified Call Stream Count','Measures.Audio Poor Call Percentage' -OutPutType DataTable
Get-JWTData: /Users/irfanmaroof/.local/share/powershell/Modules/CQDPowerShell/2.0.0/CQDPowerShell.psm1:178
Line |
178 | $JWTDecoded = Get-JWTData $Script:Token
| ~~~~~~~~~~~~~
| Cannot bind argument to parameter 'Token' because it is an empty string.
Invoke-WebRequest: /Users/irfanmaroof/.local/share/powershell/Modules/CQDPowerShell/2.0.0/CQDPowerShell.psm1:252
Line |
252 | … beRequest = Invoke-WebRequest -Uri ('{0}CubeStructure' -f $DataServic …
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| nodename nor servname provided, or not known
ConvertFrom-Json: /Users/irfanmaroof/.local/share/powershell/Modules/CQDPowerShell/2.0.0/CQDPowerShell.psm1:253
Line |
253 | $List = ConvertFrom-Json $CubeRequest
| ~~~~~~~~~~~~
| Cannot bind argument to parameter 'InputObject' because it is null.
Get-JWTData: /Users/irfanmaroof/.local/share/powershell/Modules/CQDPowerShell/2.0.0/CQDPowerShell.psm1:178
Line |
178 | $JWTDecoded = Get-JWTData $Script:Token
| ~~~~~~~~~~~~~
| Cannot bind argument to parameter 'Token' because it is an empty string.
Invoke-WebRequest: /Users/irfanmaroof/.local/share/powershell/Modules/CQDPowerShell/2.0.0/CQDPowerShell.psm1:252
Line |
252 | … beRequest = Invoke-WebRequest -Uri ('{0}CubeStructure' -f $DataServic …
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| nodename nor servname provided, or not known
ConvertFrom-Json: /Users/irfanmaroof/.local/share/powershell/Modules/CQDPowerShell/2.0.0/CQDPowerShell.psm1:253
Line |
253 | $List = ConvertFrom-Json $CubeRequest
| ~~~~~~~~~~~~
| Cannot bind argument to parameter 'InputObject' because it is null.
New-Object: /Users/irfanmaroof/.local/share/powershell/Modules/CQDPowerShell/2.0.0/CQDPowerShell.psm1:790
Line |
790 | … Attribute = New-Object System.Management.Automation.ValidateSetAttrib …
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| A constructor was not found. Cannot find an appropriate constructor for type System.Management.Automation.ValidateSetAttribute.
New-Object: /Users/irfanmaroof/.local/share/powershell/Modules/CQDPowerShell/2.0.0/CQDPowerShell.psm1:791
Line |
791 | … Attribute = New-Object System.Management.Automation.ValidateSetAttrib …
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| A constructor was not found. Cannot find an appropriate constructor for type System.Management.Automation.ValidateSetAttribute.
Working….
Error Binding
$UserName = "abc"
$SecurePassword = Read-Host -Prompt "Enter password" -AsSecureString
$Creds = New-Object System.Management.Automation.PSCredential -ArgumentList $UserName, $SecurePassword
$Session = New-PSSession -ConnectionUri https://cqd.teams.microsoft.com/ -Credential $Creds -Authentication Basic -AllowRedirection
Import-PSSession $Session
Get-CQDData -CQDVer V3 -Dimensions 'AllStreams.Month Year' -Measures 'Measures.Avg Call Duration','Measures.Audio Call Count' -outPutType DataTable -MediaType 'Audio' | format-table
Session variable is not working and gives popup for O365 auth. what needs to be tweaked to get this working?
Yes need script to automate and schedule in task schedular.
Tried multiple time but it is asking for credentials for connecting to Microsoft call quality dashboard
You're script is working like a charm, and explanations, examples, etc are really helpfull.
Thank you!!!
Need to fetch all the Conderence ID from CDQ, Any script or suggestion will be appriciated.
Thanks
Hello Jeff, good morning, sorry, bit of an old topic, but just looking for some guidance on the CQD PS module. Nice article, is there a way to obtain conferences organised by a user? Tried:
$CQDDataUserCallCount = Get-CQDData -Dimensions 'AllStreams.Date','AllStreams.Conference Id','AllStreams.Organizer UPN' -Measures 'Measures.Total Call Count','Measures.Total Audio Stream Duration (Minutes)' -OutPutType DataTable
This does remove the Organiser ID Column, which I understand due to this not being a filter, is there a way I could relate Organizer and Conference IDs togheter. I would also need to obtain participants and their type. Can do it on BI using the CQD connector but seems data is not exportable directly hence the use of this PS Module.
Thanks in advance.
Great Article ! thanks for that.
I want to add time as a filter in -StartDate & -EndDate. Can you please help me in that.
Actually, I need hourly data so when I pass -StartDate – '11-02-2021 01:00:59'
-EndDate '11-02-2021 11:00:00', it just shows as working and no output comes.
Has anyone found a way to successfully automate the login pop-up?
Have each of you put in an enhancement request to your Microsoft account team?
Microsoft should publish CQD data via a standard API endpoint similar to the Graph API where they host the more detailed CDR data.
Mike
Hi ,
I am using C# for running script “Get-CQDData -Dimensions ‘AllStreams.Month Year’ -Measures ‘Measures.Audio Good Call Stream Count’,’Measures.Audio Poor Call Stream Count’,’Measures.Audio Unclassified Call Stream Count’,’Measures.Audio Poor Call Percentage’ -OutPutType DataTable”
Everything working fine but I am getting blank popup window for Microsoft call quality dashboard sign in.
I am not able to see user name and password in popup window.
Please help me.
What authentication mechanism you have used?
Hi Ajit,
Do you have any idea successfully automate the login Microsoft call quality dashboard pop-up using PowerShell script.
Hi,
when i run Get-CQDDimensions, it gives me error
Get-JWTData: Cannot bind argument to parameter ‘Token’ because it is an empty string.
Invoke-WebRequest: nodename nor servname provided, or not known
ConvertFrom-Json: Cannot bind argument to parameter ‘InputObject’ because it is null.
Could anyone pls help.
Thanks
Thanks for the article.
It has helped me quite far down the route of writing an application written in the .net framework.
But, I have a problem.
– I want to be able to run this without having to authenticate with MS each time (I want to pass in credentials from an encrypted store and run it without any user interaction)
– Can Get-CQDDimensions etc. be run and passed credentials to avoid the MS Authentication dialog ?
I need the PS script prompts for O365 authentication each time. How can this be handled/ bypassed ?
Any idea successfully automate the login to Microsoft call quality dashboard pop-up using PowerShell script ?
Hi,
Any idea successfully automate the login to Microsoft call quality dashboard prompts for O365 authentication each time using PowerShell script ? How can this be handled/ bypassed ?
We had to use an intermediate machine to RPA its way through the login and collect the data and advertise the data back out as an API.
I wish Microsoft would improve their CQD API (authentication).
Is there really still no way to automate the login process? I know with Connect-MicrosoftTeams I can very easily pass credentials too it and automate a script. This module would be perfect if I could automate the login process.
If you need to add multiple filters for example here (CustomFilter):
You need to do it like this:
And looks like this is not working (“Operand”:3) is not working on this scenario:
{"DataModelName":"[AllStreams].[Packet Loss Rate Max]","Caption":"","Value":"[026: [0.2 - 0.25)]","Operand":3,"UnionGroup":""}Workaround is add all of them as an array, but let see if there is an another way.