Cisco Cucm & Ucxn Sql Python3 – DDI Migration, Updating Extension’s, Devices, Descriptions and Text Labels

By 14th February 2018CAPI, Cisco, CUCM, UCXN

I recently fulfilled a request for a client to merge two DDI blocks into one which meant that a large number of users would receive a new DDI.
The customer has full E164 extensions  configured in CUCM with 7 digit inter site short dial codes made up of a three digit site code and the last four digits of the extension, the seven digit short code is also used as a unity connection alternate extension for external voicemail access. User device names are derived from the site code and extension as are line text labels and descriptions.

We were able to map the last three  digits of the old number to the new one which meant I didn’t have to perform a one to one mapping.
To support this migration, the following would need to change to reflect the new ddi.

  • CUCM Extension
  • CUCM Line Text Label and Description
  • CUCM UDP Device Name and Description
  • CUCM CSF Device Name and Description
  • CUCM MRA Device Name and Description
  • UCXN Extension and Alternate Extension Value

The approach was a mixture of CUCM SQL “Replace” statements which will replace a given value string with another value of your choosing and UCXN api updates.

In the first example we have changed the portion of the number from “61390974” to “61385387”, all other parts of the extension remain the same.

Changing CUCM extension value via Sql.

run sql UPDATE numplan SET dnorpattern = REPLACE(dnorpattern, '61390974', '61385387') WHERE dnorpattern LIKE '%61390974%' AND tkpatternusage = 2

Changing CUCM line description via Sql

run sql UPDATE numplan SET description = REPLACE(description, '8304', '8307') WHERE description LIKE '%8304%'

Changing CUCM line text label via Sql

run sql UPDATE devicenumplanmap SET label = REPLACE(label, '8304', '8307') where label LIKE '8304%'

Changing CUCM device names via Sql.

run sql UPDATE device SET name = REPLACE(name, '8304', '8307') WHERE name LIKE '%8304%'

In an AD integrated environment where a users “telephoneNumber” attribute reflects their E164 extension, it is easy to obtain a list of affected users in order to update UCXN.

Select CUCM user data via Sql.

run sql SELECT userid, firstname, lastname, telephonenumber from enduser WHERE telephonenumber LIKE '%+6139097%'

The final part was to update the Ucxn extension and alternate extension values via the CUPI API.
Changing Unity Connection Extensions via API using Python.

__author__ = "Mitch Dawson"
__contact__ = ""
__copyright__ = "Copyright (C) 2017 - 2018 Ucdevops All Rights Reserved"
__license__ = "GPLv3"

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program. If not, see

import requests
import json
import re

# Json Headers
headers = {
    'Accept': 'application/json',
    'Content-type': 'application/json'

# Unity Connection API Credentials
u = 'user'
p = 'password'

# Define Unity Connection host
base_url = 'https://UcxnHost'
# Define Unity Connection User Query URI
get_user_url = '/vmrest/users?query=(alias is {0})'

# Regex Match's
# DDI Regex Match
ddi_regex = r'^\+61390974(\d{3})

# Short Code Regex Match
sd_regex = r'^(830)(\d{1})(\d{3})

# DDI Replacement Value
ddi_replace = '+61385387'
# Short Code Replacement Value
sd_replace = '7'

# Define the Requests Session Object
def requests_session():
    s = requests.session()
    s.auth = (u, p)
    s.headers = headers
    return s

# Create Requests Session Object
s = requests_session()

def open_input_users():
    # Returns a list of userid aliases
    return open('users.txt', 'r').read().split('\n')

def get_user_data_by_alias(user):
    # This function Obtains an initial user object by querying an "alias" e.g "Mitch.Dawson"
    # The initial user object is interogated to obtain the API URL for a full user data object.
    # Build URL
    url = base_url + get_user_url.format(user)
    # Make Api Request
    r = s.get(url)
    # Convert Json response to Python Dict
    j = r.json()
    # Extract the "URI" attribute from the User Object, this is a path to query the full user data object.
    user_data = j['User']['URI']
    # Build a url with the new path
    url = base_url + user_data
    # Make Api Request
    r = s.get(url)
    # Convert Json response to Python Dict and return
    return r.json()

def get_alternate_extension_object(alternate_extensions_uri):
    # Function returns a list of Alternate Extension Objects
    url = base_url + alternate_extensions_uri
    r = s.get(url)
    return r.json()

def change_alternate_extension(ae_uri, new_number):
    # Function posts the new number to the unique
    # alternate extension url "DtmfAccessId" field

    # Build the new url
    url = base_url + ae_uri
    # Create json data object from dictionary values
    update = json.dumps({'DtmfAccessId': new_number})
    # Make a "put" request to the API, providing the json data
    r = s.put(url, data=update)
    # Check for the return status code. "204" is successful
    if r.status_code == 204:
        print('Successfully Changed to new Number "{}"'.format(new_number))
        print('Error Changing to new Number "{}"'.format(new_number))

def build():
    # Open the list of users
    users = open_input_users()
    for user in users:
        if not str(user).startswith('#'):
            # Get User Object by Alias
            user_data = get_user_data_by_alias(user)
            # Extract the Alternate Extension Uri
            alternate_extensions_uri = user_data['AlternateExtensionsURI']
            # Gets a list of dictionaries containing all Alternate Extension Objects
            alternate_extension_object = get_alternate_extension_object(
            # Iterate Through return alternate extension objects list
            for ae in alternate_extension_object:
                # Get the alternate extension number
                dtmf_access_id = str(ae['DtmfAccessId'])
                # Test for E164 DDI Regex Match
                match = re.match(ddi_regex, dtmf_access_id)
                if match:
                    # Extract the individual URL for this alternate extension object
                    uri = ae['URI']
                    # Build the new number
                    number = ddi_replace + str(
                    # Call the Change Alternate Extension function
                    change_alternate_extension(uri, number)
                    # Test for Short Dial Regex Match
                    match = re.match(sd_regex, dtmf_access_id)
                    if match:
                        # Extract the individual URL for this alternate extension object
                        uri = ae['URI']
                        # Build the new number
                        number = str( \
                            + sd_replace + str(
                        # Call the Change Alternate Extension function
                        change_alternate_extension(uri, number)



Author Mitch

I am an independent IT Consultant with specialities in Software Development, Enterprise Unified Communication, Network and Security platforms. In addition to my day to day work, I develop bespoke applications and I hope that through I can build relationships with clients, business partners and fellow engineers by providing solutions to complex problems through the use of programming.

More posts by Mitch

Leave a Reply