Cisco AXL Python 3.4 SQL Query - Changing DN partitions

I recently wrote the following script as part of some tools for a phased migration of users to a new V11 cluster. We were routing pstn calls through the new cluster so could not place extensions of users not yet migrated into live partitions until their phones were cutover and registered on the new platform.

I therefore placed all extensions into a "Holding" partition  not a member of a CSS which enabled us to verify the user and device configuration without impacting the users, however this also created a following problems on cutover.

1) New Cluster - Requirement to move the migrated users extensions from holding to live
2) Old Cluster - Requirement to move the migrated users extensions from live to holding

When migrating hundreds of users, changing the partition indivudually across two clusters is a lot of work, so it was imperitive to try and automate the process - enter Python!

I previously obtained the pkid's of the relevent partitions with sql querys similar to the following:

1:  select pkid from routepartition where name = "PAR_HOLDING" 

We build the xml message with the following sql query:

1:  select dnorpattern from numplan where fkroutepartition ='72f20467-ddf1-52fb-5586-682a9ef3ea91'  

1:  msg = """  
2:      <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/11.0">  
3:        <soapenv:Header/>  
4:        <soapenv:Body>  
5:         <ns:executeSQLQuery sequence="?">  
6:          <sql>select dnorpattern from numplan where fkroutepartition ='72f20467-ddf1-52fb-5586-682a9ef3ea91'  
7:          </sql>  
8:         </ns:executeSQLQuery>  
9:        </soapenv:Body>  
10:      </soapenv:Envelope>"""  

Post to the api

1:  # Create the Requests Connection  
2:    post = requests.post(url11, data=msg, headers=headers11query, verify=False, auth=('user', 'password'))  

We then parse and iterate over the returned xml string to obtain all dnorpattern instances by looking for xml tags which match "dnorpattern" and returning their text value. In this case the number length of the two clusters are different i.e. E164 on the new and four digits on the old. We create two variables, one "newdn" which holds the full E164 value and "olddn" which takes the last four digits, before passing the variables to the relevent functions which actually do the work of changing the partition.

1:  # Parse the response string  
2:    response = ElementTree.fromstring(post.content)  
3:    
4:    # Find phone element in root  
5:    result = response.iterfind(".//row/*")  
6:    
7:    for r in result:  
8:      time.sleep(1.5)  
9:      if r.tag == 'dnorpattern':  
10:        newdn = r.text  
11:        # Take e164 number and take last 4 digits  
12:        olddn = r.text[-4:]  
13:        # Pass e164 number to cluster11 function  
14:        cluster11(newdn)  
15:        # Pass 4 digit number to cluster86 function  
16:        cluster86(olddn)  

Full Code :

