I previously covered the basics on how to install the Call Quality Dashboard (CQD) PowerShell module and perform a basic query. This article is a follow-up on how to perform more advanced queries to extract the Microsoft Teams and Skype for Business Online data in your tenant.
Latest posts by Jeff Brown (see all)

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

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

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

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

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

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

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

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.

Subscribe to 4sysops newsletter!

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.

  1. Arda 3 years ago

    Thank you for this tutorial. I was wondering if it possible to execute this with a filter that chooses records with "AllStreams.End Time" greater than a certain date. 

    I would like to use this field as my date picker instead of the StartDate and EndData options.


  2. Simona 2 years ago

    Hi, do you know how to output this data to Power BI? I know that there is a CQD Connector for Power BI, but it has lots of limitations and it pulls a humongous amount of parameters. 

    Thanks in advance for any clues you can provide!

    • Author
      Jeff Brown (Rank 2) 2 years ago

      I haven't worked with the Power BI connector to CQD yet. If you're finding too much information, maybe export via PowerShell to another data source that Power BI can use for input.

  3. kane 2 years ago


    Are we able to filer this by date and time value?

  4. Mansi 2 years ago

    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.

  5. Kerry 2 years ago

    Is there a way to build a custom filter to do a logical OR? I want to pass a list of AllStreams.Second Client Endpoint Name in one query. Obviously I could loop through it (doing that now) but that is very slow

Leave a reply

Please enclose code in pre tags

Your email address will not be published.


© 4sysops 2006 - 2023


Please ask IT administration questions in the forums. Any other messages are welcome.


Log in with your credentials


Forgot your details?

Create Account