Automating Teams voice reporting of users (2:2)

This is a follow up on my last blog post covering automated teams voice assignment for users. This time I will cover how the mentioned routine has been expanded to do reporting in PowerBI to show evolution and distribution throughout the lifetime of the service.

After running my routine of automated voice assignment in Teams for a while, I felt the need to have an overview of the solution and how it evolved.

I used some experience from the project of reporting license usage for Microsoft 365 to expand the Teams routine. Lets dive into some details.

Cosmos DB

An Azure Cosmos Database with a new container was created to hold the data about voice enabled Teams users. The necessary information for uploading data to the Cosmos DB was added as encrypted variables in the automation account holding the runbook. The Key used must have write access to the CosmosDB in order to upload data.

CosmosDB is then added as a Module in the Automation Account in order to get support to upload data in PowerShell.

PowerShell

The PowerShell script in the runbook are now expanded with some extra code to connect to the Cosmos DB, collect data and upload these to the DB.

Initially the secrets are imported and a connection is made.

# Get the variables from Automation

$cosmosdbAccount = Get-AutomationVariable -Name 'cosmosdbAccount'

$cosmosdbCollectionId = Get-AutomationVariable -Name 'cosmosdbCollectionId'

$cosmosdbDatabase = Get-AutomationVariable -Name 'cosmosdbDatabase'

$cosmosdbSecondaryKey = Get-AutomationVariable -Name 'cosmosdbSecondaryKey'

# Connect to Microsoft Teams  

Connect-MicrosoftTeams -Credential $psCredential  

Connect-AzureAd -Credential $psCredential  

$secondaryKey = ConvertTo-SecureString -String $cosmosdbSecondaryKey -AsPlainText -Force

$cosmosDbContext = New-CosmosDbContext -Account $cosmosdbAccount -Database $cosmosdbDatabase -Key $secondaryKey

$cosmosDbCollectionId = $cosmosdbCollectionId

$Date = Get-Date -UFormat "%d.%m.%Y %R"

All data that is needed for the PowerBI statistics is gathered in the previous script at the point where the Azure security groups containing users are iterated. After adding the user information to the hash table as described in the previous post, I will now also add the same data about the users to the Cosmos DB. The data will be converted to JSON format before they are written to the DB. The Foreach loop handling the group iteration gets the following code at the end of each user routine:

        # Write userdata to CosmosDB for statistics

        $id = $([Guid]::NewGuid().ToString())

        $doc = [pscustomobject]@{

            id          = $id

            UPN         = $UserPrincipalName.ToLower()

            DisplayName = $DisplayName

            Date        = $Date

            Country     = $Country

        }

        $document = $doc | ConvertTo-json | Out-String

        # Sending data to Cosmos DB

        New-CosmosDbDocument -Context $cosmosDbContext -CollectionId $cosmosDbCollectionId -DocumentBody $document -PartitionKey $id -Encoding 'UTF-8'

The script is available from my GitHub repository.

Power BI

Using Power BI Desktop, it is pretty easy to connect to the Azure Cosmos DB. This time I will use a key from the Cosmos DB with read only rights since Power BI will not update anything in the database. 

I can now build a report showing the development of users over time:

The report can also show on a map where users are located:

By adding a table of usernames and a search functionality, it is easy to find persons. This table will also be filtered by clicking on the graphical elements. In this way we can also add filters for operators - this is done by adding logos in a Chiclet Slicer. When clicking on the operator logo, the data shown in the report will be filtered accordingly. The report has also been equiped with a TimeLine filter where the usage can be filtered to a given period or point in time.

The report can be uploaded from Power BI Desktop to the Power BI service in Microsoft 365. Here I will schedule automatic updates directly from the Cosmos DB. This way the system will be self running related to Teams Voice activation of users and detailed reporting of the users being enabled for calling in Teams. The report can be consumed on all devices by people with access.

I hope this can inspire to get your datasets into PowerBI!