1:  __author__ = 'Mitch.Dawson'  
2:  import requests  
3:  from xml.etree import ElementTree  
4:  import time  
5:    
6:  # CUCM URL's  
7:  url11 = 'https://new:8443/axl/'  
8:  url86 = 'https://old:8443/axl/'  
9:    
10:  # V11 CUCM Headers  
11:  headers11query = {'Content-Type': 'text/xml',  
12:           'SOAPAction': 'CUCM:DB ver=11.0 executeSQLQuery'}  
13:    
14:  headers11update = {'Content-Type': 'text/xml',  
15:            'SOAPAction': 'CUCM:DB ver=11.0 executeSQLUpdate'}  
16:    
17:  # V8.6 CUCM Headers  
18:  headers86update = {'Content-Type': 'text/xml',  
19:            'SOAPAction': 'CUCM:DB ver=8.5 executeSQLUpdate'}  
20:    
21:    
22:  def findextensions():  
23:    
24:    """  
25:    Find numbers on cucm 11 cluster in PAR_HOLDING  
26:    :return:  
27:    """  
28:    
29:    msg = """  
30:      <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/11.0">  
31:        <soapenv:Header/>  
32:        <soapenv:Body>  
33:         <ns:executeSQLQuery sequence="?">  
34:          <sql>select dnorpattern from numplan where fkroutepartition ='72f20467-ddf1-52fb-5586-682a9ef3ea91'  
35:          </sql>  
36:         </ns:executeSQLQuery>  
37:        </soapenv:Body>  
38:      </soapenv:Envelope>"""  
39:    
40:    # Create the Requests Connection  
41:    post = requests.post(url11, data=msg, headers=headers11query, verify=False, auth=('user', 'password'))  
42:        
43:    # Parse the response string  
44:    response = ElementTree.fromstring(post.content)  
45:    
46:    # Find phone element in root  
47:    result = response.iterfind(".//row/*")  
48:    
49:    for r in result:  
50:      time.sleep(1.5)  
51:      if r.tag == 'dnorpattern':  
52:        newdn = r.text  
53:        # Take e164 number and take last 4 digits  
54:        olddn = r.text[-4:]  
55:        # Pass e164 number to cluster11 function  
56:        cluster11(newdn)  
57:        # Pass 4 digit number to cluster86 function  
58:        cluster86(olddn)  
59:    
60:                
61:  def cluster11(dn):  
62:    
63:    """  
64:    Move patterns to PAR_RESOURCES  
65:    :param dn:  
66:    :return:  
67:    """  
68:    
69:    # Message to Post  
70:    msg = """  
71:      <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/11.0">  
72:        <soapenv:Header/>  
73:        <soapenv:Body>  
74:         <ns:executeSQLUpdate sequence="?">  
75:          <sql>update numplan set fkroutepartition = 'bfc7a079-83a5-10cc-a944-74fb1f17064b'  
76:          where dnorpattern = "{0}"  
77:          </sql>  
78:         </ns:executeSQLUpdate>  
79:        </soapenv:Body>  
80:      </soapenv:Envelope>""".format(dn)  
81:    
82:    # Create the Requests Connection  
83:    post = requests.post(url11, data=msg, headers=headers11update, verify=False, auth=('user', 'password'))  
84:    
85:    # Parse the response string  
86:    response = ElementTree.fromstring(post.content)  
87:    
88:    # Find phone element in root  
89:    result = response.iterfind(".//return/*")  
90:    for i in result:  
91:      if i.tag == 'rowsUpdated':  
92:        if i.text == '1':  
93:          print('#### Successfully moved ' + str(dn) + ' to PAR_RESOURCES ####')  
94:        else:  
95:          print('#### The response indicates no rows were updated for dn ' + str(dn) + ' ####')  
96:    
97:    
98:  def cluster86(dn):  
99:    """  
100:    Move numbers to staging partition on 8.6 cluster  
101:    :param dn:  
102:    :return:  
103:    """  
104:    
105:    # Message to Post  
106:    msg = """  
107:        <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/8.5">  
108:          <soapenv:Header/>  
109:          <soapenv:Body>  
110:           <ns:executeSQLUpdate sequence="?">  
111:            <sql>update numplan set fkroutepartition = '61de9659-bc10-fca0-aa5e-81dc4b7e2fe4'  
112:            where dnorpattern = "{0}"  
113:            </sql>  
114:           </ns:executeSQLUpdate>  
115:          </soapenv:Body>  
116:        </soapenv:Envelope>""".format(dn)  
117:    
118:    # Create the Requests Connection  
119:    post = requests.post(url86, data=msg, headers=headers86update, verify=False, auth=('user', 'password'))  
120:    
121:    # Parse the response string  
122:    response = ElementTree.fromstring(post.content)  
123:    
124:    # Find phone element in root  
125:    result = response.iterfind(".//return/*")  
126:    for i in result:  
127:      if i.tag == 'rowsUpdated':  
128:        if i.text == '1':  
129:          print('#### Successfully moved ' + str(dn) + ' to staging partition ####')  
130:        else:  
131:          print('#### The response indicates no rows were updated for dn ' + str(dn) + ' ####')  
132:    
133:    
134:  findextensions()  

Cisco Cucm - Block OffNet To OffNet Transfer

I got called to look into an issue for a customer who were in the process of migrating users from an old 8.6 to a new 11.0 cluster. PSTN access was being routed through the new cluster and calls worked fine, however there were issues with transfers failing in certain scenarios. 

The call flow was as follows - 

PSTN ----> SIP GW ----> V11 CUCM ----> V11 User ----> V8.6 CUCM ----> V8.6 User

After making a number of test calls and taking the appropriate traces I found the following in the SDL logs on the V11 cluster.

69285831.001 |15:11:13.743 |AppInfo |Transferring - Cannot Complete Transfer with PrimaryTransferredIsOffnetDevice =1, SecondaryTransferDestinationIsOffnetDevice =1

The message is clear that CUCM believes this to be an "OffNet To OffNet Transfer" which on this particular cluster was blocked in the service parameters for Toll Fraud prevention, however in my view this appeared to be an OffNet originated call, with a fully On-Net transfer and therfore shouldnt have met the block criteria.

I decided to look at the Sip Trunk between the cluster, which on investigation had been correctly classified as "On-Net"

I then looked at the route pattern that was being used to route from V11 to the 8.6 and found the culprit.

As you can see, the call classification is incorrectly set as "offnet". Once changed to "OnNet" transfers worked as expected.