Discover all headsets used in #MicrosoftTeams PowerBI report

Part of succeeding with Microsoft Teams in the organization is to use certified for Microsoft Teams devices. I wrote about this in my article Why certified for Microsoft Teams devices are important. To succeed with this we need visibility and knowledge about what is being used in our environment.

I have created a simple PowerBI report for this. Download it here. The report will find which devices are used within your organization. The report uses the CQD connector for PowerBI and you can publish it and add it as a tab in Teams.

The report connects directly to the CQD database which is the only supported way. Because of this, the user who accesses the report in Teams needs one of the following admin roles assigned.

  • Global Administrator
  • Global Reader
  • Skype for Business Administrator
  • Teams Service Administrator
  • Teams Communications Administrator
  • Teams Communications Support Engineer
  • Teams Communications Support Specialist
  • Reports Reader

To just see the devices used without getting the UPNs of the users, you can use the Reports Reader or Teams Communications Support Specialist roles.

The report

By specifying the Second UPN you can see what kind of devices individuals in your organization are using. You will find that there are multiple entries of the same devices because Second Capture Dev name is different. There is currently no way of using regex to mitigate this. Second Capture Dev is the microphone used in the audio conversations and is an indication of device used regardless of PSTN, Teams call, or conference.

Default input device, Audio and Realtek High Definition Audio are typically indications of using jack based or laptop speakers as the mic. In the picture below you see that CloudWay are mostly using certified devices👍

The report is based on Audio Call Count and Second Capture Dev from the CQD database

Requirements

  • Download the CQD PowerBI reports created by Microsoft
    • Read more about them and get the reports here
  • Download and install PowerBI desktop
  • Download the GetDeviceReport from GitHub
  • Assign PowerBI PRO license, to be able to publish the report online
  • Assign at least Teams Communications Support Specialist admin role to be able to connect and read the CQD database

Setting up the report, publish it to PowerBI and adding it as a tab in Teams

  • Make sure you have or create the folder
    • [Documents]\Power BI Desktop\Custom Connectors
  • Copy the MicrosoftCallQuality.pqx to the above folder
    • This file is in the zip package you downloaded called CQD-Power-BI-query-templates.zip
    • This gives you the Microsoft Call Quality (Beta) connector in the PowerBI desktop app
  • To build a report and run queries, you will first need to connect to the CQD data source.
  • Follow the steps below to connect:
    • In the Home tab of Power BI Desktop, click on Get Data.
    • The Get Data window should appear at this point. Navigate to Online Services, then select Microsoft Call Quality (Beta) and hit Connect.
    • You will be prompted to log in
    • The next prompt will give you the option between two Data Connectivity modes.
      • Select DirectQuery, which is the only supported way to connect to CQD, and hit OK
    • Finally, you will be given a final prompt showing you the entire data model for CQD.
      • No data will be visible at this point, only the data model for CQD.
      • Select Load to complete the setup process.
    • At this point, Power BI will load the data model onto the right side of the window.
      • The page will remain otherwise blank, and no queries will be loaded by default.
      • Proceed to Building Queries below to build a query and return data.
    • Now you are ready to open the PowerBI report you downloaded from GitHub
      • Open GetDevice.pbix, see it load in PowerBI desktop
      • Next, we need to publish the report by clicking Publish in the top menu to the left
      • In the Publish to PowerBI window, you should see the Teams you are an owner of as workspaces
      • Choose one and click Select
    • When it is finished publishing you can go the Team, find a channel and add PowerBI report as a tab
      • In the workspace, you have published to, find the report and click Save
    • You have now imported one of the CQD PowerBI reports
    • Everyone that wants to see the report in Teams needs to have one of the admin roles mentioned previously
    • The report may take some time to load and this is because it is getting the data in real time. There is no supported way to pre-load the data at the moment

Need more Microsoft Teams insights like this? Check out the monthly updated book Office 365 for IT Pros where I write the calling and meetings chapter.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.