Paul Tursan

UC & Network Consultant

Often it's kind of frustrating when you get specific reporting requirements for a customer, and none of the built in reports can provide the information, or they give perhaps too much information that is hard to filter.

This had me curious to different ways of querying the database directly. I know with Standalone Cisco Unified Intelligence Center, you can create custom reports, but I wanted to see how easily it could be done without having to install another server, and the associated licencing.

Getting Started

First there's a few prerequisites:
  1. Download the Informix ODBC Driver. Easy enough to find on Google and you can choose for which platform. I used "Informix Downloads (Informix Client SDK Developer Edition for Windows x86_64, 64-bit)"
  2. Install the Client SDK. This includes the ODBC Driver. There's another standalone executable, but this wouldn't install for me and had just a cryptic error message that was very unhelpful. Installing the whole SDK worked fine though.
  3. Create a DSN for CCX. Here I followed the guide Create an ODBC Connection to connect to Cisco UCCX server

    The built in user account is "uccxhruser", and the password for this can be set under CCX Admin > Tools > Password Management.

    To build my File string, I went to File DSN > Add and then specified the filename, and followed the guide above, and finally saved the result.

    You then get a file that looks something like this:
    
    [ODBC]
    DRIVER={IBM INFORMIX ODBC DRIVER (64-bit)}
    UID=uccxhruser
    PWD=EP 81 32 10 12 12 56 86 65 29384  0  0  0  0  0  0  0  0
    DATABASE=db_cra
    HOST=ccx.lab.local
    SERVER=ccx_uccx
    SERVICE=1504
    PROTOCOL=onsoctcp
    CLIENT_LOCALE=en_US.CP1252
    DB_LOCALE=en_US.57372
    
    

  4.  Build the Configuration String to use in Python. Here you just want to take each line, and place it all on one line separated by semicolons ; and update the password to be in cleartext. Also it's important to note that anything with spaces in it needs to be included in {curly braces}. For me, my string then became:

    DRIVER={IBM INFORMIX ODBC DRIVER (64-bit)};UID=uccxhruser;PWD=Cisco123;DATABASE=db_cra;HOST=ccx.lab.local;SERVER=ccx_uccx;SERVICE=1504;PROTOCOL=onsoctcp;CLIENT_LOCALE=en_US.CP1252;DB_LOCALE=en_US.57372
    
    


    Save this string as you'll need it later for the connection.

Connect to the Database and run Queries

Here I ran some standard boilerplate code I found for connecting to Informix Databases using the Python library pyodbc. This establishes a connection with the Database, and returns a cursor that you can then execute queries against. I also import datetime because I plan on using it in later queries.

On the connect command, completely in quotes, you'll enter in the Connection String you generated above.


import pyodbc
import datetime
 
conn = pyodbc.connect('DRIVER={IBM INFORMIX ODBC DRIVER (64-bit)};UID=uccxhruser;PWD=Cisco123;DATABASE=db_cra;HOST=ccx.lab.local;SERVER=ccx_uccx;SERVICE=1504;PROTOCOL=onsoctcp;CLIENT_LOCALE=en_US.CP1252;DB_LOCALE=en_US.57372')
 
conn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
conn.setencoding(str, encoding='utf-8')
conn.setencoding(unicode, encoding='utf-8', ctype=pyodbc.SQL_CHAR)
 
cursor = conn.cursor()



Next as a sample query, I just wanted to see how many total calls were received within Business Hours. There's definitely a cleaner way to iterate over dates in Python (such as creating a generator and iterating over them). For simplicity I just did a simple for loop, incrementing the day in each iteration and saved the results.

Tip: Encase the SQL Queries in double quotes, and within the query use single quotes to avoid any syntax issues, and use the string format method so you're not breaking up the string with + variable1 +  etc throughout.

# Query all calls received during business hours for a particular month
start = datetime.datetime(2017, 3, 1)
 
# Initialise the Results list that will contain a dictionary with the day, followed by the queried results
results = []
 
for i in range(30):
    # Check if current date is a Weekday. 0-4 is Mon-Fri, 5-6 is Sat-Sun
    if start.weekday() < 5:
        # Set Start and End Times for SQL Query
        startTime = start.strftime("%Y-%m-%d 08:00:00")
        endTime = start.strftime("%Y-%m-%d 18:00:00")
 
        # Query SQL Database
        cursor.execute("select contactType, applicationName from ContactCallDetail where ContactCallDetail.startDateTime >= '{}' AND ContactCallDetail.endDateTime <= '{}'".format(startTime, endTime))
        rows = cursor.fetchall()
 
        # Append Results to list
        results.append({'Date' : startTime[:10], 'Calls' : rows})
 
    # Finally increment the day
    start += datetime.timedelta(days=1)


Printing the results, we can see just a simple date and number of calls, and these will only be for the business hours (8:00 to 18:00) specified in the query. As this is just in my test lab, there were many days with no calls at all.


print(results)
 
[{'Date': '2017-03-01', 'Total Calls': 0},
 {'Date': '2017-03-02', 'Total Calls': 0},
 {'Date': '2017-03-03', 'Total Calls': 0},
 {'Date': '2017-03-06', 'Total Calls': 0},
 {'Date': '2017-03-07', 'Total Calls': 0},
 {'Date': '2017-03-08', 'Total Calls': 0},
 {'Date': '2017-03-09', 'Total Calls': 0},
 {'Date': '2017-03-10', 'Total Calls': 0},
 {'Date': '2017-03-13', 'Total Calls': 0},
 {'Date': '2017-03-14', 'Total Calls': 0},
 {'Date': '2017-03-15', 'Total Calls': 0},
 {'Date': '2017-03-16', 'Total Calls': 0},
 {'Date': '2017-03-17', 'Total Calls': 31},
 {'Date': '2017-03-20', 'Total Calls': 4},
 {'Date': '2017-03-21', 'Total Calls': 7},
 {'Date': '2017-03-22', 'Total Calls': 0},
 {'Date': '2017-03-23', 'Total Calls': 0},
 {'Date': '2017-03-24', 'Total Calls': 0},
 {'Date': '2017-03-27', 'Total Calls': 0},
 {'Date': '2017-03-28', 'Total Calls': 0},
 {'Date': '2017-03-29', 'Total Calls': 0},
 {'Date': '2017-03-30', 'Total Calls': 0}]



