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 Update via Sql
 
  1. run SQL UPDATE numplan SET dnorpattern = REPLACE(dnorpattern, '61390974', '61385387') WHERE dnorpattern LIKE '%61390974%' AND tkpatternusage = 2


CUCM Line Description Update via Sql
 
  1. run SQL UPDATE numplan SET description = REPLACE(description, '8304', '8307') WHERE description LIKE '%8304%'


CUCM Line Text Label Update via Sql
 
  1. run SQL UPDATE devicenumplanmap SET label = REPLACE(label, '8304', '8307') WHERE label LIKE '8304%'


CUCM Device Name Update via Sql
 
  1. 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
 
  1. 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.

Full Code
 
  1. __author__ = "Mitch Dawson"
  2. __contact__ = "info@ucdevops.com"
  3. __copyright__ = "Copyright (C) 2017 - 2021 Ucdevops All Rights Reserved"
  4. __license__ = "GPLv3"
  5. """
  6. This program is free software: you can redistribute it and/or modify
  7. it under the terms of the GNU General Public License as published by
  8. the Free Software Foundation, either version 3 of the License, or
  9. (at your option) any later version.
  10. This program is distributed in the hope that it will be useful,
  11. but WITHOUT ANY WARRANTY; without even the implied warranty of
  12. MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13. GNU General Public License for more details.
  14. You should have received a copy of the GNU General Public License
  15. along with this program. If not, see http://www.gnu.org/licenses.
  16. """
  17. import requests
  18. import json
  19. import re
  20. # Json Headers
  21. headers = {
  22.     'Accept': 'application/json',
  23.     'Content-type': 'application/json'
  24. }
  25. # Unity Connection API Credentials.
  26. u = 'user'
  27. p = 'password'
  28. # Define Unity Connection host
  29. base_url = 'https://UcxnHost'
  30. # Define Unity Connection User Query URI
  31. get_user_url = '/vmrest/users?query=(alias is {0})'
  32. # DDI Regex Match.
  33. ddi_regex = r'^\+61390974(\d{3})
  34. # Short Code Regex Match.
  35. sd_regex = r'^(830)(\d{1})(\d{3})
  36. # DDI Replacement Value.
  37. ddi_replace = '+61385387'
  38. # Short Code Replacement Value
  39. sd_replace = '7'
  40. # Define the Requests Session Constructor Function.
  41. def requests_session():
  42.     s = requests.session()
  43.     s.auth = (u, p)
  44.     s.headers = headers
  45.     return s
  46. # Create Requests Session Object.
  47. s = requests_session()
  48. def open_input_users():
  49.     # Returns a list of userid aliases.
  50.     return open('users.txt', 'r').read().split('\n')
  51. def get_user_data_by_alias(user):
  52.     # This function Obtains an initial user object by querying an "alias" e.g "Mitch.Dawson"
  53.     # The initial user object is interogated to obtain the API URL for a full user data object.
  54.     # Build URL
  55.     url = base_url + get_user_url.format(user)
  56.     # Make Api Request.
  57.     r = s.get(url)
  58.     # Convert Json response to Python Dict.
  59.     j = r.json()
  60.     # Extract the "URI" attribute from the User Object, this is a path to query the full user data object.
  61.     user_data = j['User']['URI']
  62.     # Build a url with the new path.
  63.     url = base_url + user_data
  64.     # Make Api Request.
  65.     r = s.get(url)
  66.     # Convert Json response to Python Dict and return.
  67.     return r.json()
  68. def get_alternate_extension_object(alternate_extensions_uri):
  69.     # Function returns a list of Alternate Extension Objects.
  70.     url = base_url + alternate_extensions_uri
  71.     r = s.get(url)
  72.     return r.json()
  73. def change_alternate_extension(ae_uri, new_number):
  74.     # Function posts the new number to the unique
  75.     # alternate extension url "DtmfAccessId" field.
  76.     # Build the new url
  77.     url = base_url + ae_uri
  78.     # Create json data object from dictionary values.
  79.     update = json.dumps({'DtmfAccessId': new_number})
  80.     # Make a "put" request to the API, providing the json data.
  81.     r = s.put(url, data=update)
  82.     # Check for the return status code. "204" is successful.
  83.     if r.status_code == 204:
  84.         print('Successfully Changed to new Number "{}"'.format(new_number))
  85.     else:
  86.         print('Error Changing to new Number "{}"'.format(new_number))
  87. def build():
  88.     # Open the list of users.
  89.     users = open_input_users()
  90.     for user in users:
  91.         if not str(user).startswith('#'):
  92.             # Get User Object by Alias.
  93.             user_data = get_user_data_by_alias(user)
  94.             # Extract the Alternate Extension Uri.
  95.             alternate_extensions_uri = user_data['AlternateExtensionsURI']
  96.             # Gets a list of dictionaries containing all Alternate Extension Objects.
  97.             alternate_extension_object = get_alternate_extension_object(
  98.                 alternate_extensions_uri)['AlternateExtension']
  99.             # Iterate Through return alternate extension objects list.
  100.             for ae in alternate_extension_object:
  101.                 # Get the alternate extension number.
  102.                 dtmf_access_id = str(ae['DtmfAccessId'])
  103.                 # Test for E164 DDI Regex Match.
  104.                 match = re.match(ddi_regex, dtmf_access_id)
  105.                 if match:
  106.                     # Extract the individual URL for this alternate extension object.
  107.                     uri = ae['URI']
  108.                     # Build the new number.
  109.                     number = ddi_replace + str(match.group(1))
  110.                     # Call the Change Alternate Extension function.
  111.                     change_alternate_extension(uri, number)
  112.                 else:
  113.                     # Test for Short Dial Regex Match
  114.                     match = re.match(sd_regex, dtmf_access_id)
  115.                     if match:
  116.                         # Extract the individual URL for this alternate extension object.
  117.                         uri = ae['URI']
  118.                         # Build the new number.
  119.                         number = str(match.group(1)) \
  120.                             + sd_replace + str(match.group(3))
  121.                         # Call the Change Alternate Extension function.
  122.                         change_alternate_extension(uri, number)
  123.                     else:
  124.                         pass
  125. build()
Mitch

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 ucdevops.com 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

"