Project Description

As discussed in this blog post, we had to solve the problem of how to track and issue telephone numbers in a Skype for Business environment.

So I created a set of PowerShell scripts that can be scheduled to be run regularly on a server to export data from AD, Skype and Office 365 and then a spreadsheet that can automatically refresh data from those exported files so that administrators have a clear view of what numbers are issued and if there might be any issues. Below is a copy of that solution although there may be better solutions.

Telephone Number Allocation Spreadsheet

Click here to download Telephone Number Allocation spreadsheet.

You will need to update Allocation List tab with your businesses extensions and direct dials. There are some hidden columns that need to be updated as needed and those are used to generate the Active Directory and Skype columns that can be used to copy and paste into those systems when issuing new numbers or correcting errors.

All of the other tabs are updated from CSV files generated by the below PowerShell scripts. Skype Geomant and Office365 Unified have formulas in them too. Geomant you can ignore if you don’t use that third party call centre software for Skype.

You will also need to update Connections on the Data Ribbon so that when you click Refresh Connections, it pulls data from the network location your saving the CSV files too with the PowerShell scripts.

Once all of this is done and you have updated the spreadsheet and filled out the formulas for as many rows as you need, it should work and be useful to managing and checking number allocation.

PowerShell Commands

The following PowerShell scripts run as a service account that not only has rights to access the AD/Skype/Office365 data but also the network location that you will be saving the data too. The Active Directory scripts are easiest to schedule on a Domain Controller if possible. The Skype scripts can be scheduled on the Skype server. Office 365 could be scheduled on another server with all the various tools you need for the MS Online PowerShell stuff to work. You could alternately have a dedicated server for all of these things. Depends on the organisation size and security requirements.

You will need to save the below in .ps1 files and either run them manually or schedule them as needed. You will also need to edit the export path to match the connections used in the spreadsheet.

Commands to Export Active Directory Data

Import-Module ActiveDirectory
Get-ADUser -Filter * -Properties OfficePhone | select SamAccountName, OfficePhone, Enabled | Export-CSV "\\Server\Share\Skype\Exports\AD-Users.csv"
Import-Module ActiveDirectory
 Get-ADObject -LDAPFilter "objectClass=Contact" -properties telephoneNumber | select Name, telephoneNumber | Export-CSV "\\Server\Share\Skype\Exports\AD-Contacts.csv"

Commands to Export Skype for Business Data

Get-CsUser | Select-Object DisplayName, SamAccountName, VoicePolicy, SipAddress, EnterpriseVoiceEnabled, LineUri, HostedVoiceMail | Export-Csv -Path "\\Server\Share\Skype\Exports\Skype-Users.csv"
Get-CsCommonAreaPhone | Select-Object DisplayName, SamAccountName, VoicePlocy, SipAddress, EnterpriseVoiceEnabled, LineUri | Export-Csv -Path "\\Server\Share\Skype\Exports\Skype-Common.csv"
Get-CsTrustedApplicationEndpoint | Select-Object DisplayName, SipAddress, EnterpriseVoiceEnabled, LineUri | Export-Csv -Path "\\Server\Share\Skype\Exports\Skype-Geomant.csv"

Commands to export Office 365 Data (Unified Messaging)

The below will need to be ran after you have connected to Office 365 / MS Online PowerShell.

Get-Recipient -ResultSize Unlimited | Select Name, SAMAccountName, UMEnabled, UMMailboxPolicy, UMRecipientDialPlanId, EmailAddresses | Export-Csv -Path "\\Server\Share\Skype\Exports\Office365-UM.csv"