So that finishes up just a simple introduction. The queries can be as simple or as complicated as you need. To get a description of all the tables and fields available, you want to look for the Unified CCX DB Schema Guide for the corresponding version of UCCX. I'd also recommend doing some reading up on SQL commands, so that you can get more meaningful results (i.e. doing table joins between ContactCallDetail and Resource tables to get the "resourceID" reading the name or userID of the Agent answering calls, etc).


About one year ago I wrote a post  Getting Started with Python CUCM AXL API Programming, consolidating some of the information I'd gathered in using a popular SOAP library for Python "suds-jurko". Since I've done quite a bit more over the last year with CUCM AXL and suds-jurko, I thought I'd write a follow up post to demonstrate a few more practical scenarios that might give people ideas for their own custom scripts / applications.

Here were a few ideas I had of some tasks that weren't so easy, or weren't possible at all just using BAT.

Update Directory Numbers

Here's a topic that I've always found quite frustrating with CUCM and BAT, is that it's not possible to actually update an existing directory number so easily. Most operations involve deleting and recreating the directory  number, which can have other unintended consequences, such as erasing Call Forwarding destinations or Display Names / Alerting Names.

For this example, it will be updating directory numbers (filtering for those starting with the number 5 - perhaps to indicate a particular direct inward dial range) into full +E.164 format. It's becoming increasingly popular especially with larger global organisations to have all telephone numbers in +E.164 format at the Directory Number level, and to allow abbreviated dialing within the site simply configuring a locally significant translation pattern to expand and re-route to the full DN.

First we start off with the standard boilerplate code from my previous forum post. This builds the client object that will be used to run the various methods offered in the AXL WSDL.

from suds.client import Client
from suds.xsd.doctor import Import
from suds.xsd.doctor import ImportDoctor

wsdl = 'file:///C:/Development/axlsqltoolkit/schema/current/AXLAPI.wsdl'
location = 'https://cucmpub.lab.local:8443/axl/'
username = 'admin'
password = 'Cisco123'

tns = 'http://schemas.cisco.com/ast/soap/'
imp = Import('http://schemas.xmlsoap.org/soap/encoding/',
             'http://schemas.xmlsoap.org/soap/encoding/')
imp.filter.add(tns)

client = Client(wsdl,location=location,faults=False,plugins=[ImportDoctor(imp)],
                username=username,password=password)

