Skype for Business DDI Number Management using SharePoint Online [BETA]

This year I set myself a little project to see if I could use some of the tools and platforms provided in Office 365 to create something mildly useful. I wanted to start with something basic and achievable without having to spend months and months of trial and error experiments and thought that Phone Number management could be that starter project.

I will preface this blog by stating that there are already various number management solutions out there, some paid and some free and this hasn’t been created to compete with them. It is a project that enables me to learn and develop new skills but also has some use cases that may benefit you, hence the reason for sharing.

Often when I speak with customers and ask them about their number management solution, they invariably say Excel. They’d like to move towards a more suitable product but those offering these solutions are sometimes out of reach of the budget available. Using a basic Excel sheet has it’s own problems, but mainly keeping the thing up to date with all adds, moves and changes. So I thought there must be a way to leverage what is available in just an E1 Office 365 licence to create a middle ground. Something in between Excel and the paid apps must surely be possible?

So I looked at Lists in SharePoint Online. This seemed the logical choice in the Office 365 product suite to use a my “database” as it where. Out of the box it had a lot of built in features that meant I could save time by not having to create user interfaces, search filters and different views. It also acts like Excel so that you can easily update multiple records in-line and provide a single pane of glass experience without having to install any software on to a bunch of admin workstations. However, a SharePoint List on it’s own is probably no better than that Excel sheet stored on a file share somewhere. It needed a way in which admins can interact with it and easily use in day to day tasks. More importantly it needed a way to talk to Skype for Business to ensure that the list kept was the single, undisputed source of truth.

What it needed was a PowerShell Module to bridge the gap between SharePoint Online and Skype for Business. I then found that the SharePoint Online Management Shell allowed management of tasks, but offered no way to interact or manipulate the data held within SharePoint. I quickly learned that in order for me to manipulate data I needed to use the Client Side Object Model (CSOM) for SharePoint Online.

Enter first problem. I know nothing about CSOM. Worse still I seem to have a mental block in understanding how to code in .Net or C#, but I can do some basic PowerShell. I am glad to say that this was quickly resolved with the thanks to Arleta Wanat (a SharePoint MVP) who had already create her own PowerShell module for manipulating data within SharePoint Online. A great set of commandlets that everyone should have in their back pocket. You can download the module here: https://gallery.technet.microsoft.com/office/SharePoint-Module-for-5ecbbcf0

I thought all my birthdays had come at once with this module, until I found some limitations to some of the functions I was using. Mainly these where down to the size of the data being extracted from SharePoint Online in order to return my custom list fields, and the dreaded 5,000 item view limit of SharePoint Online!

So I had to customise it slightly to allow me to continue as it worked really well for 5,000 phone numbers but failed miserably with 5,001! Having overcome this, the theoretical maximum this can handle if somewhere in the region of 50,000,000 (Yes, 50 million) phone numbers.

Demo

Key Features

I am pretty sure right now, you don’t want a life story of development, but rather want to know what does this thing do, right?

Skype for Business On-Prem and Cloud PBX Support

Firstly, this works for Skype for Business On-Prem and Skype for Business Online (Cloud PBX) so you’re covered in all three states (On-Prem, Hybrid and Cloud Only).

Synchronizes Phone Numbers

Whether you run Skype for Business Server or Cloud PBX or Both you can synchronize numbers from these systems into the Phone Inventory List. All Cloud PBX numbers will be synchronized whether they are subscriber or service numbers. If they are assigned to a service or user, then this information is passed back to the list so that it immediately understands the allocation landscape. The same for On-Prem, synchronization happens and retrieves all used numbers within the ecosystem for users, conference, RGS, Trusted Applications etc etc. Again passing back their assignments.

Integration with Numverify

