Advanced Office 365 Call Quality Dashboard PowerShell queries

• Author
• Recent Posts

Jeff Brown

Jeff is a systems administrator and cloud engineer with over a decade of experience in server and application administration. He has managed a wide range of technologies in his career, including Windows Server, Microsoft 365. and Azure. Jeff has also created video-based training for Cybrary and PluralSight. You can follow Jeff on his blog at jeffbrown.tech, or feel free to contact him on Twitter at @jeffwbrown.

Latest posts by Jeff Brown (see all)

• 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

In my previous post titled How to use the Office 365 Call Quality Dashboard PowerShell module, we looked at creating a basic PowerShell query to extract CQD data based on an existing report. However, we can explore a few more parameters for Get-CQDData as well as how to filter the data to make sure we are only looking at data pertaining to our internal corporate network.

I want to look at my network health as clients report at the end of their audio calls. For reference, during a Skype for Business or Teams call, the client records if it encountered any packet loss or jitter during the call due to network performance. It packages this data up and uploads it to CQD at the end of a call. We can use this data to see where clients are reporting poor network performance that might be leading to poor call quality. Let's look at our query after using the techniques from the previous article to find our dimensions and measures and save it to the variable $networkData:

$networkData = Get-CQDData -Dimensions 'SecondTenantDataBuilding.Second Network Name','AllStreams.Month Year' -Measures 'Measures.Avg Jitter','Measures.Avg Packet Loss Rate','Measures.Avg Round Trip' -OutPutType DataTable | Format-Table

Here's a screenshot of our returned data:

View of our network performance data

We have some named networks that correspond to our internal corporate network. I have already uploaded this building subnet information to CQD so it knows that data coming from certain subnets are from my corporate network. With this, there are also some blank network names. This could indicate cases where the client did not report a subnet or the client was on a network outside the corporate network, meaning CQD does not have a network name for it.

Since I am focusing on how to improve the internal network, I don't care about these blank network names and want to filter them out. In the CQD portal, we can add a filter in the query editor to remove blank network names. Unfortunately, the Get-CQDData command does not have a filter parameter, so we'll need to improvise removing these blank networks using the Where-Object filter. I'm going to use regex on my $networkData variable to return only the Second Network Name containing letters.

Network data with missing network names filtered out

The trick is putting the column header in quotes since it contains spaces in the name. Another option for Where-Object would be to find network names not like an empty string (''):

$networkData | Where-Object {$_.'Second Network Name' -notlike ''} | Format-Table

Looking at other available parameters, there is IsServerPair. This identifies the relationship between the endpoints in a call and has three options:

1. Client: Client or peer-to-peer (P2P) calls
2. Client: Server or calls from a client to a server in Office 365, typically conference calls
3. Server: Server or traffic happening between servers in Office 365

You are primarily going to be concerned with "Client: Client" and "Client: Server." You can use these to look at traffic occurring either between people (P2P) or involving the Office 365 service. Why does this matter? Typically, if two users are on the same network and can connect to each other directly, they can place a direct call to each other (Client: Client or P2P). If they are unable to do so, or it is a call involving three or more people (a conference call), this requires servers in the online service to manage the call and traverse your internal network to the internet (Client: Server). Let's look at a new CQD query to see how many calls are occurring P2P or involving the Office 365 service.

Comparing P2P versus conferencing traffic

From running these queries, we can tell that our users are making more conference calls or making calls that require going out to the Office 365 service to complete. This means we had better make sure we have a good internet connection point with enough bandwidth to handle the call volume.

There are also some date-related parameters to filter out specific time periods. CQD keeps seven months' worth of data, so being able to drill down to specific durations of time can reduce the amount of data returned. These date-related parameters are:

• StartDate
• EndDate
• MonthYear
• WeekDays
• Week

From my testing, you must specify both a StartDate and an EndDate in the format YYYY-MM-DD. Including only one of these parameters will not filter down to the month. Here's the same query from earlier but narrowing down to the month of November:

Filter using StartDate and EndDate

For looking at a whole month, instead of specifying a start and end date, there is MonthYear in the format YYYY-MM. Here's the previous example using just this one parameter to return November:

Using MonthYear to return a single month

You can also filter down to a specific week using the format YYYY-MM-DD, with the month and day beginning each Sunday. I want to extract the number of audio streams for the week of December 2, 2018 like this (also switching my dimension from "Month Year" to "Week"):

Using the Week parameter

Finally, there is the Weekdays parameter. You can specify certain days of the week. To me, the name implies this accepts only values for Monday to Friday. I did test with Saturday, and the command did not give me an error, but I also did not have any data on a weekend to test. Here is an example showing streams that occurred on any Monday or Tuesday (also with the dimension changed to "Date"):

Query specifying specific weekdays

Our last parameter is MediaType. This has four options:

1. AppSharing
2. VBSS
3. Video
4. Audio

This allows you to look at specific modalities taking place in the tenant. You could look at usage trends in your environment, such as how much users use video or just audio during conferencing calls. You would need to change your measures to something like Measures.Total Stream Count before filtering using the MediaType parameter.

Some of these parameters give even the most novice PowerShell admins a good set of tools to extract the data they need. More advanced admins will need to use Where-Object to filter out unknown subnets and networks names to look at data for managed corporate networks. Hopefully Microsoft continues to develop this PowerShell module to make it even more useful to Office 365 administrators.