A project created to manage assigment of PSTN numbers in Microsoft Teams (Direct routing)

This project is created with a dream to ease the management of phone numbers provided to an organization by an external provider (Telecommunications company's like T-Mobil or TDC). Phone numbers are often provided in an Excel spreadsheet and System administrator in charge needs to assign the number “by hand” and remember to update the spreadsheet.

This project aims to ease that pain by having the phone numbers in a database and Power BI report to display insight in to how the numbers are used and then a couple of PowerShell scripts to assign phone numbers and then a PowerShell script to “clean up” the database.

• Microsoft Teams
• SQLServer

• Create an Azure SQL server and SQL database How To
• Create an Azure Automation Account How to
• Create a SQL table using the SQL query - CreateTables.sql found in this reposistory
• Import PSTN numbers from your Telecommunications provider into the SQL table - ImportTemplate.csv using the PowerShell script ImportPSTNNumbersToDB.ps1 *Note
• Create a Service Principal in Entra and assign SQL write permission - How To
• Create a Runbook in the Automation with the PowerShell script DB-Maintenance.ps1
• Create a Runbook in the Automation with the PowerShell script EnableAndAssignPhoneNumber.ps1

Note: You only need to provide the PSTN numbers from your number serie and the country code, you don't need to add the user that is using the number - It can be done automatily by running the DB-Maintenance.ps1

• Start the runbook with PowerShell script EnableAndAssignPhoneNumber.ps1 in the automation account and type in the users UPN ([email protected])
• Create a schedule that runs the DB-Maintenance.ps1 when you deem it best (Recommend to run at least once a day)

The PowerShell script will assign the first available phone number (PSTN) it can find in the DB and assign it to the user and then enable the user to use PSTN calling in Teams

There is a function for reserved numbers. Currently if it only looks at the department attribute. Say a user is in "IT" department then if a number is reserved for "IT" then the user will get the first available that is reserved for IT. otherwise the user will get the first available number.

This PowerShell script will first look at all users in Teams and add them to the database. After that the script will look up all users in the database in Teams. This is to ensure that the numberserie is always up to date.

This PowerShell script will import the csv file PhoneNumberImportTemplate.csv in to the SQL table that has been created using CreateTables.sql

• PSTNnumber int NOT NULL PRIMARY KEY,
• UsedBy varchar(255)
• ReservedFor varchar(255)
• CountryCode int

• Update Active Directory OfficePhone attribute with assigned phone number