For On-Prem numbers it can be hard sometimes to gather a list of cities, countries and even carriers each phone number relates to. If you don’t have this information to hand, then the task of finding this out can be arduous. Therefore, there is integration with the numverify api (https://numverify.com/)  which will search for this information and retrieve it automatically when you synchronize or import your numbers. The API is free to use (up to a max of 250 calls per month) and requires you to sign up for an account for your own personal API key.

PowerShell Module

The PowerShell module is the beating heart of all this. There are several commandlets that give admins the power to allocate numbers to users individually or in bulk as well as allow you to reserve a number or a block of numbers so that they cannot be used unless specifically chosen. This is useful for planning projects that require a bank of numbers to be allocated but not yet assigned in Skype for Business. There are also commandlets that allow you to import numbers from a CSV, or just by stating the start number and how many numbers in sequence you want to add.

PowerBI

If you want to produce graphs on consumed numbers per area, per usage, per site etc. This can all be done in PowerBI with a live data connection to the SharePoint list. PowerBI free is sufficient, but you will need the PowerBI desktop app to create the data connection.

Mobile support using PowerApps

Want to be able to quickly reserve or assign a number to a user? Maybe add one that you forgot whilst on the train home? No problem, easily create a mobile app using PowerApps and you can manage your DDIs wherever you are.

Requirements

  • An active Office 365 E1 licence subscription assigned to each admin account
  • Each admin must have at least contribute permission on the SharePoint list
  • Each admin must have Skype for Business Online Admin permission if you are using Cloud PBX
  • Each admin must have at least Voice Administrator permission on Skype for Business On-Prem
  • The SharePoint Online Client Side Components must be installed on the machine the PowerShell module is going to be run on
  • Skype for Business Management components required on the machine the module is going to be run on
  • Skype for Business Online Management Shell installed on the machine the module is going to be run on
  • SharePoint Site Collection and Site (not required to be dedicated)

PowerShell Commands

Connect-MyPhoneInventory

You must use this command at the beginning of each session to establish a connected session to SharePoint Online, Cloud PBX and Skype for Business Server. The command accepts four mandatory parameters. When connection has been established, a full copy of the SharePoint List will be downloaded into memory to enable faster access to the subsequent commands used in the session. Please be aware that as the list grows, the longer it will take to download the list. Estimate performance to be around one minute per 5,000 numbers.

Parameters

-Username [string]

-Password [string]

-Url [string]

-ConnectOnline [bool]

Usage

Connect-MyPhoneInventory –Username mark@myfluffy.cloud –Password <mypassword> –Url https://myfluffy.sharepoint.com/sites/skype –ConnectOnline $true | $false

Sync-Cache

This command you can use to update your local cache based on the data held on SharePoint. This is useful in case the local cache becomes inconsistent, or something happens where the cache is lost. This command accepts no parameters.

Usage

Sync-Cache

Get-Cache

This command you can use to bring the cache out of the private data area into a custom variable you can use for other commandlets not included in this module. This commandlet accepts no parameters

Usage

$Data = Get-Cache

Get-MyNextNumber

This command gets the next available number(s) from the pool in which you specify. When executed, this command will put a “soft” reserve on the numbers returned. This allows you to assign numbers to bulk users in your scripts without having to synchronize with SharePoint after each one. Please be aware that after you have finished using this command you should perform a Sync-Cache to return an accurate copy of the list if no changes have been made in Skype for Business / Cloud PBX. If there have been, a full Skype for SharePoint synchronization is required.

Parameters

-Area [string][optional] – The Area / City to get the next number from

-Carrier [string][optional] – The carrier from which to get the next number from

-NumberType – [string][optional] [valid types] – Subscriber, Conference, RGS, Service, Private

-Block – [num][optional] – The number of free numbers to return in the result

Usage

Get the next 10 numbers available in the town called Crewe where the Carrier is BT and the Number is a Subscriber.

Get-MyNextNumber -Area Crewe -Carrier BT -NumberType Subscriber -Block 10

Get-MyNextNumber on it’s own returns the next available number in the list based on ID

Get-MyAvailableNumber

This command returns all the available numbers in the pool in which you specify.

Parameters

-Area [string][optional] – The Area / City to get the next number from

-Carrier [string][optional] – The carrier from which to get the next number from

-NumberType – [string][optional] [valid types] – Subscriber, Conference, RGS, Service, Private

Usage

Get-MyAvailableNumber -Area Crewe -Carrier BT -NumberType Conference | Format-Table

Count-MyAvailableNumber

This command returns a count of all the available numbers in the pool in which you specify.

Parameters

-Area [string][optional] – The Area / City to get the next number from

-Carrier [string][optional] – The carrier from which to get the next number from

-NumberType – [string][optional] [valid types] – Subscriber, Conference, RGS, Service, Private

Usage

Count-MyAvailableNumber -Area Crewe -Carrier BT -NumberType Conference

Reserve-MyNumberRange

This command allows you to reserve numbers in ranges from 1 to unlimited, or by number type and how many based on area.

Parameters

-StartNumber [number] must be in E164

-EndNumber [number] must be in E164

-Area [string][optional] – The Area / City to get the next number from

-NumberType – [string][optional] [valid types] – Subscriber, Conference, RGS, Service, Private

Usage

Reserve-MyNumberRange -StartNumber +441270212000 -EndNumber +4412702121000

Or

Reserve-MyNumberRange -Area Crewe -NumberType Subscriber -BlockTotal 500

Count-MyReservedNumber

This command allows you to count how many numbers are reserved but not assigned yet in the pool in which you specify. This is useful for identifying number that require  scavenging / recycling back into the available pool.

Parameters

-Area [string][optional] – The Area / City to get the next number from

-Carrier [string][optional] – The carrier from which to get the next number from

-NumberType – [string][optional] [valid types] – Subscriber, Conference, RGS, Service, Private

Usage

Count-MyReservedNumber -Carrier BT

Get-MyReservedNumber

This command outputs all the reserved numbers in your chosen selection

Parameters

-Area [string][optional] – The Area / City to get the next number from

-Carrier [string][optional] – The carrier from which to get the next number from

-NumberType – [string][optional] [valid types] – Subscriber, Conference, RGS, Service, Private

Usage

Get-MyReservedNumber -Area Crewe | Format-Table

Get-MyNextReservedNumber

This command returns the next unallocated reserved number in your selection

Parameters

-Area [string][optional] – The Area / City to get the next number from

-Carrier [string][optional] – The carrier from which to get the next number from

-NumberType – [string][optional] [valid types] – Subscriber, Conference, RGS, Service, Private

-Block [int] – the number of numbers to return

Usage

Get-MyNextReservedNumber -Area Crewe -Carrier BT -NumberType Service -Block 10

Release-MyReservedNumber

This command is used to release reserved numbers that will not be allocated to a user or service back into the available pool.

Parameters

-StartNumber [number] – Must be in E164 format

-EndNumber [number] – Must be in E164 format

-Area [string][optional] – The Area / City to get the next number from

-Carrier [string][optional] – The carrier from which to get the next number from

-NumberType – [string][optional] [valid types] – Subscriber, Conference, RGS, Service, Private

-BlockTotal [int] How many numbers to release

-Site [string] – The site the numbers belong to

Usage

Release-MyReservedNumber -StartNumber +441270212000 -EndNumber +441270212500

Or

Release-MyReservedNumber -Area Crewe -NumberType Subscriber -BlockTotal 500

Or

Release-MyReservedNumber -Site ManchesterOffice -NumberType Service -BlockTotal 100

Count-MyUsedNumber

This command returns a count of all used numbers within the pool chosen

Parameters

-Area [string][optional] – The Area / City to get the next number from

-Carrier [string][optional] – The carrier from which to get the next number from

-NumberType – [string][optional] [valid types] – Subscriber, Conference, RGS, Service, Private

Usage

Count-MyUsedNumber -Area Crewe -Carrier BT -NumberType Service

Get-MyUsedNumber

This command returns a list of all used numbers within the pool chosen

Parameters

-Area [string][optional] – The Area / City to get the next number from

-Carrier [string][optional] – The carrier from which to get the next number from

-NumberType – [string][optional] [valid types] – Subscriber, Conference, RGS, Service, Private

Usage

Get-MyUsedNumber -Area Crewe -Carrier BT -NumberType Service | Format-Table

Sync-MyNumbersFromSkype

This command gathers all the allocated numbers within Skype for Business Server and uploads them to the SharePoint List. This command also updates the assignments to users and other services that may use numbers. It is recommended that you create a schedule task to run this command at least once per day during quiet times to ensure that the list remains current and up to date.

Parameters

-UserNumbers [bool] [optional] $true | $false

-ConferenceNumbers [bool] [optional] $true | $false

-PrivateNumbers [bool] [optional] $true | $false

-RgsNumbers [bool] [optional] $true | $false

-ServiceNumbers [bool] [optional] $true | $false

Usage

Sync-MyNumbersFromSkype

Use parameters only if you want to synchronize a sub portion of numbers based on type.

Import-MyNumberBlock

This command allows you to add new numbers to the SharePoint List from a CSV File

Parameters

-Path [string] – Path to CSV file

Usage

Import-MyNumberBlock -Path C:\Numbers\MyNumbers.csv

Please note that columns in the CSV should be named as follows:

  • Number – The full phone number in E164 format
  • Exten – The extension number
  • Carrier – The name of the Carrier
  • Type – The Number Type i.e. Subscriber, Service, Conference, Rgs, Private
  • Country – The country of origin
  • Area – The city / town or area
  • Reserved – Yes or No
  • Site – Site where the number terminates
  • User – The SIP address of the user assigned to the number – must match sip:username@domain.com
  • Residency – The system type, e.g. On-Prem or Online

Export-MyNumberBlock

This command allows you to export the data from the SharePoint list to a CSV. The default behaviour is to export from the current data held in memory.

Parameters

-ExportPath [string] [mandatory] – The path where the file will be saved

-FromSharePoint [bool][default $false] [optional] $true

Usage

Export-MyNumberBlock -ExportPath C:\Numbers\MyNumbers.csv

Or

Export-MyNumberBlock -ExportPath C:\Numbers\MyNumbers.csv -FromSharePoint $true

Add-MyNewNumberBlock

This command allows you to add sequential number blocks to SharePoint without a CSV. If you don’t know the area, country or carrier of the numbers, then you can use numverify to retrieve these.

Parameters

-StartNumber [number] [mandatory] – Must be in E164 format

-BlockTotal [int] [mandatory] – number of sequential numbers to add

-NumberType [optional][valid options] Subscriber,Conference, RGS, Service, Private

-Area [string][optional] – The city town or area of the number’s residence

-Country [string][optional] – The country the number belongs to

-Carrier [string][optional] – The name of the carrier

-ExtensionLength [int][optional][default = 4] – The extension length (taken from last X of DDI. Default is 4 if none specified)

-Site [string][optional] – The name of your site where the number terminates

-Reserved [bool][optional] – Should the number be immediately reserved. Values $true | $false

-Residency [string][mandatory][valid options] – On-Prem,Online

Usage

Add-MyNewNumberBlock -StartNumber +441270212000 -BlockTotal 2000 -NumberType Subscriber -Area Crewe -Country "United Kingdom" -Carrier BT -ExtensionLength 5 -Site CreweOffice -Reserved $False -Residency On-Prem

Or with numverify enabled

Add-MyNewNumberBlock -StartNumber +441270212000 -BlockTotal 2000 -NumberType Subscriber -ExtensionLength 5 -Site CreweOffice -Reserved $False -Residency On-Prem

If no results found, or numverify not enabled, then “unknown” will be entered in missed fields.

Remove-MyNumberBlock

This command removes numbers that are not allocated to users or services from the list

Parameters

-StartNumber [number] [mandatory] – Must be in E164 format

-BlockTotal [int] [mandatory] – number of sequential numbers to remove

Usage

Remove-MyNumberBlock - StartNumber +441270212000 -BlockTotal 1000

Show-MyNumberSummary

This command produces a summary of the current number allocation

Parameters

-Area [string][optional] – The Area / City to get the next number from

-Carrier [string][optional] – The carrier from which to get the next number from

-NumberType – [string][optional] [valid types] – Subscriber, Conference, RGS, Service, Private

Usage

Show-MyNumberSummary -Area Crewe

Or

Show-MyNumberSummary -Carrier BT

Or

Show-MyNumberSummary -NumberType Subscriber -Area Crewe -Carrier BT

Or

Show-MyNumberSummary

Sync-MyOnlineNumber

This command synchronizes Cloud PBX numbers to the list and updates assignments. It is recommended to create a scheduled task to run this command at least once per day to ensure the list is kept up to date. This command accepts no parameters

Usage

Sync-MyOnlineNumber

Get-MyOnlineNumberArea

This command is used to retrieve the Cloud PBX area allocation code which can be used to find available and reserve numbers. This command accepts no parameters

Usage

Get-MyOnlineNumberArea

Get-MyOnlineNextNumber

This command return the next available number(s) from Cloud PBX that have been acquired by your tenant. Please note that this command places a “soft” reserve on returned numbers so it an be used in a script. Please ensure that you run Sync-MyOnlineNumber command once finished to update the cache.

Parameters

-NumberType [string][mandatory][valid options] Subscriber, Service

-Area [string] – output of Get-MyOnlineNumberArea

-Block [int][optional] – Number of numbers to return

Usage

Get-MyOnlineNextNumber -NumberType Subscriber -Area EMEA-UK-ALL-ENG_CR -Block 10

Reserve-MyOnlineNumber

This command reserves Cloud PBX numbers in the list so they cannot be allocated.

Parameters

-NumberType [string][mandatory][valid options] Subscriber, Service

-Area [string] – output of Get-MyOnlineNumberArea

-Block [int][optional] – Number of numbers to return

Usage

Reserve-MyOnlineNumber -Area EMEA-UK-ALL-ENG_CR -NumberType Subscriber -Block 5

Release-MyOnlineReservedNumber

This command releases any reserved but not allocated numbers in the list that are Cloud PBX numbers.

Parameters

-NumberType [string][mandatory][valid options] Subscriber, Service

-Area [string] – output of Get-MyOnlineNumberArea

-Block [int][optional] – Number of numbers to return

Usage

Release-MyOnlineReservedNumber -Area EMEA-UK-ALL-ENG_CR -NumberType Subscriber -Block 10

Get-MyOnlineReservedNumber

This command return a list of all reserved numbers in the selection chosen

Parameters

-NumberType [string][mandatory][valid options] Subscriber, Service

-Area [string] – output of Get-MyOnlineNumberArea

-Block [int][optional] – Number of numbers to return

-All [bool][optional] – Set to $true if you want to return all reserved numbers regardless of type or area

Usage

Get-MyOnlineReservedNumber -All $true

Or

Get-MyOnlineReservedNumber -Area EMEA-UK-ENG_CR -NumberType Subscriber

Count-MyOnlineReservedNumber

This command returns a count of all numbers reserved in Cloud PBX

Parameters

-Area [string] – output of Get-MyOnlineNumberArea

-Block [int][optional] – Number of numbers to return

-All [bool][optional] – Set to $true if you want to return all reserved numbers regardless of type or area

Usage

Count-MyOnlineReservedNumber -All $true

Or

Count-MyOnlineReservedNumber -Area EMEA-UK-ENG_CR -NumberType Subscriber

Count-MyOnlineAvailableNumber

This command returns a count of all numbers available in Cloud PBX

Parameters

-Area [string] – output of Get-MyOnlineNumberArea

-Block [int][optional] – Number of numbers to return

-All [bool][optional] – Set to $true if you want to return all reserved numbers regardless of type or area

Usage

Count-MyOnlineAvailableNumber -All $true

Or

Count-MyOnlineAvailableNumber -Area EMEA-UK-ENG_CR -NumberType Subscriber

Count-MyOnlineUsedNumber

This command returns a count of all numbers in use in Cloud PBX

Parameters

-Area [string] – output of Get-MyOnlineNumberArea

-Block [int][optional] – Number of numbers to return

-All [bool][optional] – Set to $true if you want to return all reserved numbers regardless of type or area

Usage

Count-MyOnlineUsedNumber -All $true

Or

Count-MyOnlineUsedNumber -Area EMEA-UK-ENG_CR -NumberType Subscriber

Get-MyOnlineAvailableNumber

This command returns a list of all available numbers in Cloud PBX

Parameters

-Area [string] – output of Get-MyOnlineNumberArea

-Block [int][optional] – Number of numbers to return

-All [bool][optional] – Set to $true if you want to return all reserved numbers regardless of type or area

Usage

Count-MyOnlineAvailableNumber -All $true

Or

Count-MyOnlineAvailableNumber -Area EMEA-UK-ENG_CR -NumberType Subscriber

Installation

  • Install the sharepoint components in the Components Directory
  • Create a folder in your My Documents called WindowsPowerShell
  • Create a sub folder within this directory called Modules
  • Copy the PhoneInventory Folder and place it inside the Module folder you created above
  • In SharePoint Online click on Site Contents > Site Settings
    image
  • Click on List Templates in the Web Designer Galleries
    image
  • Click on the Files tab and click Upload Document
    image
  • Browse to the SharePoint Online folder and select the PhoneInventory.stp file and press OK
    image
  • Now go to your SharePoint Home Page and select Add lists, libraries and other apps
    image
  • Find the app called PhoneInventory and click on it
    image
  • You must call it PhoneInventory. No other name will work! Press Create
    image
  • You should now be able to browse to your list
    image

Enabling numverify Integration

To enable numverify integration, go to C:\users\<your name>\documents\windowspowershell\modules\phoneinventory and open PhoneInventory.psm1. Around Line 13 you will see a variable $numverify that is commented out. Uncomment this variable and replace <your-key> with the API key given to you by numverify. Save the file and reload any open PowerShell windows.

image

Download

To download PhoneInventory please click here

Public BETA

This is currently in BETA. There may be bugs or features missing. If you come across any, please use the comment section below and I will work with you to get a stable release in the coming weeks / months.

Advertisements

5 comments

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s