Next, a bit of investigation into what methods we can use, and what the required input would be. For this a quick visit to Cisco DevNet AXL Schema (version 11.5 is https://developer.cisco.com/site/axl/documents/latest-version/axl-soap.gsp), and a search for the "listLine" method reveals that we need to give the searchCriteria, and the returnedTags we want.

Clicking on the searchCriteria shows us what attributes we can search for to limit the results.



Because I know that all the numbers corresponding to the DID range I'm using start with the number 5 and a partition of "dCloud_PT", I'll use both the pattern and routePartitionName attributes to filter.

Next, for the results, I also just want to return the pattern and routePartitionName, as I will use these in my script for the updateLine method. The returnedTags section shows what other options are available if you  needed to be more specific. You could also return more tags to allow you to further filter later in your script beyond what the searchCriteria offers.



Here is the result of the listLine method with my given filters:

resp = client.service.listLine(searchCriteria={'pattern' : '5%', 
             'routePartitionName' : 'dCloud_PT'}, 
             returnedTags={'pattern' : '', 'routePartitionName' : ''})
print resp

(200, (reply){
    return = 
       (return){
          line[] = 
             (LLine){
                _uuid = "{0BD5A715-60A9-BB1E-58F1-440ECE619E13}"
                pattern = "5211"
                routePartitionName = 
                   (routePartitionName){
                      value = "dCloud_PT"
                      _uuid = "{99C5A8FE-DD48-6E6A-CDEC-42262292EB98}"
                   }
             },
             (LLine){
                _uuid = "{A9D7208A-B9D6-8247-5B79-E0DAFD1500FF}"
                pattern = "5212"
                routePartitionName = 
                   (routePartitionName){
                      value = "dCloud_PT"
                      _uuid = "{99C5A8FE-DD48-6E6A-CDEC-42262292EB98}"
                   }
             },
             (LLine){
                _uuid = "{A8EA818F-0B86-995E-8D4B-31E078CE881E}"
                pattern = "5213"
                routePartitionName = 
                   (routePartitionName){
                      value = "dCloud_PT"
                      _uuid = "{99C5A8FE-DD48-6E6A-CDEC-42262292EB98}"
                   }
             },
       }
  })

The result is a tuple, with the first index being the return code (200 = OK) and the second part being the results. Next we'll define a new variable that contains just the list of returned results that we can loop through in our script.

lines = resp[1]['return'].line
print lines

[(LLine){
   _uuid = "{0BD5A715-60A9-BB1E-58F1-440ECE619E13}"
   pattern = "5211"
   routePartitionName = 
      (routePartitionName){
         value = "dCloud_PT"
         _uuid = "{99C5A8FE-DD48-6E6A-CDEC-42262292EB98}"
      }
 }, (LLine){
   _uuid = "{A9D7208A-B9D6-8247-5B79-E0DAFD1500FF}"
   pattern = "5212"
   routePartitionName = 
      (routePartitionName){
         value = "dCloud_PT"
         _uuid = "{99C5A8FE-DD48-6E6A-CDEC-42262292EB98}"
      }
 }, (LLine){
   _uuid = "{A8EA818F-0B86-995E-8D4B-31E078CE881E}"
   pattern = "5213"
   routePartitionName = 
      (routePartitionName){
         value = "dCloud_PT"
         _uuid = "{99C5A8FE-DD48-6E6A-CDEC-42262292EB98}"
      }
 }]

for line in lines:
    print line.pattern, line.routePartitionName.value

5211 dCloud_PT
5212 dCloud_PT
5213 dCloud_PT

Now we'll loop through all the lines in the list, and for each entry, update the existing directory number with the new directory number. I've added only very basic validation and no error handling, so this is something you might want to add yourself.

for line in lines:
    new_pattern = "\+4930555" + line.pattern
    resp = client.service.updateLine(pattern=line.pattern, 
            routePartitionName=line.routePartitionName.value, 
            newPattern=new_pattern)
    
    if resp[0] == 200:
        print "Successfully Updated DN {} to {}".format(line.pattern, new_pattern)
    else:
        print "Error encountered updating DN {} to {}".format(line.pattern, new_pattern)

Successfully Updated DN 5211 to \+49305555211
Successfully Updated DN 5212 to \+49305555212
Successfully Updated DN 5213 to \+49305555213

And the end result: all the directory numbers have had just their pattern updated, and all other attributes are the same.



Finally, all of the code consolidated for reference:


from suds.client import Client
from suds.xsd.doctor import Import
from suds.xsd.doctor import ImportDoctor

wsdl = 'file:///C:/Development/axlsqltoolkit/schema/current/AXLAPI.wsdl'
location = 'https://cucm1.dcloud.cisco.com:8443/axl/'
username = 'axl_admin'
password = 'dCloud12345!'

tns = 'http://schemas.cisco.com/ast/soap/'
imp = Import('http://schemas.xmlsoap.org/soap/encoding/',
             'http://schemas.xmlsoap.org/soap/encoding/')
imp.filter.add(tns)

client = Client(wsdl,location=location,faults=False,plugins=[ImportDoctor(imp)],
                username=username,password=password)

resp = client.service.listLine(searchCriteria={'pattern' : '5%', 
            'routePartitionName' : 'dCloud_PT'}, 
            returnedTags={'pattern' : '', 'routePartitionName' : ''})

for line in lines:
    new_pattern = "\+4930555" + line.pattern
    resp = client.service.updateLine(pattern=line.pattern, 
            routePartitionName=line.routePartitionName.value, 
            newPattern=new_pattern)
    
    if resp[0] == 200:
        print "Successfully Updated DN {} to {}".format(line.pattern, new_pattern)
    else:
        print "Error encountered updating DN {} to {}".format(line.pattern, new_pattern)

This is just an example for changing the pattern itself, but in the AXL Schema documentation you can find many other attributes that you might want to modify (like CFW Calling Search Space, AAR Masks, External Phone Number Masks, etc.)


Update Telephone, Line and Associations

The next example will be a script that takes an existing Device and User as input, and performs a number of actions:

User:
  • Adds the Device as a "Controlled CTI Device"
  • Adds the user groups Standard CCM End User and Standard CTI Enabled
  • Updates the "Primary Directory Number" to the DN assigned to Line 1 of the Device
Device:
  • Updates the "Owner UserID" on the Device
  • Updates the Device Description with the first & last names of the user and the directory number
 Line 1 on the Device:
  • Updates the Line Text Label (Note: this is actually the Line Appearance from the Device)
  • Updates the Line Description
  • Updates the Alerting Name
  • Updates the Display Name (Note: this is also the Line Appearance from the Device)

Firstly, let's define the User and the Device we're dealing with, then get the Device Details:

phone = 'SEP123456654320'
userid = 'john.doe'

get_phone_resp = client.service.getPhone(name='SEP123456654320')
print get_phone_resp

(200, (reply){
   return = 
      (return){
         phone = 
            (RPhone){
               _ctiid = 179
               _uuid = "{20DBA4D8-0055-A1CE-706D-7C7237021F21}"
               name = "SEP123456654320"
               description = "Tanya Adams - 8861 MRA - X6024"
               product = "Cisco 8861"
               model = "Cisco 8861"
               cls = "Phone"
               protocol = "SIP"
               protocolSide = "User"
               callingSearchSpaceName = 
                  (callingSearchSpaceName){
                     value = "dCloud_CSS"
                     _uuid = "{3A321D3D-0919-0C97-AF0C-F80E3CFB6695}"
                  }
               devicePoolName = 
                  (devicePoolName){
                     value = "dCloud_DP"
                     _uuid = "{1B1B9EB6-7803-11D3-BDF0-00108302EAD1}"
                  }
               commonDeviceConfigName = 
                  (commonDeviceConfigName){
                     value = "dCloud Common Device Configuration"
                     _uuid = "{9F0F7D5F-89A4-136C-20C0-90F3E54D9BCB}"
                  }
               commonPhoneConfigName = 
                  (commonPhoneConfigName){
                     value = "Standard Common Phone Profile"
                     _uuid = "{AC243D17-98B4-4118-8FEB-5FF2E1B781AC}"
                  }
               networkLocation = "Use System Default"
               locationName = 
                  (locationName){
                     value = "dCloud_Location"
                     _uuid = "{B59F63DC-F9FE-16CD-6C9B-9AB84F7AC572}"
                  }
               mediaResourceListName = 
                  (mediaResourceListName){
                     value = "dCloud_MRGL"
                     _uuid = "{2A76E338-7EF1-9B12-4BDE-E1DC7A04BE5C}"
                  }
               networkHoldMohAudioSourceId = "1"
               userHoldMohAudioSourceId = "1"
               automatedAlternateRoutingCssName = ""
               aarNeighborhoodName = ""
               loadInformation = 
                  (loadInformation){
                     value = "sip88xx.11-5-1-18"
                     _special = "false"
                  }
               vendorConfig = 
                  (XVendorConfig){
                     disableSpeaker[] = 
                        "false",
                     disableSpeakerAndHeadset[] = 
                        "false",
                     pcPort[] = 
                        "0",
                     voiceVlanAccess[] = 
                        "0",
                     webAccess[] = 
                        "0",
                     spanToPCPort[] = 
                        "1",
                     recordingTone[] = 
                        "0",
                     recordingToneLocalVolume[] = 
                        "100",
                     recordingToneRemoteVolume[] = 
                        "50",
                     powerPriority[] = 
                        "0",
                     minimumRingVolume[] = 
                        "0",
                     ehookEnable[] = 
                        "0",
                     headsetWidebandUIControl[] = 
                        "0",
                     headsetWidebandEnable[] = 
                        "0",
                     garp[] = 
                        "1",
                     allCallsOnPrimary[] = 
                        "0",
                     g722CodecSupport[] = 
                        "0",
                     webAdmin[] = 
                        "0",
                  }
               versionStamp = "{1453772260-43933CAB-193A-4C47-8A6F-773C5B65CCE1}"
               traceFlag = "false"
               mlppDomainId = ""
               mlppIndicationStatus = "Default"
               preemption = "Default"
               useTrustedRelayPoint = "Default"
               retryVideoCallAsAudio = "true"
               securityProfileName = 
                  (securityProfileName){
                     value = "Cisco 8861 - Standard SIP Non-Secure Profile"
                     _uuid = "{C7D7F171-28C8-4196-94A3-C9C5471DD5AA}"
                  }
               sipProfileName = 
                  (sipProfileName){
                     value = "dCloud Standard SIP Profile"
                     _uuid = "{7E2A3A02-A350-6A17-2967-BDF2998929E3}"
                  }
               cgpnTransformationCssName = ""
               useDevicePoolCgpnTransformCss = "true"
               geoLocationName = ""
               geoLocationFilterName = ""
               sendGeoLocation = "false"
               lines = 
                  (lines){
                     line[] = 
                        (RPhoneLine){
                           _uuid = "{8A34879B-4587-29EC-9788-249762960A89}"
                           index = "1"
                           label = "Tanya Adams - X6024"
                           display = "Tanya Adams - X6024"
                           dirn = 
                              (RDirn){
                                 _uuid = "{A7C50E64-6B3F-2C1F-611A-CCC24ED80D05}"
                                 pattern = "\+19725556024"
                                 routePartitionName = 
                                    (routePartitionName){
                                       value = "dCloud_PT"
                                       _uuid = "{99C5A8FE-DD48-6E6A-CDEC-42262292EB98}"
                                    }
                              }
                           ringSetting = "Use System Default"
                           consecutiveRingSetting = "Use System Default"
                           ringSettingIdlePickupAlert = "Use System Default"
                           ringSettingActivePickupAlert = "Use System Default"
                           displayAscii = "Tanya Adams - X6024"
                           e164Mask = "+1972555XXXX"
                           dialPlanWizardId = ""
                           mwlPolicy = "Use System Policy"
                           maxNumCalls = "6"
                           busyTrigger = "2"
                           callInfoDisplay = 
                              (callInfoDisplay){
                                 callerName = "true"
                                 callerNumber = "false"
                                 redirectedNumber = "false"
                                 dialedNumber = "true"
                              }
                           recordingProfileName = 
                              (recordingProfileName){
                                 value = "MediaSense"
                                 _uuid = "32c8f8a7-1387-dc6e-5293-2c4b4572a4bb"
                              }
                           monitoringCssName = ""
                           recordingFlag = "Selective Call Recording Enabled"
                           audibleMwi = "Default"
                           speedDial = None
                           partitionUsage = "General"
                           associatedEndusers = 
                              (associatedEndusers){
                                 enduser[] = 
                                    (REnduserMember){
                                       userId = "tadams"
                                    },
                              }
                           missedCallLogging = "true"
                           recordingMediaSource = "Gateway Preferred"
                        },
                  }
               numberOfButtons = "10"
               phoneTemplateName = 
                  (phoneTemplateName){
                     value = "Standard 8861 SIP"
                     _uuid = "{3987DE44-582C-4E18-9691-7B07FBF30BCF}"
                  }
               speeddials = ""
               busyLampFields = ""
               primaryPhoneName = ""
               ringSettingIdleBlfAudibleAlert = "Default"
               ringSettingBusyBlfAudibleAlert = "Default"
               blfDirectedCallParks = ""
               addOnModules = ""
               userLocale = "English United States"
               networkLocale = "United States"
               idleTimeout = ""
               authenticationUrl = None
               directoryUrl = None
               idleUrl = None
               informationUrl = None
               messagesUrl = None
               proxyServerUrl = None
               servicesUrl = None
               services = ""
               softkeyTemplateName = ""
               loginUserId = ""
               defaultProfileName = ""
               enableExtensionMobility = "true"
               currentProfileName = ""
               loginTime = ""
               loginDuration = ""
               currentConfig = 
                  (currentConfig){
                     userHoldMohAudioSourceId = "1"
                     phoneTemplateName = 
                        (phoneTemplateName){
                           value = "Standard 8861 SIP"
                           _uuid = "{3987DE44-582C-4E18-9691-7B07FBF30BCF}"
                        }
                     mlppDomainId = ""
                     mlppIndicationStatus = "Default"
                     preemption = "Default"
                     softkeyTemplateName = ""
                     ignorePresentationIndicators = "false"
                     singleButtonBarge = "Off"
                     joinAcrossLines = "Off"
                     callInfoPrivacyStatus = "Default"
                     dndStatus = ""
                     dndRingSetting = ""
                     dndOption = "Use Common Phone Profile Setting"
                     alwaysUsePrimeLine = "Default"
                     alwaysUsePrimeLineForVoiceMessage = "Default"
                     emccCallingSearchSpaceName = 
                        (XFkType){
                           _uuid = ""
                        }
                     deviceName = ""
                     model = ""
                     product = ""
                     deviceProtocol = ""
                     cls = ""
                     addressMode = ""
                     allowAutoConfig = ""
                     remoteSrstOption = ""
                     remoteSrstIp = ""
                     remoteSrstPort = ""
                     remoteSipSrstIp = ""
                     remoteSipSrstPort = ""
                     geolocationInfo = ""
                     remoteLocationName = ""
                  }
               singleButtonBarge = "Off"
               joinAcrossLines = "Off"
               builtInBridgeStatus = "On"
               callInfoPrivacyStatus = "Default"
               hlogStatus = "On"
               ownerUserName = 
                  (ownerUserName){
                     value = "tadams"
                     _uuid = "61a1d409-6ff5-449d-5988-33f1722888b9"
                  }
               ignorePresentationIndicators = "false"
               packetCaptureMode = "None"
               packetCaptureDuration = "0"
               subscribeCallingSearchSpaceName = ""
               rerouteCallingSearchSpaceName = ""
               allowCtiControlFlag = "true"
               presenceGroupName = 
                  (presenceGroupName){
                     value = "Standard Presence group"
                     _uuid = "{AD243D17-98B4-4118-8FEB-5FF2E1B781AC}"
                  }
               unattendedPort = "false"
               requireDtmfReception = "false"
               rfc2833Disabled = "false"
               certificateOperation = "No Pending Operation"
               certificateStatus = "None"
               upgradeFinishTime = None
               deviceMobilityMode = "Default"
               remoteDevice = "false"
               dndOption = "Use Common Phone Profile Setting"
               dndRingSetting = ""
               dndStatus = "false"
               isActive = "true"
               isDualMode = "false"
               mobilityUserIdName = ""
               phoneSuite = "Default"
               phoneServiceDisplay = "Default"
               isProtected = "false"
               mtpRequired = "false"
               mtpPreferedCodec = "711ulaw"
               dialRulesName = ""
               sshUserId = ""
               digestUser = ""
               outboundCallRollover = "No Rollover"
               hotlineDevice = "false"
               secureInformationUrl = ""
               secureDirectoryUrl = ""
               secureMessageUrl = ""
               secureServicesUrl = ""
               secureAuthenticationUrl = ""
               secureIdleUrl = ""
               alwaysUsePrimeLine = "Default"
               alwaysUsePrimeLineForVoiceMessage = "Default"
               featureControlPolicy = ""
               deviceTrustMode = "Not Trusted"
               confidentialAccess = 
                  (confidentialAccess){
                     confidentialAccessMode = ""
                     confidentialAccessLevel = "-1"
                  }
               requireOffPremiseLocation = "false"
               cgpnIngressDN = ""
               useDevicePoolCgpnIngressDN = "true"
               msisdn = ""
               enableCallRoutingToRdWhenNoneIsActive = "false"
               wifiHotspotProfile = ""
               wirelessLanProfileGroup = ""
            }
      }
 })


Next we can update the user, to add the CTI Device Association, the user groups, and update the Primary Directory Number to that of the associated Device.

# Define the list of associated devices. 
# This is a list of 0 or more <device>SEPXXXXXXXX</device> tags
phone = resp[1]['return'].phone.name
associated_devices = [{'device' : resp[1]['return'].phone.name}]

# Define the Groups to be added to the user. 
# Under "userGroup" is a list of <name>GroupName</name> tags.
associated_groups = {'userGroup' : [{'name' : 'Standard CCM End Users'}, 
                                    {'name' : 'Standard CTI Enabled'}]}

# Get the DN Pattern and Partition of the first line from the getPhone query

dn_pattern = resp[1]['return'].phone.lines.line[0].dirn.pattern
dn_partition = resp[1]['return'].phone.lines.line[0].dirn.routePartitionName.value

# Update the user
update_user_resp = client.service.updateUser(userid=userid, 
                      associatedDevices=associated_devices, 
                      associatedGroups=associated_groups, 
                      primaryExtension={'pattern' : dn_pattern, 
                                        'routePartitionName' : dn_partition})

print update_user_resp

(200, (reply){
   return = "{86EFA455-4753-0DF4-2501-477E8EAC68FE}"
 })

Next we need to gather the details of the user to be able to update the Device / Line.

# Get the User Details
get_user_resp = client.service.getUser(userid=userid)

print get_user_resp

(200, (reply){
   return = 
      (return){
         user = 
            (RUser){
               _uuid = "{86EFA455-4753-0DF4-2501-477E8EAC68FE}"
               firstName = "John"
               middleName = None
               lastName = "Doe"
               userid = "john.doe"
               password = None
               pin = None
               mailid = "john.doe@cisco.com"
               department = None
               manager = None
               userLocale = ""
               associatedDevices = 
                  (associatedDevices){
                     device[] = 
                        "SEP123456654320",
                  }
               primaryExtension = 
                  (primaryExtension){
                     pattern = "\+19725556024"
                     routePartitionName = "dCloud_PT"
                  }
               associatedPc = None
               associatedGroups = 
                  (associatedGroups){
                     userGroup[] = 
                        (userGroup){
                           name = "Standard CCM End Users"
                           userRoles = 
                              (userRoles){
                                 userRole[] = 
                                    "Standard CCM End Users",
                                    "Standard CCMUSER Administration",
                              }
                        },
                        (userGroup){
                           name = "Standard CTI Enabled"
                           userRoles = 
                              (userRoles){
                                 userRole[] = 
                                    "Standard CTI Enabled",
                              }
                        },
                  }
               enableCti = "true"
               digestCredentials = None
               phoneProfiles = ""
               defaultProfile = ""
               presenceGroupName = 
                  (presenceGroupName){
                     value = "Standard Presence group"
                     _uuid = "{AD243D17-98B4-4118-8FEB-5FF2E1B781AC}"
                  }
               subscribeCallingSearchSpaceName = ""
               enableMobility = "false"
               enableMobileVoiceAccess = "false"
               maxDeskPickupWaitTime = "10000"
               remoteDestinationLimit = "4"
               associatedRemoteDestinationProfiles = ""
               passwordCredentials = 
                  (passwordCredentials){
                     pwdCredPolicyName = "dCloud Credential Policy"
                     pwdCredUserCantChange = "false"
                     pwdCredUserMustChange = "false"
                     pwdCredDoesNotExpire = "true"
                     pwdCredTimeChanged = "May 27, 2017 08:14:28 CDT"
                     pwdCredTimeAdminLockout = None
                     pwdCredLockedByAdministrator = "false"
                  }
               pinCredentials = 
                  (pinCredentials){
                     pinCredPolicyName = "dCloud Credential Policy"
                     pinCredUserCantChange = "false"
                     pinCredUserMustChange = "false"
                     pinCredDoesNotExpire = "true"
                     pinCredTimeChanged = "May 27, 2017 08:14:27 CDT"
                     pinCredTimeAdminLockout = None
                     pinCredLockedByAdministrator = "false"
                  }
               associatedTodAccess = ""
               status = "1"
               enableEmcc = "false"
               associatedCapfProfiles = ""
               ctiControlledDeviceProfiles = ""
               patternPrecedence = ""
               numericUserId = None
               mlppPassword = None
               customUserFields = ""
               homeCluster = "true"
               imAndPresenceEnable = "false"
               serviceProfile = 
                  (serviceProfile){
                     value = "dCloud_Service-Profile"
                     _uuid = "{4B5DE118-6185-A12B-6164-EF523DE23753}"
                  }
               lineAppearanceAssociationForPresences = 
                  (lineAppearanceAssociationForPresences){
                     lineAppearanceAssociationForPresence[] = 
                        (RLineAppearanceAssociationForPresence){
                           _uuid = "{8A34879B-4587-29EC-9788-249762960A89}"
                           laapAssociate = "t"
                           laapProductType = "Cisco 8861"
                           laapDeviceName = "SEP123456654320"
                           laapDirectory = "\+19725556024"
                           laapPartition = "dCloud_PT"
                           laapDescription = "Tanya Adams - 8861 MRA - X6024"
                        },
                  }
               directoryUri = "john.doe@cisco.com"
               telephoneNumber = None
               title = None
               mobileNumber = None
               homeNumber = None
               pagerNumber = None
               extensionsInfo = 
                  (extensionsInfo){
                     extension[] = 
                        (RExtension){
                           _uuid = "{840FB340-0348-3BD2-55CA-B4704987DF48}"
                           sortOrder = "0"
                           pattern = 
                              (pattern){
                                 value = "\+19725556024"
                                 _uuid = "{A7C50E64-6B3F-2C1F-611A-CCC24ED80D05}"
                              }
                           routePartition = "dCloud_PT"
                           linePrimaryUri = "tadams@sip.dcloud.cisco.com"
                           partition = 
                              (partition){
                                 value = "dCloud_PT"
                                 _uuid = "{99C5A8FE-DD48-6E6A-CDEC-42262292EB98}"
                              }
                        },
                  }
               selfService = "19725556024"
               userProfile = ""
               calendarPresence = "false"
               ldapDirectoryName = ""
               userIdentity = None
               nameDialing = "DoeJohn"
               ipccExtension = ""
               convertUserAccount = ""
            }
      }
 })

Here, using the attributes of the user, we can update the Device Description

# Gather the attributes needed to update the Telephone and Line
first_name = get_user_resp[1]['return'].user.firstName
last_name = get_user_resp[1]['return'].user.lastName

# Define Description in the format "FirstName LastName - DirectoryNumber"
# Because the DN in this case is +E.164 with a \
# we have to skip the \ otherwise it's not in a valid format.
description = "{} {} - {}".format(first_name, last_name, dn_pattern[1:])

# First, get the line appearance from the getDevice query initiated earlier
line = get_phone_resp[1]['return'].phone.lines.line[0]

# Next define the updated line appearance variables
# For the Label, we'll use "LastName - 5-Digit-Ext"
line.label = last_name + " - " + dn_pattern[-5:]

# For the Display, it'll be "FirstName LastName"
line.display = first_name + " " + last_name 
line.displayAscii = first_name + " " + last_name 


# Update the Phone with a Description consisting of the First Name,
# Last Name and Directory Number
update_phone_resp = client.service.updatePhone(name=phone, 
               description=description, 
               ownerUserName=userid, 
               lines={'line' : line})

print update_phone_resp

(200, (reply){
   return = "{20DBA4D8-0055-A1CE-706D-7C7237021F21}"
 })

And then the Line Description, Alerting Name, Display Name, Line Text Label, and Line Association.

# Next update the Line itself for non-line appearance settings such as the Alerting Name and Description

update_line_resp = client.service.updateLine(pattern=dn_pattern, 
               routePartitionName=dn_partition, 
               description=description, 
               alertingName=first_name + " " + last_name, 
               asciiAlertingName=first_name + " " + last_name)

print update_line_resp

(200, (reply){
   return = "{A7C50E64-6B3F-2C1F-611A-CCC24ED80D05}"
 })


And there you have it. Here is the summarised script:

from suds.client import Client
from suds.xsd.doctor import Import
from suds.xsd.doctor import ImportDoctor

wsdl = 'file:///C:/Development/axlsqltoolkit/schema/current/AXLAPI.wsdl'
location = 'https://cucm1.dcloud.cisco.com:8443/axl/'
username = 'axl_admin'
password = 'dCloud12345!'

tns = 'http://schemas.cisco.com/ast/soap/'
imp = Import('http://schemas.xmlsoap.org/soap/encoding/',
             'http://schemas.xmlsoap.org/soap/encoding/')
imp.filter.add(tns)

client = Client(wsdl,location=location,faults=False,plugins=[ImportDoctor(imp)],
                username=username,password=password)

# Specify the Phone Name and UserID to associate with one another.
# You could also have these input as arguments at the command line rather than static in the script.
phone = 'SEP123456654320'
userid = 'john.doe'

# Get the Phone
get_phone_resp = client.service.getPhone(name=phone)

# Define the list of associated devices for the user.
# This is a list of 0 or more <device>SEPXXXXXXXX</device> tags
phone = get_phone_resp[1]['return'].phone.name
associated_devices = [{'device' : phone}]

# Define the Groups to be added to the user. 
# Under "userGroup" is a list of <name>GroupName</name> tags.
associated_groups = {'userGroup' : [{'name' : 'Standard CCM End Users'}, 
                                    {'name' : 'Standard CTI Enabled'}]}

# Get the DN Pattern and Partition of the first line from the getPhone query

dn_pattern = get_phone_resp[1]['return'].phone.lines.line[0].dirn.pattern
dn_partition = get_phone_resp[1]['return'].phone.lines.line[0].dirn.routePartitionName.value

# Update the user
update_user_resp = client.service.updateUser(userid=userid, 
                      associatedDevices=associated_devices, 
                      associatedGroups=associated_groups, 
                      primaryExtension={'pattern' : dn_pattern, 
                                        'routePartitionName' : dn_partition})

# Get the User Details
get_user_resp = client.service.getUser(userid=userid)

# Gather the attributes needed to update the Telephone and Line
first_name = get_user_resp[1]['return'].user.firstName
last_name = get_user_resp[1]['return'].user.lastName

# Define Description in the format "FirstName LastName - DirectoryNumber"
# Because the DN in this case is +E.164 with a \
# we have to skip the \ otherwise it's not in a valid format.
description = "{} {} - {}".format(first_name, last_name, dn_pattern[1:])

# First, get the line appearance from the getDevice query initiated earlier
line = get_phone_resp[1]['return'].phone.lines.line[0]

# Next define the updated line appearance variables
# For the Label, we'll use "LastName - 5-Digit-Ext"
line.label = last_name + " - " + dn_pattern[-5:]

# For the Display, it'll be "FirstName LastName"
line.display = first_name + " " + last_name 
line.displayAscii = first_name + " " + last_name 


# Update the Phone with a Description consisting of the First Name, 
# Last Name and Directory Number
update_phone_resp = client.service.updatePhone(name=phone, 
                     description=description, 
                     ownerUserName=userid, 
                     lines={'line' : line})
print update_phone_resp

# Next update the Line itself for non-line appearance settings
# such as the Alerting Name and Description
update_line_resp = client.service.updateLine(pattern=dn_pattern, 
                      routePartitionName=dn_partition, 
                      description=description, 
                      alertingName=first_name + " " + last_name, 
                      asciiAlertingName=first_name + " " + last_name)



Wrap-Up / Tips

So, there are a couple of other examples of how to use Python with suds-jurko. I'll leave this post with a few tips:

  • If you're getting syntax errors, check the CUCM AXL Schema pages and try and get a better understanding of what inputs it requires for updates / adds. Sometimes it's a little more convoluted than you think it will be. A good example was the "associatedGroups" attribute in the updateUser method. There I find it helps to think of how suds will take your dict data and transform it into XML.

    For example:

                <associatedGroups>
                    <userGroup>
                        <name>Standard CCM End Users</name>
                    </userGroup>
                    <userGroup>
                        <name>Standard CTI Enabled</name>
                    </userGroup>
                </associatedGroups>

    May look like this when represented as keyword arguments with a Python dictionary as the value:
    associatedGroups={'userGroup' : [{'name' : 'Standard CCM End User'}, {'name' : 'Standard CCM End User'}]}

    suds-jurko supports the logging module, so you can also turn this on and view all of your requests, and there the mistakes might appear obvious.

  • If you're not getting errors, but you don't see any actual changes, you've probably screwed up your syntax. It can be a little trial and error (in fact, it was for me writing this post too, especially with the user groups).

  • These scripts won't work as written if your CUCM has SAML SSO activated. I used a dCloud instance and deactivated SSO. For SSO you'll need to modify the boilerplate code to be the following. This ensures the authentication header is included in each request suds sends. (https://stackoverflow.com/questions/11742494/python-soap-client-wsdl-call-with-suds-gives-transport-error-401-unauthorized-f) 
from suds.client import Client
from suds.xsd.doctor import Import
from suds.xsd.doctor import ImportDoctor
from suds.plugin import MessagePlugin
import base64

wsdl = 'file:///C:/Development/axlsqltoolkit/schema/current/AXLAPI.wsdl'
location = 'https://cucm1.dcloud.cisco.com:8443/axl/'
username = 'amckenzie'
password = 'dCloud12345!'

base64string = base64.encodestring('%s:%s' % (username, password)).replace('\n', '')
authenticationHeader = {
    "SOAPAction" : "ActionName",
    "Authorization" : "Basic %s" % base64string
}


tns = 'http://schemas.cisco.com/ast/soap/'
imp = Import('http://schemas.xmlsoap.org/soap/encoding/',
             'http://schemas.xmlsoap.org/soap/encoding/')
imp.filter.add(tns)

client = Client(wsdl,location=location,faults=False,plugins=[ImportDoctor(imp)],
                headers=authenticationHeader)

I recently had a very annoying issue. One of my end users wasn't able to login to Cisco Jabber, and was receiving the "Cannot communicate with the Server" error message. Service discovery was working fine, _cisco-uds._tcp.domain.name resolved fine, as did the underlying servers.

UDS Discovery was working fine (User could successfully browse and authenticate to https://cucm.domain:8443/cucm-uds/user/USERID and retrieve the XML), and TFTP over HTTP 6970 was working fine to download the jabber-config.xml. Erasing the Jabber cache and uninstalling / reinstalling Jabber had no effect.

In the Jabber logs, any HTTP GET request was returning a 407 Proxy Authentication Required

HTTP response code 407 for request #7 to http://10.1.1.1:6970/CSFUSERID.cnf.xml
HTTP response code 407 for request #9 to http://10.1.1.1:6970/SPDefault.cnf.xml
Request #7 got status line: HTTP/1.1 407 Proxy Authentication Required
Initial retrieval of the TFTP files failed: TFTP_REQUEST_FAILED

This was even though the system proxy settings specified a proxy.pac that returns DIRECT for all internal domain names and IP Addresses. Disabling the Proxy altogether seemed to have no effect.

This issue was only affecting the user on this PC. As it turns out, there was an environment variable of "http_proxy" that was the cause of the issue. From a command-prompt you could run the following command:

C:\Windows\System32> set http_proxy
http_proxy=http://proxyserver.domain.com:8080/

After trying to remove this variable, it still didn't work

C:\Windows\System32> set http_proxy=
C:\Windows\System32> set http_proxy
Environment variable http_proxy not defined

It turns out, this was only removing the environment variable for this one command-prompt session. To REALLY clear the variable, I had to run cmd.exe as Administrator, and enter the following command:

C:\Windows\System32> setx http_proxy "" -m

After running this, the http_proxy is persisently gone, and my user could authenticate to Jabber again, and Jabber LDAP directory successfully authenticates too.

Just another side tip: It seems the default setting for Jabber is now to use "UPN Discovery", which if it detects a user is authenticated to a Windows domain, it will automatically populate the username field. This is extremely annoying for troubleshooting if you want to login to Jabber as a different user as the one who is logged into Windows. To work around this, you can reinstall Jabber with the following settings. The CLEAR=1 erases any existing Jabber Bootstrap Properties file.

msiexec.exe /i CiscoJabberSetup.msi CLEAR=1 UPN_DISCOVERY_ENABLED=false EXCLUDED_SERVICES=WEBEX

As an alternative, you can also update the C:\ProgramData\Cisco Systems\Cisco Jabber\jabber-bootstrap.properties file and simply modify any line (or line 30 as the Installer does) from "NOT_SPECIFIED" to "upnDiscoveryEnabled: false"

Introduction

In the previous posts we setup the PKI infrastructure needed. This post assumes that you've already created a trustpoint and installed a certificate on your Gateway (from Part 2), and also added the CA Root Certificate to CUCM (from Part 1).

In this post, I cover setting up the SIP Trunk from the CUCM and Gateway side, and enabling TLS and sRTP.

Create SIP Trunk Security Profile

All of the default SIP Trunk Security Profiles in CUCM are for non-encrypted communcation, therefore it's necessary to create a new SIP Trunk Security Profile. The X.509 Name in the specified Profile needs to match the Common Name presented by your Gateway certificate.

First go to CUCM Administration > System > Security > SIP Trunk Security Profile and choose Find to display all existing profiles. Next to the standard "Non-Secure SIP Trunk Security Profile" click the copy button.


Give the new profile a meaningful name that is specific to the Gateway you're going to use it with. 

Select a Device Security Mode of Encrypted and ensure the Incoming / Outgoing Transport Type are TLS. Finally configure the X.509 Subject Name to be your Gateway's FQDN and click Save.

If you want to use the same profile for multiple Gateways, you should be able to include each subsequent Gateway in the X.509 field separated by commas.


Create SIP Trunk

Now there are many things to consider when configuring a SIP Trunk, MGCP Gateway, etc. such as AAR, Calling Search Spaces. As the purpose of this article is simply to demonstrate SIP TLS configuration will be skipping over any specific configuration for this and assuming you know how to handle CSSs, digit manipulation, etc.

Browse to Device > Trunk and click Add New. Select SIP Trunk and leave the Trunk Service Type at the default value. Click Next.


Next enter the following details as a minimum:





You could optionally create a new SIP Profile for use with the Trunk. I typically do this and enable Options Ping so you can view the status of the Trunk from CUCM. Also if you  need to force early offer for any reason, you'll definitely need to do that.

Providing all your certificate setup is complete, all you need to do is setup a route pattern and point it to the SIP Trunk (preferably via a Route List / Route Group) and another secure mode Phone to make and receive external calls with.

Gateway Configuration

First stage is to set the SIP Transport mode globally to TLS. If you only wish to enable this on individual dial-peers you can leave this out and configure everything on a specific voip dial-peer.

 voice service voip  
  ip address trusted list  
   ipv4 10.1.1.1 255.255.255.255  
   ipv4 10.1.1.2 255.255.255.255  
  srtp fallback  
  allow-connections sip to sip  
  sip  
   session transport tcp tls  
   srtp negotiate cisco  

Here I've also added some IPs to the trusted IP List. This isn't absolutely necessary as long as you have a specific IP mentioned in a dial-peer they will get added to this automatically (not visible in the config though).

The srtp command alone forces all calls to use srtp. If you have some devices that are not capable of this (such as Cisco 7937 SCCP Conference stations) then you're going to want to use the "srtp fallback", that will allow the call to fallback to regular RTP.

Next you need to setup the sip-ua configuration to select which trustpoint you'll use for SIP TLS.


 sip-ua  
  retry invite 3  
  timers trying 200  
  crypto signaling default trustpoint VOICEGW1 strict-cipher  

The "strict-cipher" forces all communication to use TLS. Again, if you want to only activate this on certain dial-peers, you can leave this out and configure directly on the dial-peer. Also in this config I adjust some timers to allow for faster failover if one CUCM node is down.

Lastly, is simply the VoIP dial-peer configuration.

You need an inbound dial-peer because the default inbound dial-peer has crappy settings by default (VAD on, g729 codec, h323, etc).

As you can see, I use my dial-peer towards the CUCM as full +E.164, because on the PSTN inbound dial-peer I would have translated any called numbers first to this format (see my other blog post http://chilli-net.blogspot.de/2016/08/sip-gateway-digit-manipulation-with.html regarding my reasons for this).

Note that the port you configure here for the outbound SIP dial-peers needs to match your SIP Trunk (and SIP Trunk Security Profile).

 dial-peer voice 3000 voip  
  description ## INBOUND SIP VOIP DIAL-PEER ##  
  session protocol sipv2  
  incoming called-number .  
  dtmf-relay rtp-nte sip-kpml  
  no vad
  codec g711ulaw  
 !  
 dial-peer voice 2000 voip  
  description ## SITE X DID RANGE TO CUCM1 ##  
  destination-pattern +61894445...  
  session protocol sipv2  
  session target ipv4:10.1.1.1:5061  
  dtmf-relay rtp-nte sip-kpml 
  no vad 
  codec g711ulaw  
 !  
 dial-peer voice 2001 voip  
  description ## SITE X DID RANGE TO CUCM2 ##  
  preference 1  
  destination-pattern +61894445...  
  session protocol sipv2  
  session target ipv4:10.1.1.2:5061  
  dtmf-relay rtp-nte sip-kpml  
  no vad
  codec g711ulaw  

And there you have it. You'll just need to configure the rest of your dial-plan for PSTN side and on the CUCM side. Use "debug ccsip messages"  to see the inbound / outbound communication.

I hope this is helpful, as I know I had to dig in dozens of different articles to get all the necessary information. Cisco Support documentation has a habit now of making you go down the rabbit hole to perform any one simple task!
Previous Post Older Posts Home